Top 3 Users using INDEX/MATCH with duplicate values, but not duplicate names

[Tazukie]

Hi all,


I'm trying to figure out how to get the names of the Top 3 highest values without duplicating names, but allowing duplicate values.

I have a list of (often repeating) names in table column [Last, First]. I have a single value relating to each name in table column [Used]. I have =SUMIF(Last, First],[@[Last, First]],[Used]) resulting in the sum of each name's used in the table column [Individual's Total].


I would like to pull the highest values from [Inidividual's Total] and MATCH their corresponding name at the top of the sheet


For example, my data looks like this-


[Last, First] [Used] [Individual's Total]
Albert 2 7
Betty 3 4
Charlie 3 3
Dorothy 4 4
Albert 2 7
Betty 1 4
Albert 3 7


In my example, Albert would be Rank 1 with a value of 7. Rank 2 would be a tie between Betty and Dorothy with a duplicate value of 4. My intent is to get something that looks like the follwing-


Albert 7
Betty 4
Dorothy 4


I've managed to get Albert and Betty to properly display, but third place is skipped since it's a duplicate value and instead shows Rank 3 as Charlie with a value of 3. My current formula is a bit of a Frankenstein using INDEX/MATCH though, so I'm welcome to ideas starting from scratch.


Thanks for looking!

 

[Marcelo Branco]

Welcome to Mr Excel forum

I think you need some complex array formulas.
What is the Table name?

M.

 

[Tazukie]

It's just Table13 at the moment.

 

[Marcelo Branco]

It's just Table13 at the moment.

Maybe something like this (Table13 in A1:C10)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Last, First​

[/TD]
[TD]

Used​

[/TD]
[TD]

Indivudual's Total​

[/TD]
[TD][/TD]
[TD]

Name​

[/TD]
[TD]

Top 3​

[/TD]
[TD][/TD]
[TD]

3th Largest​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Albert​

[/TD]
[TD]

2​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

Albert​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Betty​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

Betty​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Charlie​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

Charlie​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Dorothy​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

Dorothy​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Albert​

[/TD]
[TD]

2​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Betty​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Albert​

[/TD]
[TD]

3​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

John​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Charlie​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Helper cell to get the 3rd largest
Array formula in H2
=LARGE(IF(FREQUENCY(MATCH(Table13[Last, First],Table13[Last, First],0),ROW(Table13)-MIN(ROW(Table13))+1),Table13[Indivudual''s Total]),3)
Ctrl+Shift+Enter, not just Enter

Array formula in F2 copied down
=IFERROR(LARGE(IF(FREQUENCY(IF(Table13[Indivudual''s Total]>=H$2,MATCH(Table13[Last, First],Table13[Last, First],0)),ROW(Table13)-MIN(ROW(Table13))+1),Table13[Indivudual''s Total]),ROWS(F$2:F2)),"")
Ctrl+Shift+enter, not just Enter

Array formula in E2 copied down
=IF(F2="","",INDEX(Table13[Last, First],SMALL(IF(ISNA(MATCH(Table13[Last, First],K$1:K1,0)),IF(Table13[Indivudual''s Total]=F2,ROW(Table13)-MIN(ROW(Table13))+1)),1)))
Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Tazukie]

Wow, thanks for the effort that must have gone into that. I'm not even familiar with the FREQUENCY or ISNA functions and am only aware that IFERROR exists, but not how to use it.

I copied your functions and after some customizing to match cell placements, finally figured out the H2 fomula didn't work because my table had empty rows. It's a protected sheet so I started it off with 500 rows, but it's currently at 193. After deleting all rows after 193, it populated the cells.

However at that point, it was still repeating duplicates. In fact, in some arrangements, it even created duplicates that were single entries to begin with. For example- in your table, if you changed C3 to 7, so Albert and Betty are tied with 7, the table shows Albert in both E2 and E3. Betty should be in E3. Even if you remove all the Alberts from A:A except A2, it'll still result in Albert twice in E2 and E3.

I appreciate the time and thought that went into replying to my request. I'm going to look for another method to accomplish my goals a little more outside the box. This seems to require some skill a bit over my head.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1	individual	used	i.total			3
2	Albert	2	7			4
3	Betty	3	4			4
4	Charlie	3	3			individual	largest total
5	Dorothy	4	4			Albert	7
6	Albert	2	7			Aladin	7
7	Betty	1	4			Betty	4
8	Aladin	7	7			Dorothy	4
9	Albert	3	7
Sheet1

F1 is the desired Top N.

In F2 control+shift+enter, not just enter:

=LARGE(SUMIFS($B$2:$B$9,$A$2:$A$9,IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9)),MIN(F1,SUM(IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),1))))

In F3 control+shift+enter, not just enter, and copy down:

=IFERROR(SUM(IF(SUMIFS($B$2:$B$9,$A$2:$A$9,IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9))>=F2,1)),0)

In F5 control+shift+enter, not just enter, and copy down:

=IF($G5="","",INDEX($A$2:$A$9,SMALL(IF(SUMIFS($B$2:$B$9,$A$2:$A$9,IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9))=$G5,ROW($A$2:$A$9)-ROW($A$2)+1),COUNTIFS($G$5:G5,G5))))

In G5 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$5:G5)>$F$3,"",LARGE(SUMIFS($B$2:$B$9,$A$2:$A$9,IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),$A$2:$A$9)),ROWS($G$5:G5)))

This set up does not use the range in column C.

 

[Tazukie]

This worked! Thank you so much, there's no way I could have possibly come up with something like this on my own. Very much appreciated!

 

[Aladin Akyurek]

This worked! Thank you so much, there's no way I could have possibly come up with something like this on my own. Very much appreciated!

You are very welcome.