How to sort row values in excel and display corresponding column names elsewhere?

Question

Essentially, I have the excel sheet like this -

 A   B   C   D
16  14  13  12
14  14  13  14
16  14  15  12
14  14  15  14

I want the excel sheet to display this -

 A   B   C   D
16  14  13  12  A   B   C   D
14  14  13  14  A   B   D   C
16  14  15  12  A   C   B   D
14  14  15  14  C   A   B   D

In other words, beside each row, I want the descending order of column names. How do I achieve this? I tried sorting in several different ways, but none give the column name in another column, like what I want. Please help.

I use Excel 2007.

EDIT:

After going through this post, I used

=INDEX(L$1:S$1,MATCH(LARGE(L42:S42,1),L42:S42,0))
=INDEX(L$1:S$1,MATCH(LARGE(L42:S42,2),L42:S42,0))
=INDEX(L$1:S$1,MATCH(LARGE(L42:S42,3),L42:S42,0))
=INDEX(L$1:S$1,MATCH(LARGE(L42:S42,4),L42:S42,0))

to display column names corresponding to the 4 largest values in each row. But then again, when 2 or more values are equal, I'm getting output like this -

 A   B   C   D
16  14  13  12  A   B   C   D
14  14  13  14  A   A   A   C
16  14  15  12  A   C   B   D
14  14  15  14  C   A   A   A

How do I fix this to get the output I want (which I stated in the beginning)?

Answer 1

The answer has been accepted, and it is a good answer, but just for my own amusement I will post a variation:-

=INDEX($A$1:$D$1,MATCH(0,IF($A2:$D2=LARGE($A2:$D2,COLUMN()-COLUMN($E2)),COUNTIF($E2:E2,$A$1:$D$1),1),0))

This uses a standard COUNTIF/MATCH method to eliminate columns which have already been entered when there are ties and doesn't make any assumptions about the size of the numbers.

Again it is an array formula and must be entered with Ctrl Shift Enter

Answer 2

You must consider the positions of the values while determining the LARGEst value. If values are equal, then you must determine if the first of the equal values shall also comes first in the listing.

If there are only integer values, then we can take the positions of the values as decimal places.

Example:

Formula in E2, copied cross to E2:H5:

{=INDEX($A$1:$D$1,MATCH(LARGE($A2:$D2+(10-COLUMN($A2:$D2))/10,E$1),$A2:$D2+(10-COLUMN($A2:$D2))/10,0))}

This is an array formula. Input it without the curly brackets and finish with [Ctrl]+[Shift]+[Enter].

This works for up to 10 columns only. If more columns the parts (10-...)/10 have to be changed to (100-...)/100.

In this example the first of the equal values also comes first in the listing. If the last of the equal values shall comes first in the listing, then remove the 10- parts.

Highlighting the column names with same value is possible using Conditional Formatting.

1. Select E2:H6.
2. Call Conditional Formatting.
3. Apply a new rule using a formula, formula: =COUNTIF($A2:$D2,INDEX($A2:$D2,MATCH(LARGE($A2:$D2,E$1),$A2:$D2,0)))>1
4. Select your desired format.

Answer 3

Here is a VBA approach. It associates the location of the data value in the table with the column label; does a stable sort of the data values; and returns the column labels.

A potential advantage is that it will work for either text or numeric values; and can be made case-sensitive if desired.

It depends on the data table starting in A1, and that there not be anything else on the worksheet in column A or Row 1; as it uses those ranges to determine the last row and column to process.

To enter this Macro (Sub), alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window.

First, from the top menu, select Insert/Class Module and paste the Class Module code below into the window that opens. Select the Class Module and Rename it (hit F4 and change the Name property) cRow.

Then, from the top menu, select Insert/Module and paste the Regular Module code below into that window.

To use this Macro (Sub), ensure your data is showing on the active sheet. Then alt-F8 opens the macro dialog box. Select the macro by name, and .

Class Module

Option Explicit
Private pData As Variant
Private pColLabel As String

Public Property Get Data() As Variant
    Data = pData
End Property
Public Property Let Data(Value As Variant)
    pData = Value
End Property

Public Property Get ColLabel() As String
    ColLabel = pColLabel
End Property
Public Property Let ColLabel(Value As String)
    pColLabel = Value
End Property

---

Regular Module

Option Explicit
Option Compare Text
Sub SortAndColLabel()
    Dim vSrc As Variant, vRes() As Variant
    Dim cR As cRow, colR As Collection
    Dim LastRow As Long, LastCol As Long
    Dim R As Range
    Dim I As Long, J As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

vSrc = Range(Cells(1, 1), Cells(LastRow, LastCol))
ReDim vRes(1 To UBound(vSrc, 1), 1 To UBound(vSrc, 2) * 2)

'Associate entries with column labels
For I = 2 To UBound(vSrc, 1)
Set colR = New Collection
    For J = 1 To UBound(vSrc, 2)
        Set cR = New cRow
        With cR
            .ColLabel = vSrc(1, J)
            .Data = vSrc(I, J)
            colR.Add cR
        End With
    Next J

    'Sort the row, need to use stable sort
    CollectionBubbleSort colR

    'Place in results array
    For J = 1 To colR.Count
        With colR(J)
            vRes(I, J) = vSrc(I, J)
            vRes(I, J + UBound(vSrc, 2)) = .ColLabel
        End With
    Next J
Next I

'Add Column Labels to Vres
For J = 1 To UBound(vSrc, 2)
    vRes(1, J) = vSrc(1, J)
Next J


'Write the results
Set R = Range("a1", Cells(UBound(vRes, 1), UBound(vRes, 2)))
With R
    .EntireColumn.Clear
    .Value = vRes
    .HorizontalAlignment = xlCenter
End With

End Sub

'---------------------------------------------------------------
'Could use faster sort routine if necessary
Sub CollectionBubbleSort(TempCol As Collection)
'Must manually insert element of collection to sort on in this version
    Dim I As Long
    Dim NoExchanges As Boolean

    ' Loop until no more "exchanges" are made.
    Do
        NoExchanges = True

        ' Loop through each element in the array.
        For I = 1 To TempCol.Count - 1

            ' If the element is greater than the element
            ' following it, exchange the two elements.
            If TempCol(I).Data < TempCol(I + 1).Data Then
                NoExchanges = False
                TempCol.Add TempCol(I), after:=I + 1
                TempCol.Remove I
            End If
        Next I
    Loop While Not (NoExchanges)
End Sub

---