Top "N" List with Selection Criteria

[NeedInformation]

Would appreciate help with the following.

I have a large, unsorted table in one worksheet tab and I need to populate a sorted TOP N list on another worksheet tab where TOP is the largest amount of sales (largest being #1 ) but it has to be only with those of a certain color. I need to return the KEY into the TOP N table. KEY is guaranteed to be unique in the source table. From the KEY I can retrieve any additional needed data to populate the target tab. There can be duplicate sales amounts. In fact, anything can be duplicated save for the KEY value.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]KEY[/TD]
[TD]Sales[/TD]
[TD]Color[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]asdf[/TD]
[TD]100[/TD]
[TD]Red[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]qwer[/TD]
[TD]200[/TD]
[TD]blue[/TD]
[TD]Nick[/TD]
[/TR]
[TR]
[TD]zxcv[/TD]
[TD]100[/TD]
[TD]Red[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]5463[/TD]
[TD]2000[/TD]
[TD]Green[/TD]
[TD]Sally[/TD]
[/TR]
[TR]
[TD]fghj[/TD]
[TD]150[/TD]
[TD]Red[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]yuio[/TD]
[TD]300[/TD]
[TD]Yellow[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]cvbn[/TD]
[TD]275[/TD]
[TD]Blue[/TD]
[TD]Ralph[/TD]
[/TR]
[TR]
[TD]ghjk[/TD]
[TD]500[/TD]
[TD]Red[/TD]
[TD]Sara[/TD]
[/TR]
[TR]
[TD]ghjz[/TD]
[TD]750[/TD]
[TD]Blue[/TD]
[TD]Rita[/TD]
[/TR]
</tbody>[/TABLE]

So, a top 3 list based on Red would return TOP=3, COLOR=RED

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Key[/TD]
[/TR]
[TR]
[TD]ghjk[/TD]
[/TR]
[TR]
[TD]fghj[/TD]
[/TR]
[TR]
[TD]asdf[/TD]
[/TR]
</tbody>[/TABLE]


In the case of duplicate, a top-down selection would be fine as illustrated by the key asdf as opposed to zxcv above.

If SALES has to be part of the solution to help find the key that's okay.

Formulas are the preferred solution vs macro(s).

TIA!

 

[FDibbins]

See if this will help?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]KEY[/td][td]Sales[/td][td]Color[/td][td]Name[/td][/tr]

[tr][td]

2​

[/td][td]asdf[/td][td]

100​

[/td][td]Red[/td][td]Sam[/td][/tr]

[tr][td]

3​

[/td][td]qwer[/td][td]

200​

[/td][td]blue[/td][td]Nick[/td][/tr]

[tr][td]

4​

[/td][td]zxcv[/td][td]

100​

[/td][td]Red[/td][td]Mary[/td][/tr]

[tr][td]

5​

[/td][td]

5463​

[/td][td]

2000​

[/td][td]Green[/td][td]Sally[/td][/tr]

[tr][td]

6​

[/td][td]fghj[/td][td]

150​

[/td][td]Red[/td][td]Mark[/td][/tr]

[tr][td]

7​

[/td][td]yuio[/td][td]

300​

[/td][td]Yellow[/td][td]Tom[/td][/tr]

[tr][td]

8​

[/td][td]cvbn[/td][td]

275​

[/td][td]Blue[/td][td]Ralph[/td][/tr]

[tr][td]

9​

[/td][td]ghjk[/td][td]

500​

[/td][td]Red[/td][td]Sara[/td][/tr]

[tr][td]

10​

[/td][td]ghjz[/td][td]

750​

[/td][td]Blue[/td][td]Rita[/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]Top[/td][td]

3​

[/td][td]color[/td][td]Red[/td][/tr]

[tr][td]

13​

[/td][td]ghjk[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]fghj[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]asdf[/td][td][/td][td][/td][td][/td][/tr]
[/table]
B12=top N
D12 = color
A13=IF(ROW(A1)>$B$12,"",INDEX($A$2:$A$10,MATCH(LARGE(IF($C$2:$C$10=$D$12,$B$2:$B$10),ROW(A1)),$B$2:$B$10,0)))
ARRAY entered using CTRL SHIFT ENTER, not just enter
Then copy down as needed

 

[AhoyNC]

@FDibbins - I think there may be a couple potential problems with your formula. 1) In the above example for RED if the Sales in Row 3 for Blue had been 500 your formula will pick up the Key for Blue and not Red. 2) If you were to look for the top 4 your formula will only pick up one of the Key's for Sales at 100.

Try:
This is also an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Top	4	color	Red
2	ghjk
3	fghj
4	zxcv
5	asdf
Sheet2

Excel Workbook
	A	B	C	D
1	KEY	Sales	Color	Name
2	asdf	100	Red	Sam
3	qwer	200	blue	Nick
4	zxcv	100	Red	Mary
5	5463	2000	Green	Sally
6	fghj	150	Red	Mark
7	yuio	300	Yellow	Tom
8	cvbn	275	Blue	Ralph
9	ghjk	500	Red	Sara
10	ghjz	750	Blue	Rita
Sheet1

 

[NeedInformation]

At first glance this seemed perfect but then, as a test, I changed sheet1!b9 to 1. Sheet2 didn't change. I have automatic calculation turned on. Is this a nuance of Excel or am I missing something? I need to ensure Sheet2 is automatically updated when changes in Sheet1 occur and sheet2 remains sorted based on Sales in descending order.

@FDibbins - I think there may be a couple potential problems with your formula. 1) In the above example for RED if the Sales in Row 3 for Blue had been 500 your formula will pick up the Key for Blue and not Red. 2) If you were to look for the top 4 your formula will only pick up one of the Key's for Sales at 100.

Try:
This is also an array formula that must be entered with CTRL-SHIFT-ENTER.

Sheet2

	A	B	C	D

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Top[/TD]
[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]color[/TD]
[TD="align: center"]Red[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: #ffff00"]ghjk[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: #ffff00"]fghj[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: #ffff00"]zxcv[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="bgcolor: #ffff00"]asdf[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 {=IF(ROWS($A$2:A2)>$B$1,"",INDEX(Sheet1!$A$2:$A$10,LARGE(IF(Sheet1!$C$2:$C$10=$D$1,ROW(Sheet1!$C$2:$C$10)-ROW(Sheet1!$C$2)+1),ROWS($A$2:A2))))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Sheet1

	A	B	C	D

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:66px;"><col style="width:68px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]KEY[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Sales[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Color[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Name[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]asdf[/TD]
[TD="align: right"]100[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Sam[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]qwer[/TD]
[TD="align: right"]200[/TD]
[TD="align: center"]blue[/TD]
[TD="align: center"]Nick[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]zxcv[/TD]
[TD="align: right"]100[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Mary[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]5463[/TD]
[TD="align: right"]2000[/TD]
[TD="align: center"]Green[/TD]
[TD="align: center"]Sally[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]fghj[/TD]
[TD="align: right"]150[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Mark[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]yuio[/TD]
[TD="align: right"]300[/TD]
[TD="align: center"]Yellow[/TD]
[TD="align: center"]Tom[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]cvbn[/TD]
[TD="align: right"]275[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Ralph[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]ghjk[/TD]
[TD="align: right"]500[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Sara[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]ghjz[/TD]
[TD="align: right"]750[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Rita[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

You're right I also gave you a bad formula.

Try this (I added a Sales column)
These are array formulas and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Top	3	color	Red
2	Sales	Key
3	150	fghj
4	100	asdf
5	100	zxcv
6
Sheet2

Excel Workbook
	A	B	C	D
1	KEY	Sales	Color	Name
2	asdf	100	Red	Sam
3	qwer	200	blue	Nick
4	zxcv	100	Red	Mary
5	5463	2000	Green	Sally
6	fghj	150	Red	Mark
7	yuio	300	Yellow	Tom
8	cvbn	275	Blue	Ralph
9	ghjk	1	Red	Sara
10	ghjz	750	Blue	Rita
Sheet1