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.

KEY	Sales	Color	Name
asdf	100	Red	Sam
qwer	200	blue	Nick
zxcv	100	Red	Mary
5463	2000	Green	Sally
fghj	150	Red	Mark
yuio	300	Yellow	Tom
cvbn	275	Blue	Ralph
ghjk	500	Red	Sara
ghjz	750	Blue	Rita

<tbody>
</tbody>

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

Key

ghjk

fghj

asdf

<tbody>
</tbody>

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?

	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
11​
12​	Top	3​	color	Red
13​	ghjk
14​	fghj
15​	asdf

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
1	Top	4	color	Red
2	ghjk
3	fghj
4	zxcv
5	asdf

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</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
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

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:66px;"><col style="width:68px;"></colgroup><tbody>
</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