numbers w score - sort numbers with higher to least score

[frankyalta]

Hi,

I don't really know what title to write but i don't really know which nested formula i need to do this simple task.

I have 2 columns (1 column have numbers and each numbers have an score on second column)
I need to transpose the column with numbers and bellow i would like to display the score sorted by highest to least
example before achieve:

__Col_A____Col_B
1_Numb____Score
2__18_______50
3__01_______65
4__35_______100
5__06_______10
6__10_______25
7__02_______80

Need to achieve this output:

l________A_____l___B__l__C__l__D__l__E__l__F__l__G__
Row 19l________l______l_____l______l_____l_____l_____
Row 20lNumbersl__35__l__02_l__01__l_18_l_10__l_06__
Row 21lScore___l__100_l__80_l__65_ l_50_l_25__l_10__ <--- Sort by Highest to least score
Row 22l________l______l_____l______l_____l_____l_____

to my mind and google mind its cross nested formulas Match,Row, Small, if plus index, i tried but not success

please any advice?

 

[Teeroy]

Is this something you need to do often?
If it's a one off thing copy the data, sort by column B, copy the sorted data and paste special | transpose to get the output.

Some things are best left uncomplicated.

 

[frankyalta]

Hi Teeroy,

yes i'm going to do daily.that's why i'm looking for formulas with out using CF.

 

[Teeroy]

OK, so the two formula to suit your example are:

Numbers: =TRANSPOSE(INDEX($A$2:$A$7,MATCH(COLUMN(B2)-1,RANK($B$2:$B$7,$B$2:$B$7,0),0)))
Score: =TRANSPOSE(INDEX($B$2:$B$7,MATCH(COLUMN(B2)-1,RANK($B$2:$B$7,$B$2:$B$7,0),0)))

Both are array formulas and must be committed by Ctrl + Shift + Enter. Commit each formula into one cell first then fill sideways as required (until you hit the #N/A error or alternatively wrap in an IFERROR).

Note that there may be an issue if you have two identical scores.

 

[hiker95]

frankyalta,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

If your raw data is always in range A1:B7, then here is a macro solution for you to consider, based on your displayed flat text display, that uses three arrays in memory.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	Numbers	Score
2	18	50
3	01	65
4	35	100
5	06	10
6	10	25
7	02	80
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	Numbers	Score
2	18	50
3	01	65
4	35	100
5	06	10
6	10	25
7	02	80
8
9
10
11
12
13
14
15
16
17
18
19
20	Numbers	35	2	1	18	10	6
21	Score	100	80	65	50	25	10
22
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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 11/22/2015, ME904102
Dim ab As Variant, lr As Long
Dim a As Variant, b As Variant
Application.ScreenUpdating = False
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  ab = .Range("A1:B" & lr)
  .Range("A2:B" & lr).Sort key1:=.Range("B2"), order1:=2
  a = .Range("A1:A" & lr).Value
  b = .Range("B1:B" & lr).Value
  .Range("A20").Resize(, lr).Value = Application.Transpose(a)
  .Range("A21").Resize(, lr).Value = Application.Transpose(b)
  .Range("A1:B" & lr) = ab
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.


If you raw data can be less, or, more than 7 rows, I can adjust the macro accordingly so that there are 12 blank rows between the end of the raw data, and, the beginning of the results.

 

[Peter_SSs]

If you are using Excel 2010 or later you can also try these. They should still work even if scores in column B are repeated.

Both formula copied to the right.

Excel Workbook
	A	B	C	D	E	F	G
1	Numb	Score
2	18	50
3	01	65
4	35	100
5	06	10
6	10	25
7	02	80
8
19
20	Numb	35	02	01	18	10	06
21	Score	100	80	65	50	25	10
Sort

 

[frankyalta]

Hi All!!

wow, many responses... Thanks Teeroy, Hiker95 and Peter_SSs

i use excel 2007 on WIN 10 PC.

i see that still need to learn more about multiples nested formulas.

Thank you so much!!

 

[Peter_SSs]

i use excel 2007 on WIN 10 PC.

In that case you can use these formulas, copied across. Still work with repeated scores.

B20 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across.

Excel Workbook
	A	B	C	D	E	F	G
1	Numb	Score
2	18	50
3	01	65
4	35	100
5	06	10
6	10	25
7	02	80
8
19
20	Numb	35	02	01	18	10	06
21	Score	100	80	65	50	25	10
Sort

 

[frankyalta]

Hi Peter_SSs

excel 2007 doesn't have AGGREGATE Function , but i'm curious from where do you get the numbers in the formula (15,6 and (14,6 just next to AGGREGATE function?


thank you !

 

[hiker95]

frankyalta,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.