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?

 

[Peter_SSs]

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 !

They are just standard arguments of the AGGREGATE function. 15 is for SMALL and 6 is to ignore errors. You can read about them here

The fact that Excel 2007 does not have AGGREGATE is why I prefaced my original suggestion with "If you are using Excel 2010 or later ..." & why I have provided an alternative, that does work with Excel 2007, in post #8

 

[frankyalta]

Hi Hiker95,

a code is nicer than a formulas but this code is for column A and B, and i expect on next couple days i'm going to add more columns (may be 10 pairs more as col A and B)
so in order to expand the range i need to add a syntax in the code called Step and/or offset?,
i'm not sure where to added in the code.

i'm planning to add or modify it as:
(A+B),(C+D),(E+F),(J+K) etc..


Thank you !

 

[frankyalta]

Hi Peter_SSs,

I must switch for newer excel version, perhaps 2013.

so AGGREGATE function work with 19 numbers that represent 19 functions,
with AGGREGATE just place a number that represent a formula.
WOW!!


thank you Peter_SSs

 

[Peter_SSs]

a code is nicer than a formulas ..

That seems at odds with:

that's why i'm looking for formulas ...

... i'm going to add more columns ...

Could you give a small data sample with at least a few more columns and the expected result(s)?

 

[frankyalta]

Hi Peter_ SSs

i can't attach a sample file, can i post a image?


thanks

 

[Peter_SSs]

Hi Peter_ SSs

i can't attach a sample file, can i post a image?


thanks

An image is not much good as we cannot copy to our sheets to test.
Either just like you did in post #1 would do, or my signature block below has some suggestions for posting small (copyable) screen shots directly into your post like I did in post #6 and hiker did in post #5.

 

[frankyalta]

Hi Peter_SSs,

at start i was avoiding to create a macro using CF (conditional format) because while creating and clicking the code become big and slow

that's why i'm looking for formulas ...

[image]https://dl.dropboxusercontent.com/u/70638562/NUM-Sorted-By-SCORE-SAMPLE.png [/image]



thanks

 

[Peter_SSs]

Hi Peter_SSs,

at start i was avoiding to create a macro using CF (conditional format) because while creating and clicking the code become big and slow



[image]https://dl.dropboxusercontent.com/u/70638562/NUM-Sorted-By-SCORE-SAMPLE.png [/image]



thanks

1. What does this have to do with Conditional Formatting? Your question is about sorting values.

2. I'm still also confused about formulas v macros. Which do you really want?

3. As I stated before, an image like that is no use, it would take me far too long to type out the data myself. You have to make it easy for your helpers .

 

[frankyalta]

Hi,

i'm sorry, wrongly i wrote the whole word [image] instead,
please can administrator edit to ??


thanks

 

[frankyalta]

Hi peter_SSs

i'm sorry i mean i was avoiding to create a macro to sort values using DATA->Filters->Sort, because while creating and clicking the code become big and slow
(sorry about conditional format, lol)


please hope not confuse you more..


thanks