Free Microsoft Excel 2013 Quick Reference

Sort columns by header row

Is there a simple way to sort (rearrange) column position based on a text string in the first row?

I'm not asking to sort the values in the columns, just want to present the columns in alphabetical order of the column name.

Thanks,
Jim


Hi, i'm a real noob here so please bear with me. Here is where i'm at. I had 150+ lists with contact data i.e. Company name, phone number, Address Ect. Each list had header rows. To combine the lists i went through dos and did a copy *.csv C:masterlist.csv. This worked great at combining the lists, however, I now have misaligned data. I need to know how i can shift or align cells/rows/columns based on a header row... if it is even possible.

Example:
Company Address1 City State Zip Unique#
Janes tavern 1234 blue street LA CA 12345 555555
Joe's Tavern 1233 Green St LA CA 155654 666666
Jill's Tavern 12345 purple LA CA 54621 77777
Gary's Tavern 12345 Orange St LA CA 56455 88888
Uniqu# Company Address City State Zip Comments
99999999 Andy's Tavern 1234 4th St La CA 5464 greatplace
10101 Hum's Tavern 456 s. 8th LA CA 5464 good food

Make sense? I just need to shift the columns around to match by header row of each list. If header row does not exist, then append to a new column.

Hello,

I'm trying to sort columns by clicking the heading, as well. I tried using RoyUK's code in response to UKCreation's query. Code doesn't seem to work, however

Any suggestions why? I do have a header row... and the range containing my dataset is A15:I35.

The error I get is Sort Method of Range Class failed.

This is how my code looks like:

	VB:
	
) 
    Dim LastCol 
    Dim LastCell 
     
     
    If Selection.Cells.Count > 1 Then Exit Sub 
     
    LastCol = Sheets(vItemRankingSheet).Range("$IV$15").End(xlToLeft).Column 
     
    LastCell = Sheets(vItemRankingSheet).Cells(15, LastCol).Address 
     
    Set Target = Range("a15:" & LastCell) 'header rows
     
     
    Select Case tglSort.Value 
    Case True 
        ActiveSheet.Range("A15:I35").Sort Key1:=ActiveCell, Order1:=xlAscending, Header:= _ 
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    Case False 
        ActiveSheet.UsedRange.Sort Key1:=ActiveCell, Order1:=xlDescending, Header:= _ 
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    End Select 
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is based off of Roy's original suggestion:

	VB:
	
 
 
 '---------------------------------------------------------------------------------------
 ' Procedure : Worksheet_BeforeDoubleClick
 ' DateTime  : 07/03/2006 11:02
 ' Author    : royUK
 ' Web site  : www.excel-it.com
 ' Purpose   : Sort selected column
 
 '---------------------------------------------------------------------------------------
 '
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    If Selection.Cells.Count > 1 Then Exit Sub 
    Dim i      As Integer 
    Dim sOrder As String 
    Set Target = Range("a5:d5") 'header rows
    i = InputBox("Do you want to sort Ascending or Descending," & vbNewLine & "Enter 1 for Ascending, 2 for Descending",
"Sort Order") 
    Select Case i 
    Case 1 
        sOrder = xlAscending 
    Case 2 
        sOrder = xlDescending 
    Case Else 
        Exit Sub 
    End Select 
    ActiveSheet.UsedRange.Sort Key1:=ActiveCell, Order1:=sOrder, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any help you can provide!

GWeasley

Hi there-

Help...I am looking for a better solution working with columns in VBA.

Currently, I refer to columns by their letter or # in my VBA code.

For example:
Code:
I have a scenario where I have to work with a dynamic data source that scrambles columns around (column is not always in the
same position on the spreadsheet).  Currenct...Also, there may be missing columns or new columns that need to be
recognized.

What I would like to do is refer to my columns by header name.

For example (PSUEDO CODE):
Code:
Goto ErrorHandler
Else
Columns("MTD REV").FormulaR1C1 = "=+RC[MTD BUD]"
End if
Would the best way to go about something like this be to create an array with column header names that I can run things against? How would this look?

(column headers are always in row 2)

I want to copy =sum(a2:a3) across 5 columns incrementing the row by 1 per column... then I want to copy the formulas down 10 rows... so changes would only occur when the formula is copied across columns... ie Column A would only sum a2:a3, a3:a4, a3:a5 etc... Column B (incremented by one row) would contain sums a2:a4, a3:a5, a4:a6 etc...I know how to do this by hand but I use a vlookup function that uses these formulas that use 250 columns by 3000 rows so I am interested in learning how to change the copy over to next column without having to Cntrl F all 250 columns... Simple example... formula is copied across columns incrementing row by 1 and then has ability to copy formula down to still have the same functionality as the example.Column A
=SUM(A1:A2)=SUM(A2:A3)=SUM(A3:A4)=SUM(A4:A5)=SUM(A5:A6)

ColumnB
=SUM(A1:A3)=SUM(A2:A4)=SUM(A3:A5)=SUM(A4:A6)=SUM(A5:A7)

ColumnC
=SUM(A1:A4)=SUM(A2:A5)=SUM(A3:A6)=SUM(A4:A7)=SUM(A5:A8)

ColumnD
=SUM(A1:A5)=SUM(A2:A6)=SUM(A3:A7)=SUM(A4:A8)=SUM(A5:A9)

I have a two part question. I've searched around, but haven't had any luck finding an answer.

I need to copy certain columns by header since the columns are not always in the same place. For example, I have a sheet with columns labeled "1-5" and I need to copy columns 1-3, and 5.

The second part is that I don't want to copy the whole column, just up to the last cell of data. I've had luck with xldown/xlup, however, this data is non-contiguous, so there are blank cells mixed-in which will render these commands useless.

Any help is greatly appreciated. Thanks! Unfortunately, I can't provide any sample spreadsheet data as this is sensitive information. I hope I explained myself well enough. Thank you!

I think it would be helpful to sort columns by color. Sometimes it helps for
me to add colors to certain columns which visually identifies its properties.
It would be helpful to not only sort by column names, but sort by color as
well.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi everyone,

I have the following problem that I hope somebody can help me with.

I have a table, that I want to sort in terms of row values (making the existing columns corresponding with the new row order). Essentially, I want to be able to select a row name from a dropdown list and have it automatically sort the columns by the values in that row. Doing the dropdown list is straight forward enough, and I can make the code for sorting rows based on column values, but not the other way around , as shown below:

For example:

X Y Z
Age 74 53 62
Size 11 5 13

Becomes the following after sorting by age, for example: where Age is defined in a dropdown list cell.

Y Z X
Age 53 62 74
Size 5 13 11

I've tried using the following code:, where A1 in the case above, would be Age and the Range defines the table (including the row headings and column headings), but I just get a runtime error 1004. If I do the equivalent thing for sorting column values (change the data range to fit just the column and change to xlSortColumns/xlToptoBottom) it works for columns, but I can't get it to work for rows.


	VB:
	
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address = "$A$1" Then 
        myKey = Range("A1") 
        Range("G1:I4").Sort Key1:=myKey, Order1:=xlAscending, Header:=xlGuess, _          OrderCustom:=1, MatchCase:=False,
Orientation:=xlSortRows, _ 
        DataOption1:=xlSortNormal 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks a lot for the help. I've asked on a few other forums and I've not managed to get this sorted (!) so I hope this time will be different.

Hi everyone,

I have the following problem that I hope somebody can help me with.

I have a table, that I want to sort in terms of row values (making the existing columns corresponding with the new row order). Essentially, I want to be able to select a row name from a dropdown list and have it automatically sort the columns by the values in that row. Doing the dropdown list is straight forward enough, and I can make the code for sorting rows based on column values, but not the other way around , as shown below:

For example:

X Y Z
Age 74 53 62
Size 11 5 13

Becomes the following after sorting by age, for example: where Age is defined in a dropdown list cell.

Y Z X
Age 53 62 74
Size 5 13 11

I've tried using the following code:, where A1 in the case above, would be Age and the Range defines the table (including the row headings and column headings), but I just get a runtime error 1004. If I do the equivalent thing for sorting column values (change the data range to fit just the column and change to xlSortColumns/xlToptoBottom) it works for columns, but I can't get it to work for rows.

Code:

Private Sub Worksheet_Change(ByVal Target
As Range)
If Target.Address = "$A$1" Then
myKey = Range("A1")
Range("G1:I4").Sort Key1:=myKey, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False,
Orientation:=xlSortRows, _
DataOption1:=xlSortNormal
End If
End Sub
Also I've tried using the macro recorder to figure out a route, but that selects the row manually and I'm finding it difficult to integrate the search code into that code so that it sorts only a named row.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Range("G1:J4").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("$B$3"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("G1:J4")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
Thanks a lot for the help!

See sample picture of my worksheet data. Trying to find the last cell (by columns) containing data BELOW the header row (row 1) such that row 1 is NOT examined when looking for the last DATA cell.

In my example, then, cell P14 with the value "38.47" is what I want the VBA code to find, NOT cell AD1 with the header. Ultimately, I want the VBA code to also remove all the empty columns following the column with the 38.47 that just have headers but no data beneath them (rows 2:end all blank).

Thanks to anyone who can help!

Hello everybody,

probably a simple thing, but i cant locate the answer or figure out the syntax. what is the code to sort a spreadsheet in VBA code by HEADER rows?

i have 2 rows i want to sort by: first by Column D row 1, then by column C row 1.

thankyou for your attention.

EDIT:

i apologize i already figured it out. no followup necessary. here is how i did it:


	VB:
	
 
Cells.Select 
Range("C1").Activate 
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _ 
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ 
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ 
:=xlSortNormal 
Range("C2").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
selects all cells, sorts, then de-selects.

anyway, thanks for looking and i hope i at least helped somebody out. =)

Hi

Row 1 is a header row.

I'm looking to be able to sort (A-Z) by any column in the sheet (column range is A to M) by double clicking any cell in that column. I've tried using macro recorder to get the code for this without any luck - I must be doing something wrong

Is this possible please? 'd be very grateful if you can help me - thank you!

In Excel XP on Windows XP, I need to freeze the first row (header row)
so that it will always appear at the top of every page and its data will
not be included in sorting. Only data below the first row should be
sorted. How do I do that? At present whenever I sort by any column, the
header row gets sorted along with the other data. That is unacceptable.

Lt. Ito

Hi,
I have a file in which some client names are repeatedly so many times in D column. In E Column there is fixed data either Purchase or Sales. I want to sort the E column first in ascending order then after D column in the same order. Then select D column & insert a row after the end of each name & also copy the heading of 1st row. For ex if a name Bob is repeatedly 7 times then I need a row insert after 8.

Hello,

I know this is an easy question but was hoping to get some help because it's taking me too long to figure out. I was looking to sort severy columns by depart date with the most current date showing at the bottom. Each row will contain information associated with these to dates, for example, trip expenses. The rows are entered by the end user and are sometimes put in after the fact and don't always sort chornologically. Therfore, what is the best way to format this sheet so the dates and the other associated information in others cells surrounding these dates properly stays together in the the row....each row is a record. I don't want to the user to sort every time using the A-Z sort. Should I build a MACRO or does 2007 have a feature you can autosort columns somwhere?

Also, do you recommend I build a form to accomodate data entry?

Thanks in advance,

Hello All,

so for some reason, sometimes when i create spreadsheets, and i try to alphabetize a column... the header row is sorted into the list as well.

For example, if i were to click the 2nd row (aaa, or a2)
Code:
Header 1 | Header 2| Header 3| etc
aaa
bbb
ccc
ddd
eee
fff
ggg
hhh
iii
jjj
kkk
would become

Code:
aaa
bbb
ccc
ddd
eee
fff
ggg
Header 1 | Header 2| Header 3| etc
hhh
iii
jjj
kkk

anyone know of a way to stop this... or why it only happens on some spreadsheets?

does it have to do with the data type in the header row?

I have a file i am working with... i used to be able to click on cell A2 and sort, and the header info would stay put (its even split and frozen), and for some reason, now, if i do the sort, it moves my header row too.

AGGGHHH!

I want to sort by no header row, colum M, the colum N, then colum O.
I do not want to have to insert this data everytime I sort. How can I
preserve the order of this sort?

In Exel; is there a way to sort Column A and have the information in each row
follow the change?
For example: A5 has 4567 in Column A, with information about 4567 in
different columns but on the same row.
However, when I enter 3456 in Column A, it sorts by number (which I want it
to do), but it leaves the information about 4567 in that row. I need the
information to correspond with the number in Column A.
Does anyone know how to correct this problem?

I have three rows at the top of my Excel spreadsheet. One row is the column
names; the other two rows contain data used in formulae elsewhere in the
spreadsheet. Is there any way to do it without first selecting the data? I've
found this method is very prone to error.

How do I sort the data without sorting in the three header rows? Designating
"header row" in the sort dialogue only identifies one row.

I have three rows at the top of my Excel spreadsheet. One row is the column
names; the other two rows contain data used in formulae elsewhere in the
spreadsheet. Is there any way to do it without first selecting the data? I've
found this method is very prone to error.

How do I sort the data without sorting in the three header rows? Designating
"header row" in the sort dialogue only identifies one row.

Is it possible to sort a column by the number of chars in a cell ie:

all the cells with a high number of chars at the top row

and the lowest number of chars at the bottom row?

Thanks for any help.

I am trying sort data by headers in a situation where I have a lot of
variables but some are missing. I have all my data like this:

City A Value for variable 1
City B Value for variable 1
City C Value for variable 1

City A Value for variable 2
City B Value for variable 2
City C Value for variable 2

My problem is, that sometimes I do not have the data for some variable for a
given city. For these cities, I do not have the city name either. To do
plots, I put the data into the same worksheet in adjacent columns.

What happens is this:
City A Value for variable 1 City A Value for variable 2
City B Value for variable 1 City C Value for variable 2
City C Value for variable 1 City D Value for variable 2
City D Value for variable 1 City E Value for variable 2

This is what I would need:
City A Value for variable 1 City A Value for variable 2
City B Value for variable 1
City C Value for variable 1 City C Value for variable 2
City D Value for variable 1 City D Value for variable 2

I would like to do this for all my variables for an end result like this:
City A Value for variable 1 Value for variable 2 Value for variable 3
City B Value for variable 1 N/A Value for
variable 3
City C Value for variable 1 Value for variable 2 N/A
City D Value for variable 1 Value for variable 2 Value for variable 3
Etc.

Any advice will be greatly appreciated.

I am usually pretty good at figuring out how to accomplish things in Excel, but this seems like a very remedial task that is continually eluding me. Normally, when I setup a spreadsheet, Excel automatically recognizes the top row as a header and does not sort it with the rest of the data; however, in one of my recent spreadsheets, this is not the case. When I go to sort a particular column, the header row gets mixed in with the rest. Is there a way to designate a particular row as a header?

I know i can sort columns using the sort ascending/descending button, but is it possible to perform the same function on a list but by clicking on the list heading?

for example if i click on "date", the cells below will sort themselves chronologically.

furthermore, is it possible to switch between ascending and descending? and will it also work with text as well as numbers?

many thanks

Hi,

Ive written the macro below to sort Column A according to the length of the cell values in Ascending order. Ive done this by writing the length to Column B for each value and then sorting on column B. Surely there is a more elegant method, perhaps using arrays. Could anyone help me create one?

Thx!


	VB:
	
 SortIt() 
     
    Range("A1").Select 
     
    Do Until IsEmpty(ActiveCell) 
        ActiveCell.Offset(0, 1).Value = Len(ActiveCell) 
        ActiveCell.Offset(1, 0).Select 
    Loop 
     
    Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines