Order values, return adjacent cell value

[ATSJ]

I have the following table structure

A	B	C
Allan	12	21
Bob	15	9
Chris	17	15
Dave	22	7

<tbody>
</tbody>

And I would like to use a formula that orders the values in columns B and C and returns the corresponding value in column A, as below...

A	B
Dave	22
Allan	21
Chris	17
Bob	15
Chris	15
Allan	12
Bob	9
Dave	7

<tbody>
</tbody>

Currently I have code that would order the values in one column, say B and then return A - but not two columns of values.

Any help is appreciated.

 

[mumps]

Assuming that row 1 has headers, try:

Sub ATSJ()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).Cut Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
    Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet1").Sort.SortFields.Clear
    Sheets("Sheet1").Sort.SortFields.Clear
    Sheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Sheets("Sheet1").Sort
        .SetRange Range("A1:C" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

 

[Aladin Akyurek]

A formula approach...

Allan	12	21		Dave	22
Bob	15	9		Allan	21
Chris	17	15		Chris	17
Dave	22	7		Bob	15
				Chris	15
				Allan	12
				Bob	9
				Dave	7

<tbody>
</tbody>

In E2 control+shift+enter, not just enter, and copy down:

=IF($F2="","",INDEX($A$2:$A$5,SMALL(IF($B$2:$C$5=$F2,ROW($A$2:$A$5)-ROW($A$2)+1),
    COUNTIFS($F$2:F2,F2))))

In F2 just enter and copy down:

=IF(ROWS($F$2:F2)>COUNT($B$2:$C$5),"",LARGE($B$2:$C$5,ROWS($F$2:F2)))

 

[ATSJ]

Thanks for the help folks, I like a formula based solution better in this particular case but good to have an alternative that works.

 

[Aladin Akyurek]

You are welcome.

 

[mumps]

Glad to help.