ExcelBanter - Finding the next same rank in data

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Finding the next same rank in data (https://www.excelbanter.com/excel-discussion-misc-queries/242100-finding-next-same-rank-data.html)

Finding the next same rank in data

I will simplyfy my request to two columns of data; A containing a unique ID
and B containing random values (which can be duplicated).

I want to extract the top 10 IDs based on their values, I was able to do
this via an "index" on a "match" on a "large", but if I have two or more
tieing entries, the first found ID is shown for all.

Is there away to find (lookup or match) the next joint ranked item?

Many thanks in advance

Finding the next same rank in data

When there are duplicate numeric values (ties) a top n list may have more
than n values that fall within the top n. For example:

5
5
4
4
4

If you want the top 3, due to the ties there are actually 5 values that fall
within the top 3.

Let's assume the ID's are in the range A1:A20. Number values in the range
B1:B20.

Enter this formula in D2. This will return the count of how many numbers
fall within the top 10:

=COUNTIF(B1:B20,"="&LARGE(B1:B20,10))

Enter this formula in E2. This will return the top 10 numbers:

=IF(ROWS(E$2:E2)<=D$2,LARGE(B$1:B$20,ROWS(E$2:E2)) ,"")

Enter this array formula** in F2. This will return the correspond ID:

=IF(E2="","",INDEX(A$1:A$20,SMALL(IF(B$1:B$20=E2,R OW(B$1:B$20)-MIN(ROW(B$1:B$20))+1),COUNTIF(E$2:E2,E2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Select E2:F2 and copy down until you get blanks.

--
Biff
Microsoft Excel MVP

"steven.holloway" wrote in
message ...
I will simplyfy my request to two columns of data; A containing a unique ID
and B containing random values (which can be duplicated).

I want to extract the top 10 IDs based on their values, I was able to do
this via an "index" on a "match" on a "large", but if I have two or more
tieing entries, the first found ID is shown for all.

Is there away to find (lookup or match) the next joint ranked item?

Many thanks in advance

Finding the next same rank in data

Thank you, but I forgot to mention I don't want to use the CTRL+SHIFT+ENTER
formulas (I have extensive large database and history of performance issues
with using this type of formula in large data sets).

Also I am not looking at adding another column of pre-work formulas if
possible to get the desired result (otherwise I could just do a large+row
number which bye-passes the array formula issue and gives the same desired
result without any of the fuss).

If there are no other direct suggestions, I will simply record a Macro to
pivot the top 10 ID's and then vlookup this list which will save a massive
amount of storage/performance. - This is not ideal as I have users still on
Excel 2003 and we all know the issues with pivot tables and versions!!!

- Excel 2007 is just so awful now with self-contained databases - 2003
wipes the floor with-it with ease! - Own goal by Microsoft!

"T. Valko" wrote:

When there are duplicate numeric values (ties) a top n list may have more
than n values that fall within the top n. For example:

5
5
4
4
4

If you want the top 3, due to the ties there are actually 5 values that fall
within the top 3.

Let's assume the ID's are in the range A1:A20. Number values in the range
B1:B20.

Enter this formula in D2. This will return the count of how many numbers
fall within the top 10:

=COUNTIF(B1:B20,"="&LARGE(B1:B20,10))

Enter this formula in E2. This will return the top 10 numbers:

=IF(ROWS(E$2:E2)<=D$2,LARGE(B$1:B$20,ROWS(E$2:E2)) ,"")

Enter this array formula** in F2. This will return the correspond ID:

=IF(E2="","",INDEX(A$1:A$20,SMALL(IF(B$1:B$20=E2,R OW(B$1:B$20)-MIN(ROW(B$1:B$20))+1),COUNTIF(E$2:E2,E2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Select E2:F2 and copy down until you get blanks.

--
Biff
Microsoft Excel MVP

"steven.holloway" wrote in
message ...
I will simplyfy my request to two columns of data; A containing a unique ID
and B containing random values (which can be duplicated).

I want to extract the top 10 IDs based on their values, I was able to do
this via an "index" on a "match" on a "large", but if I have two or more
tieing entries, the first found ID is shown for all.

Is there away to find (lookup or match) the next joint ranked item?

Many thanks in advance

Finding the next same rank in data

Well, if you don't want to use array formulas or helper (intermediate)
calculations then I guess the pivot is your only other alternative.

--
Biff
Microsoft Excel MVP

"steven.holloway" wrote in
message ...
Thank you, but I forgot to mention I don't want to use the
CTRL+SHIFT+ENTER
formulas (I have extensive large database and history of performance
issues
with using this type of formula in large data sets).

Also I am not looking at adding another column of pre-work formulas if
possible to get the desired result (otherwise I could just do a large+row
number which bye-passes the array formula issue and gives the same desired
result without any of the fuss).

If there are no other direct suggestions, I will simply record a Macro to
pivot the top 10 ID's and then vlookup this list which will save a massive
amount of storage/performance. - This is not ideal as I have users still
on
Excel 2003 and we all know the issues with pivot tables and versions!!!

- Excel 2007 is just so awful now with self-contained databases - 2003
wipes the floor with-it with ease! - Own goal by Microsoft!

"T. Valko" wrote:

When there are duplicate numeric values (ties) a top n list may have more
than n values that fall within the top n. For example:

5
5
4
4
4

If you want the top 3, due to the ties there are actually 5 values that
fall
within the top 3.

Let's assume the ID's are in the range A1:A20. Number values in the range
B1:B20.

Enter this formula in D2. This will return the count of how many numbers
fall within the top 10:

=COUNTIF(B1:B20,"="&LARGE(B1:B20,10))

Enter this formula in E2. This will return the top 10 numbers:

=IF(ROWS(E$2:E2)<=D$2,LARGE(B$1:B$20,ROWS(E$2:E2)) ,"")

Enter this array formula** in F2. This will return the correspond ID:

=IF(E2="","",INDEX(A$1:A$20,SMALL(IF(B$1:B$20=E2,R OW(B$1:B$20)-MIN(ROW(B$1:B$20))+1),COUNTIF(E$2:E2,E2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Select E2:F2 and copy down until you get blanks.

--
Biff
Microsoft Excel MVP

"steven.holloway" wrote in
message ...
I will simplyfy my request to two columns of data; A containing a unique
ID
and B containing random values (which can be duplicated).

I want to extract the top 10 IDs based on their values, I was able to
do
this via an "index" on a "match" on a "large", but if I have two or
more
tieing entries, the first found ID is shown for all.

Is there away to find (lookup or match) the next joint ranked item?

Many thanks in advance