Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank and return Names
Hi All,
I've a data from Col A to Col C, Column A contains names and column B contains Quantily. I've used Rank condition in column C. I want a formula to return names of top five in column F and quantity in column G A B C D E F User 1 10 4 User 3 20 User 2 5 7 User 7 14 User 3 20 1 User 6 11 User 4 6 6 User 1 10 User 5 7 5 User 5 7 User 6 11 3 User 7 14 2 User 8 3 8 I can use Vlookup to get details in column F but would need help in finding a formula to get Column E. Thanks. -- Karthi |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank and return Names
In cell E1 and copy down
=INDEX($A$1:$A$10,MATCH(LARGE($B$1:$B$10,ROW(A1)), $B$1:$B$10,0)) In cell F1 and copy down =LARGE($B$1:$B$10,ROW(A1)) -- Jacob "Karthik" wrote: Hi All, I've a data from Col A to Col C, Column A contains names and column B contains Quantily. I've used Rank condition in column C. I want a formula to return names of top five in column F and quantity in column G A B C D E F User 1 10 4 User 3 20 User 2 5 7 User 7 14 User 3 20 1 User 6 11 User 4 6 6 User 1 10 User 5 7 5 User 5 7 User 6 11 3 User 7 14 2 User 8 3 8 I can use Vlookup to get details in column F but would need help in finding a formula to get Column E. Thanks. -- Karthi |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank and return Names
Thanks for your kind reply.
Formula works fine when all the ranks are different, but it doesn't return a name if two users have same rank. I'm not sure where I'm going wrong. Please let me know how to get the names of top 5 Ranks. I've used =Rank(A1,$A$1:$A$10) in column C. -- Karthi "Jacob Skaria" wrote: In cell E1 and copy down =INDEX($A$1:$A$10,MATCH(LARGE($B$1:$B$10,ROW(A1)), $B$1:$B$10,0)) In cell F1 and copy down =LARGE($B$1:$B$10,ROW(A1)) -- Jacob "Karthik" wrote: Hi All, I've a data from Col A to Col C, Column A contains names and column B contains Quantily. I've used Rank condition in column C. I want a formula to return names of top five in column F and quantity in column G A B C D E F User 1 10 4 User 3 20 User 2 5 7 User 7 14 User 3 20 1 User 6 11 User 4 6 6 User 1 10 User 5 7 5 User 5 7 User 6 11 3 User 7 14 2 User 8 3 8 I can use Vlookup to get details in column F but would need help in finding a formula to get Column E. Thanks. -- Karthi |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank and return Names
Hi Karthik
Try the below In cell E1 and copy down. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10=F1,ROW($B$1: $B$10)), COUNTIF($F$1:F1,F1))) In cell F1 (copy down as required) =LARGE($B$1:$B$10,ROW(A1)) -- Jacob "Karthik" wrote: Thanks for your kind reply. Formula works fine when all the ranks are different, but it doesn't return a name if two users have same rank. I'm not sure where I'm going wrong. Please let me know how to get the names of top 5 Ranks. I've used =Rank(A1,$A$1:$A$10) in column C. -- Karthi "Jacob Skaria" wrote: In cell E1 and copy down =INDEX($A$1:$A$10,MATCH(LARGE($B$1:$B$10,ROW(A1)), $B$1:$B$10,0)) In cell F1 and copy down =LARGE($B$1:$B$10,ROW(A1)) -- Jacob "Karthik" wrote: Hi All, I've a data from Col A to Col C, Column A contains names and column B contains Quantily. I've used Rank condition in column C. I want a formula to return names of top five in column F and quantity in column G A B C D E F User 1 10 4 User 3 20 User 2 5 7 User 7 14 User 3 20 1 User 6 11 User 4 6 6 User 1 10 User 5 7 5 User 5 7 User 6 11 3 User 7 14 2 User 8 3 8 I can use Vlookup to get details in column F but would need help in finding a formula to get Column E. Thanks. -- Karthi |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank and return Names
Thanks Micky and Jacob.... Both methods worked pretty well...
Again Thanks a lot..... -- Karthi "מיכאל (מיקי) אבידן" wrote: In your(!) Individual/Private case you may: In cell F1: =LARGE($B$1:$B$8,ROW()) and copy down. In cell E1: ="User "&MATCH(F1,$B$1:$B$8,) and copy down. Micky "Karthik" wrote: Hi All, I've a data from Col A to Col C, Column A contains names and column B contains Quantily. I've used Rank condition in column C. I want a formula to return names of top five in column F and quantity in column G A B C D E F User 1 10 4 User 3 20 User 2 5 7 User 7 14 User 3 20 1 User 6 11 User 4 6 6 User 1 10 User 5 7 5 User 5 7 User 6 11 3 User 7 14 2 User 8 3 8 I can use Vlookup to get details in column F but would need help in finding a formula to get Column E. Thanks. -- Karthi |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rank and return Names
Hello Karthi,
I suggest to use this approach: http://sulprobil.com/html/sorting.html Please see also http://sulprobil.com/html/rank.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return a list of names, from a large list of repeated names. | Excel Worksheet Functions | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Sumproduct if rank <=5 if not return 0 | Excel Discussion (Misc queries) | |||
Rank and return column header | Excel Discussion (Misc queries) | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |