Top "N" List with Selection Criteria

NeedInformation

New Member
Joined
Feb 23, 2014
Messages
14
Office Version
  1. 2021
Platform
  1. Windows
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
@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
ABCD
1Top4colorRed
2ghjk
3fghj
4zxcv
5asdf
Sheet2
Excel Workbook
ABCD
1KEYSalesColorName
2asdf100RedSam
3qwer200blueNick
4zxcv100RedMary
554632000GreenSally
6fghj150RedMark
7yuio300YellowTom
8cvbn275BlueRalph
9ghjk500RedSara
10ghjz750BlueRita
Sheet1
 
Upvote 0
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

ABCD

<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
CellFormula
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

ABCD

<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
 
Upvote 0
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
ABCD
1Top3colorRed
2SalesKey
3150fghj
4100asdf
5100zxcv
6
Sheet2
Excel Workbook
ABCD
1KEYSalesColorName
2asdf100RedSam
3qwer200blueNick
4zxcv100RedMary
554632000GreenSally
6fghj150RedMark
7yuio300YellowTom
8cvbn275BlueRalph
9ghjk1RedSara
10ghjz750BlueRita
Sheet1
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top