Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4


View 2 Replies


Return Column Header Based On Column Criteria And Number Value

Feb 7, 2014

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

CriteriaHeader 1Header 2Header 3Header 4

DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))

View 3 Replies View Related

Rearrange Columns By Number Leaving Blank Spaces

Oct 16, 2013

I have a lot of numbers in different columns:

1 1 2
2 4 6
3 6 9
4 8 .
5 9 .

I want to to order them so they leave blank rows if they don't this:

1 1 2
4 4
6 6 6
8 8
9 9 9

View 11 Replies View Related

Possible To Sort Hoizontally With Multiple Columns Under One Header

Aug 6, 2008

In row 1 I want to have the names of servers, so we would have A C D E

Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.

Then B would have under it Start, End, Data, Time

And so on for C D ...

I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be

A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.

I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.

View 9 Replies View Related

Column Header As Sort Tool

Apr 14, 2008

Came across a spreadsheet that my firm created, where you can click on the column header and it automatically sorts ascending order for the column without messing up the other columns. Then if you double click it again, it will sort in reverse order.

View 9 Replies View Related

Copy Every Second Column Header And String Something On

Mar 12, 2009

I have this spreadsheet, I have a title in cell A1, then the following column headers alternate betweens entries I want and those I don't (in this case A in Cell B1, an irrelevant value in C1, C in Cell D1, an irrelevant value in E1, D in Cell F1 etc...).

What I want to do is take the original value, so A from B1 and make it Aa, then make C1 contain the same A, but this time Ab.

It should be very obvious what I'm after from the spreadsheet.

View 3 Replies View Related

Excel 2010 :: Sort Growing Column Starting Under Header Row At Cell C5

May 7, 2014

find macros to sort fixed ranges but not an entire column with existing header rows from 1 to 4.

Added to that is the need to keep the code compatible with Excel 2003 even though I have Excel 2010 at office (it's a file that needs to be "openable" in both versions so the vb code needs to be in XL 03.

Column currently starts at C5 but goes down to C47, at this point. But it's a growing list. Some of the rows are blank at the bottom, too.

View 2 Replies View Related

Move And Sort With One Column But Insert Extra Columns As Needed For Proper Sort?

Jan 13, 2014

Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.

Edit: Not sure what happened but file was NOT understandable before. It should be correct now.

View 2 Replies View Related

Generating Random Number String Based On Alphanumeric String?

Aug 14, 2014

Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?


Cell A1 has 123XVF1234
Cell A2 has 321AFW4321

In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value


If B1 returns 643562 it would need to be able to be converted back to 123XVF1234

View 7 Replies View Related

How To Delete Columns And Then Add Column Header To Remaining Ones

May 15, 2014

So I want to add some VBA Code that deletes specified columns and then insert Bolded Column headers to the remaining columns. The columns I want deleted are D,F,I,J,K. For simplicity purposes, the column headers would be A,B,C,D,E,F,G.

I tried using Columns("D,F,I,J,K").delete but I kept on getting 13 error.

[Code] ......

Attached File : Test VBA File.xlsx‎

View 3 Replies View Related

Create PivotTable With Two Columns Of Data For The Same Column Header?

Dec 24, 2012

Can I create a PivotTable with two columns of data for the same Column header?

I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.

View 2 Replies View Related

Sort Pivot Table Based On Partial String...

Oct 6, 2008

I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,

Standard Kitchen
Standard Master Bath
Standard Hall Bath
Optional Kitchen
Optional Basement Bath

First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.

View 3 Replies View Related

Sort Rows By Mid() String Of Column Cells

Mar 5, 2009

I have a single column dynamic range called "Dock" and I need to sort the rows in my worksheet based on information from this range.

Each cell in "Dock" is formated similarly to "TM09030010"
This is "TM" & YYMM & "4 digit number"

I need to sort my rows based first on Date "YYMM" then second by the last 4 digits of the same string.

The worksheet has information in columns A through K and Range "Dock" is located within column A.

View 9 Replies View Related

VBA / Find Column Header And Reformat (date) Values In Columns

Jun 28, 2013

I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).

Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.

Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer


View 3 Replies View Related

Return Value From Column Based On Header Value?

Jul 16, 2013

Cells A3:A500 contain product numbers, e.g. 1001, 1002, 1003 etc.. etc...
Cells G2:N2 are column headers, e.g. North2011, South2011, North2012, South2012 etc... etc...
Cell F2 is a lookup that will return a year, e.g. 2011, 2012, 2013 etc... etc...

Against each product is a "yes" if it was sold in the north or south region for each year. This could be both north and south, one or the other or none at all.

What I would like is a formula in cells E3:E500 that looks at the year in cell F2 and if there is a "yes" in either the North or South for that year to return "yes" otherwise blank.

I am unable to upload a sample document due to firewalls.

View 5 Replies View Related

Return Column Header Based On Row Value

Mar 11, 2014

Evaluation Results

Vendor Lowest Price
Vendor 2nd Lowest Price

[Code] .......

I have above table and wants to get the formula for getting the 1) Vendor Name of the Lowest Price per item 2) Vendor Name of the 2nd Lowest Price per item 3) and be able to copy down the formula for other items.

View 5 Replies View Related

Macro To Copy Data From All Columns Containing Header To A Single Column In Workbook

Apr 11, 2014

I am a macro newbie and I think this is beyond me.

I've been trying all morning with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.

So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in cell A4 or B4 or C4 etc. with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database.

There must be a way to do this that is easier than searching the internet all afternoon again

View 3 Replies View Related

Delete Column Based On Column Header

Apr 29, 2009

I have a spreadsheet with several columns (all column names are in row 1).

I have 2 of column names. Note that their actual column# might change so the macro would have to look for the column name and NOT the column position ...

View 11 Replies View Related

Dynamic Lookup Based On Column Header

Jun 21, 2013

I'm trying to calculate a figure in a report tab that reads off the data tab and looks for a specific column based on the header of that column. My explanation is admittedly poor but I've attached a sample file with an outline of what I'm trying to do which I hope makes things clearer.

Sample File.xlsx

View 2 Replies View Related

Lookup Column Header Based On Cell Value

Aug 11, 2009

I have a spreadsheet an excerpt of which is as follows:

1 Doors Security Vehicle Key

Want I want to happen is to return the column header where there for a cell which contains "TRUE" in a blank column. If there isn't a cell with the value "TRUE" then to return "N/A". In the above example in blank column P for Row 2 would be "Doors", Row 3 would be "Security,Vehicle" and Row 4 would be "N/A".

In my actual spreadsheet I have 12 columns (L to W) which have the TRUE or FALSE values.

I know it has something to do with either HLOOKUP, INDEX and MATCH but can't seem to get it right. Any ideas?

View 4 Replies View Related

Looking Up Column Header Value Based On Color And Position In Row

Dec 12, 2009

I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document.

Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.

View 5 Replies View Related

Rearrange Rows Into Columns?

Jun 11, 2014

I have a report that lists the # of times an agent in a call center passed or failed their call based on specific guidelines. The report that's generated will not show the failed category if they didn't fail any calls. I've attached what the report looks like. I'm trying to format the report so that in column A I have the name, column B the passed calls and column C the failed calls. Column B and C can be interchanged...doesn't matter too much. If they don't have any failed or passed calls, I'd like a 0 to show up. I've tried messing with filters and INDEX/MATCH functions but can't make any headway.

View 2 Replies View Related

Getting Column Number From Column Header?

Jan 24, 2013

I have software (SAS Add-In for Microsoft Office) that loads external data into Excel. The data has a title, header, and rows/columns. It automatically creates a named range such as Table1. The named range does NOT include the headers.

Here is an example:



As above, can I dynamically derive the column number based on the header row of the named range, where such named range refers to a table?

On a related topic, can I derive the column name given the column number? For example, in pseudocode:

For currCol = firstCol To (firstCol + lastCol - 1)
' Assume a function called ColumnName
Debug.Print ColumnName(currCol)

View 1 Replies View Related

Lookup Column Header Value Based On Matching Table Value

May 16, 2013

I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.

eg: sheet 1



[Code] .......

The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.

View 1 Replies View Related

Selectively Copying Cells Based On Column Header?

Jul 9, 2013

I'm trying to copy cells from a column in one workbook to cells in a column in another workbook based on what's in a header column.

Such as

Original workbook
Mat Qty
A 1
C 3
D 2
E 4
G 5
Total 15

Target workbook becomes

Mat Qty -> Mat Qty
A -> A 1
B -> B
C -> C 3
D -> D 2
E -> E 4
F -> F
G -> G 5

My code so far (which might not be worth reading) is:

Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range, sourceColumnValue As Range, targetColumnValue As Range
Dim mySourceCell As Range, myTargetCell As Range


I'm getting a runtime error ('1004'. Application-defined or object-defined error) on the Copy statement within the If sttement.

View 3 Replies View Related

Macro To Insert Formula Based On Column Header

Feb 24, 2009

I have to use several dozen pivot tables a day. I already use a couple of macros to do a lot of the tedious formatting, but if I could automate this it would save me a lot more time. I have been trying to both write a code and modify code written by the recorder but have not even come close to anything that works. What I want is to have a macro that searches through the column header for predefined names and if it comes across one of these names it will insert a specific formula, based on its name, in the first cell below the header. Then it will autofill to the last row and move on to searching for the next predefined header name. If that name is not found, it will move to the next predefined header name. One major problem is that the formula to insert under any specific header name is based on other columns in the table that aren't always in the same range. So, not only do I need it to search for columns to place a formula in, that formula has to search for the appropriate column to find the correct value to use in the calculation.

For example, one predefined column name would be Avg Price. The formula for that column would be (Sales/Qty Sold), but these 2 columns could be located anwhere in the table. How can I get it, once it has found a column that needs a formula, to locate the correct column/cell to get the correct value from the sales and Qty Sold columns?

View 9 Replies View Related

Rearrange Selected Columns Automatically

Jun 21, 2013

Daily I am getting a file which have so many columns with different names. I used to select and move my required columns to separate place. For exp.

I have the file with various columns, such are.

Sales Qty, Year, Tax, Company Code, Discount Paid, Sales Amount, Company Name

The above column order are not constant, daily the order will changes. But we have to make the order as below

Company Name, Company Code, Year, Sales Qty, Sales Amount, Tax, Discount Paid

Like this we have so many columns in the file and we used to rearrange as required.

Any formula or any macro to avoid this manual work with error, so that i will save my time and free from error.

View 2 Replies View Related

How To Rearrange Data Into Columns If It Is In Rows

Jan 15, 2013

I have an Excel sheet that is in rows. I want to separate the data into columns. Each sample is separated by a space in the rows. So sample 1 is A1 is the name and B1 is the actual name. A2 is the description and B2 is the description. Each sample is +or- rows. Sample 1 may be 8 rows but sample 2 may be 6 rows. What type of formula or macro do I use to arrange the samples into columns instead of rows so the data will be like A1 name, A2 description, etc... and A2 will be sample 2 name, and B2 will be sample 2 description, etc...

View 7 Replies View Related

Rearrange Dataset From Columns To Rows

Feb 23, 2014

Rearrange dataset from columns to rows

However, the solutions do not work if "species " are text instead of numbers:



View 4 Replies View Related

Rearrange Columns In Particular Manner In A Table

May 9, 2014

I create a report where in I need to rearrange the columns in a particular manner as required by the client. Any macro which will will work as per following criteria.

Column headers start from A1 onwards (the last column header might vary depending on the creator, I get the report from a different team). The number of rows of data might vary.

For changing the order of columns, the macro should look for a column with the header "Close Date", cut it and insert is before a column with header "Bookings". Then, the macro should look for a column with the header "Description", cut it and insert is before a column with header "Selling BU". The macro needs to then cut two adjacent columns namely "Sector" and "Sub-Area" and insert it before the column with header "Total Bookings".

View 2 Replies View Related

Copyrights 2005-15, All rights reserved