Sorting each row one by one with column headings

[near]

Hi,
I have 100*374 data. With 374 columns, I want to sort each row of my data in descending order but keeping in mind that the respective column heading should also be sorted with the data.
For Example: BMW, Ferrari, Audi and Honda are the column headings in the first row and the other two rows are the data.


BMW......Ferrari.....Audi......Honda
32..........42........ 17..........19
50..........39.........40..........25


Now, Each Row should be sorted each time with their column headings


Ferrari..........BMW...........Honda.........Audi
42................32...............19.............17


BMW........Audi........Ferrari.......Honda
50............40.......... 39..............25




Any help will be greatly appreciated.


Note: I have places the same question on http://www.mrexcel.com/forum/newthread.php?do=newthread&f=10

 

[c_m_s_jr]

Perhaps the tip on this page will help.

http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

You could convert it to a data table, sort as need be, then transform back to a matrix layout. If you don't want to convert it to a data table, I would think you would need a new column header above each row of data.

 

[near]

Hi c_m_s_jr,
What do you mean by this statement "I would think you would need a new column header above each row of data."?

New column means?

 

[John Davis]

Hi,
I have 100*374 data. With 374 columns, I want to sort each row of my data in descending order but keeping in mind that the respective column heading should also be sorted with the data.
For Example: BMW, Ferrari, Audi and Honda are the column headings in the first row and the other two rows are the data.


BMW......Ferrari.....Audi......Honda
32..........42........ 17..........19
50..........39.........40..........25


Now, Each Row should be sorted each time with their column headings


Ferrari..........BMW...........Honda.........Audi
42................32...............19.............17


BMW........Audi........Ferrari.......Honda
50............40.......... 39..............25




Any help will be greatly appreciated.


Note: I have places the same question on http://www.mrexcel.com/forum/newthread.php?do=newthread&f=10

This assumes your data is on Sheet1 and the results will be at Sheet2?

Sub near()
Dim i As Long
Dim lr As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet1")

lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = lr To 2 Step -1

    Sheets("Sheet2").Range("A1:A3").EntireRow.Insert SHIFT:=xlDown
    ws.Activate
    Rows("1:1").Copy Sheets("Sheet2").Range("A1")
    Range("A" & i).EntireRow.Copy Sheets("Sheet2").Range("A2")
    Sheets("Sheet2").Activate
    Rows("1:2").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
    ws.Activate
    
Next i
  
End Sub

 

[near]

Thanks for your solution....
But I want the sorted result on the same sheet (not on sheet2) under the main data (one line after each time)

 

[John Davis]

Probably much better ways, this still uses Sheet2 as a helper.

Sub near()
Dim i As Long
Dim lr As Long
Dim ws As Worksheet

Set ws = Sheets("Sheet1")

lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = lr To 2 Step -1

    Sheets("Sheet2").Range("A1:A3").EntireRow.Insert SHIFT:=xlDown
    ws.Activate
    Rows("1:1").Copy Sheets("Sheet2").Range("A1")
    Range("A" & i).EntireRow.Copy Sheets("Sheet2").Range("A2")
    Sheets("Sheet2").Activate
    Rows("1:2").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
    ws.Activate
    
Next i

Sheets("Sheet2").Activate
Sheets("Sheet2").UsedRange.Cut ws.Range("A" & Rows.Count).End(3)(2)
  
End Sub