Sorting horizontally and adding text

[dunk123]

So, I know how to sort horizontally (easy enough) but this goes beyond that. I've tried formulas but they're getting out of hand... I need a macro.

Here's what I have:
Column H Column I Column J Column K Column L
Item 1 Item 2 Item 3 Item 4 Item 5
26 52 37 65 10
45 9 6 5 89

I need to sort each row, greatest to least and add the "Item ?" to the cell. Here's how it would look once done (assuming the macro will not override the current data, but use further columns):
Column M Column N Column O Column P Column Q

Item 4 65 Item 2 52 Item 3 37 Item 1 26 Item 5 10
Item 5 89 Item 1 45 Item 2 9 Item 3 6 Item 4 5

Row 1 would be blank in the new columns as it contains the header row.

Any ideas? Thanks all

 

[Marcelo Branco]

Hi,

If formulas are ok, maybe this

H J......Q
<TABLE style="WIDTH: 480pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=640><COLGROUP><COL style="WIDTH: 48pt" span=10 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Item 1


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Item 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Item 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Item 4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Item 5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>26</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>52</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>37</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>65</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 4 65</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 2 52</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 3 37</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 1 26</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 5 10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>45


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2699691 class=xl64 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>89</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 5 89</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 1 45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 2 9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 3 6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Item 4 5</TD></TR></TBODY></TABLE>

Formula in M2
=INDEX($H$1:$L$1,MATCH(LARGE($H2:$L2,COLUMNS($M:M)),$H2:$L2,0))&" "&LARGE($H2:$L2,COLUMNS($M:M))

copy across till Q2 and down as needed

HTH

M.

 

[dunk123]

Formulas are fine. I tried some and couldn't get it. I'll piece this apart to see how it works. Awesome, thanks Marcelo.

 

[Marcelo Branco]

If you need a macro, maybe this

Sub Test()
    'Mr. Excel Forum Thread 546734
    Dim lastRow As Long
 
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
 
        .Range("M2").FormulaR1C1 = _
        "=INDEX(R1C8:R1C12,MATCH(LARGE(RC8:RC12,COLUMNS(C13:C)),RC8:RC12,0))&"" ""&LARGE(RC8:RC12,COLUMNS(C13:C))"
 
        .Range("M2").AutoFill Destination:=Range("M2:Q2"), Type:=xlFillDefault
        .Range("M2:Q2").AutoFill Destination:=Range("M2:Q" & lastRow), Type:=xlFillDefault
    End With
 
End Sub

HTH

M.

 

[Marcelo Branco]

Formulas are fine. I tried some and couldn't get it. I'll piece this apart to see how it works. Awesome, thanks Marcelo.

Glad to help

Tks for the feedback

M.

 

[dunk123]

Actually, I just noticed an anomaly. If two or more items have the same quantity, it shows the first item twice... It does not show the second item. Can this be fixed in the formula or are we back to a macro being required?

 

[dunk123]

And I just spotted the macro you posted and it too has the same issue (which makes sense as it's the same formula, just different delivery).

 

[hiker95]

dunk123,


Be right back.

 

[hiker95]

dunk123,

I used range R1:S5 as a small work area. If this is not any good, then let me know if that range contains any formulae?


Sampe data before the macro:

Excel Workbook
	H	I	J	K	L	M	N	O	P	Q
1	Item 1	Item 2	Item 3	Item 4	Item 5
2	26	52	37	65	10
3	45	9	6	5	89
4
Sheet1

After the macro:

Excel Workbook
	H	I	J	K	L	M	N	O	P	Q
1	Item 1	Item 2	Item 3	Item 4	Item 5
2	26	52	37	65	10	Item 4 65	Item 2 52	Item 3 37	Item 1 26	Item 5 10
3	45	9	6	5	89	Item 5 89	Item 1 45	Item 2 9	Item 3 6	Item 4 5
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub AddTextSort()
' hiker95, 04/28/2011
' http://www.mrexcel.com/forum/showthread.php?t=546734
Dim LR As Long, a As Long, b As Long, NR As Long
Application.ScreenUpdating = False
Columns("M:Q").ClearContents
LR = Cells(Rows.Count, 8).End(xlUp).Row
For a = 2 To LR Step 1
  NR = 0
  For b = 8 To 12 Step 1
    NR = NR + 1
    Cells(NR, "R") = Cells(a, b)
    Cells(NR, "S") = Cells(1, b) & " " & Cells(a, b)
  Next b
  Range("R1:S5").Sort Key1:=Range("R1"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
  Range("M" & a).Resize(, 5).Value = Application.Transpose(Range("S1:S5").Value)
  Range("R1:S5").ClearContents
Next a
Columns("M:Q").AutoFit
Application.ScreenUpdating = True
End Sub

Then run the AddTextSort macro.

 

[dunk123]

Ah, that works perfectly! Thank you so much for taking the time. Another trick to add to the collection.