Largest Value & Name. Error When Values Are Exactly The Same

[aedctalk]

Is there a way when im creating a chart of highest values using

=LARGE(IF(U40:AS40=0,,U40:AS40),1)
=LARGE(IF(U40:AS40=0,,U40:AS40),2)

And say when both of them are 4

then when i pull the names i just get the same persons name twice. Is there a way to get both different peoples names that both had 4?

Thank you!

 

[GlennUK]

How do you "pull the names" exactly?

 

[aedctalk]

Thanks!

Ok I have my values in AX149:AX154
I have the peoples names in AW149:AW154


Then in AN 150:AN152 i have the top 3 values

IN AL150:AL152 i have the names.. To match names to the top 3 values i use

=IF(ISERR(INDEX($AW$149:$AW$154,MATCH(AN150,$AX$149:$AX$154,0))),,INDEX($AW$149:$AW$154,MATCH(AN150,$AX$149:$AX$154,0)))

changing the AN150, to AN151, AN152 for 2nd and 3rd names..

If i have 2 people with same number though i get "Nick" twice when i'd like i t to show nick and megan.


Thanks for any insight / solutions! :P

 

[GlennUK]

You need an extra column with a unique ranking calculation, so that you get results, 1,2,3,4, etc etc, based on max first. Then you can pull results for 1,2,3 for values and for names.

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Amount	Rank		Top3	Amount	Name
2	Abe	400	10		1	1000	Dave
3	Bob	500	9		2	1000	Eddy
4	Carl	800	4		3	900	Larry
5	Dave	1000	1
6	Eddy	1000	2
7	Fred	600	6
8	George	600	7
9	Harry	200	13
10	Ike	800	5
11	John	600	8
12	Kev	300	11
13	Larry	900	3
14	Mike	300	12
15	Nick	200	14
Sheet4

 

[aedctalk]

thank you for your help!

I now have ranking working ok.

However i ran into another issue.




if you look at "top shortages" on right. i have this sheet split into two sections "morning" and night so i have rankings for the morning.. on top 3 uder "top shortages" and rankings for night as the bottom 3 under top shortages..

since both were ranked up to 41.... when i match my AO150 to the rankings in BE .. it doesnt work since i have two 41 rankings 2 40 rankings and 2 39 rankings..

Can you think of any workarounds? Would be willing to send a dummy file to anyone who thinks maybe they can conquer this for me. Been stuck quite a while on this

if i take away the doubles of 41, 40, 39.. the bottom 3 names show up correctly on the left..

its just this duplicate rankings issue since i have a morning and night part of the sheet... that is getting me stuck now

 

[GlennUK]

You say "I now have ranking working ok.", but it appears not, if you are getting 41 twice. Am I understanding you correctly?

 

[aedctalk]

I did not make sense. I do apologize.

The issue i'm having with ranking now is

I have a morning and evening section on one sheet.

Morning totals are on U43:AS43

Evening totals are on row U144:AS144

I now understand how to rank these sperately

However how do i get it to Rank both different rows?

IE: i can have rank 1 be on data on row 144 and rank 2 can be from data on row 43.

 

[GlennUK]

I don't understand ... what do you mean "how do i get it to Rank both different rows?" ?

Can you give an example of exactly what you are talking about ... a block of data and a block of expected results?

 

[aedctalk]

Hi! Thanks for your continued help!

Ok. Here is a image showing how my confusion in ranking

So how do i rank the above from 1 to 6 when the names and data are not all on the same rows?

Thanks so much :P

 

[GlennUK]

Look at this, and see if it helps:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Names of morning employees	John	Eddie	Miguel		Top 3	Amount	Name
2	Items rung up in morning	1	4	4		1	10	Shawn
3	( rank )	6	2	3		2	4	Eddie
4	Names of evening employees	Jessie	Adam	Shawn		3	4	Miguel
5	Items rung up in evening	2	4	10
6	( rank )	5	4	1
Sheet4