Need top 10 from column B for every different value in column A

D

DaRTH KiRo

New Member
Joined
Jan 24, 2018
Messages
39
Office Version
  1. 2016
Platform
  1. Windows
I have three columns in my worksheet, column A contains a list of hubs, column B contains a list of suppliers, and column C contains a sum of their business. The hub in the cell of column A repeats for every supplier they have before moving onto a new hub. Below is an example of what the data looks like; however, there are thousands of rows. Is there there a way to identify the top 10 suppliers using columns B & C for each hub (column A)? In case it is relevant, multiple hubs may have the same supplier but the sum of business would be different because it is just based on that hub and supplier.


Hub 1Supplier Name 1
561651​
Hub 1Supplier Name 2
32165​
Hub 1Supplier Name 3
1596​
Hub 1Supplier Name 4
15984​
Hub 1Supplier Name 5
1651​
Hub 2Supplier Name 4
68573​
Hub 2Supplier Name 5
654981​
Hub 2Supplier Name 6
16864​
Hub 2Supplier Name 7
654​
Hub 2Supplier Name 8
156654​
Hub 3Supplier Name 10
651​
Hub 3Supplier Name 11
984​
Hub 3Supplier Name 12
1891​
Hub 3Supplier Name 13
15687​
Hub 3Supplier Name 14
35489​
Hub 3Supplier Name 15
1568​
Hub 3Supplier Name 16
68465​
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
You could just turn it into a pivot table with hub and supplier in the rows and total in the values. Then set the supplier value filters to show top 10.

unique.xlsx
ABC
3HubSupplierSum of Total
4Hub 1609800
5Supplier Name 1561651
6Supplier Name 232165
7Supplier Name 415984
8Hub 2880208
9Supplier Name 5654981
10Supplier Name 8156654
11Supplier Name 468573
12Hub 3119641
13Supplier Name 1668465
14Supplier Name 1435489
15Supplier Name 1315687
16Grand Total1609649
Sheet4


Or with a formula. Both of these I just did top 3.

unique.xlsx
ABCDEFGH
1HubSupplierTotalHub 1Hub 2Hub 3
2Hub 1Supplier Name 15616511Supplier Name 1Supplier Name 5Supplier Name 16
3Hub 1Supplier Name 2321652Supplier Name 2Supplier Name 8Supplier Name 14
4Hub 1Supplier Name 315963Supplier Name 4Supplier Name 4Supplier Name 13
5Hub 1Supplier Name 415984
6Hub 1Supplier Name 51651
7Hub 2Supplier Name 468573
8Hub 2Supplier Name 5654981
9Hub 2Supplier Name 616864
10Hub 2Supplier Name 7654
11Hub 2Supplier Name 8156654
12Hub 3Supplier Name 10651
13Hub 3Supplier Name 11984
14Hub 3Supplier Name 121891
15Hub 3Supplier Name 1315687
16Hub 3Supplier Name 1435489
17Hub 3Supplier Name 151568
18Hub 3Supplier Name 1668465
Sheet3
Cell Formulas
RangeFormula
F2:H4F2=INDEX($B$2:$B$18,MATCH(AGGREGATE(14,6,1/($A$2:$A$18=F$1)*$C$2:$C$18,$E2),$C$2:$C$18,0))
 
Upvote 0
Solution
Try this:
Mail Generator2.xlsm
ABCDEFGHIJ
1Hub 1Supplier Name 156151RankHub 1Hub 2Hub 3
2Hub 1Supplier Name 23216515615165498168465
3Hub 1Supplier Name 3159623216515665435489
4Hub 1Supplier Name 4159843159846857315687
5Hub 1Supplier Name 5165141651168641891
6Hub 2Supplier Name 468573515966541568
7Hub 2Supplier Name 56549816  984
8Hub 2Supplier Name 6168647  651
9Hub 2Supplier Name 76548   
10Hub 2Supplier Name 81566549   
11Hub 3Supplier Name 1065110   
12Hub 3Supplier Name 11984
13Hub 3Supplier Name 121891
14Hub 3Supplier Name 1315687
15Hub 3Supplier Name 1435489
16Hub 3Supplier Name 151568
17Hub 3Supplier Name 1668465
Sheet2
Cell Formulas
RangeFormula
G2:I11G2=IFERROR(LARGE(IF($A$1:$A$20=G$1,$C$1:$C$20),ROW()-1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your verison, another possible option
+Fluff v2.xlsm
ABCDEFGH
1HubSupplierTotalHub 1Hub 2Hub 3
2Hub 1Supplier Name 1561651Supplier Name 1Supplier Name 5Supplier Name 16
3Hub 1Supplier Name 232165Supplier Name 2Supplier Name 8Supplier Name 14
4Hub 1Supplier Name 31596Supplier Name 4Supplier Name 4Supplier Name 13
5Hub 1Supplier Name 415984Supplier Name 5Supplier Name 6Supplier Name 12
6Hub 1Supplier Name 51651Supplier Name 3Supplier Name 7Supplier Name 15
7Hub 2Supplier Name 468573Supplier Name 11
8Hub 2Supplier Name 5654981Supplier Name 10
9Hub 2Supplier Name 616864
10Hub 2Supplier Name 7654
11Hub 2Supplier Name 8156654
12Hub 3Supplier Name 10651
13Hub 3Supplier Name 11984
14Hub 3Supplier Name 121891
15Hub 3Supplier Name 1315687
16Hub 3Supplier Name 1435489
17Hub 3Supplier Name 151568
18Hub 3Supplier Name 1668465
19
20
Master
Cell Formulas
RangeFormula
H2:H8,F2:G6F2=LET(Fltr,FILTER($B$2:$C$20,$A$2:$A$20=F$1),INDEX(SORT(Fltr,2,-1),SEQUENCE(MIN(ROWS(Fltr),10)),1))
Dynamic array formulas.
 
Upvote 0
lrobbo314 said:
You could just turn it into a pivot table with hub and supplier in the rows and total in the values. Then set the supplier value filters to show top 10.

unique.xlsx
ABC
3HubSupplierSum of Total
4Hub 1609800
5Supplier Name 1561651
6Supplier Name 232165
7Supplier Name 415984
8Hub 2880208
9Supplier Name 5654981
10Supplier Name 8156654
11Supplier Name 468573
12Hub 3119641
13Supplier Name 1668465
14Supplier Name 1435489
15Supplier Name 1315687
16Grand Total1609649
Sheet4


Or with a formula. Both of these I just did top 3.

unique.xlsx
ABCDEFGH
1HubSupplierTotalHub 1Hub 2Hub 3
2Hub 1Supplier Name 15616511Supplier Name 1Supplier Name 5Supplier Name 16
3Hub 1Supplier Name 2321652Supplier Name 2Supplier Name 8Supplier Name 14
4Hub 1Supplier Name 315963Supplier Name 4Supplier Name 4Supplier Name 13
5Hub 1Supplier Name 415984
6Hub 1Supplier Name 51651
7Hub 2Supplier Name 468573
8Hub 2Supplier Name 5654981
9Hub 2Supplier Name 616864
10Hub 2Supplier Name 7654
11Hub 2Supplier Name 8156654
12Hub 3Supplier Name 10651
13Hub 3Supplier Name 11984
14Hub 3Supplier Name 121891
15Hub 3Supplier Name 1315687
16Hub 3Supplier Name 1435489
17Hub 3Supplier Name 151568
18Hub 3Supplier Name 1668465
Sheet3
Cell Formulas
RangeFormula
F2:H4F2=INDEX($B$2:$B$18,MATCH(AGGREGATE(14,6,1/($A$2:$A$18=F$1)*$C$2:$C$18,$E2),$C$2:$C$18,0))
Click to expand...
I hate how easy that was and how it rattled my brain at night. Thank you very much the pivot table value filtering worked. I was definitely making it a bigger problem than it was.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Extract Data from Single Cell. Column is Constant. Number of Rows Involved Changes. Number of Rows Within a Single Cell Can be 1 Up to 9
Replies
6
Views
105
helpmeplz_
H
S
  • Question
How to insert X amount of blank rows at the top and then pull data from column
Replies
0
Views
277
suet
S
P
  • Question
Return a value from a table that is in the top n percent of a person's trial scores
2
Replies
13
Views
409
Fluff
Fluff
P
  • Solved
Difference from Top Rated Value in a Range
2
Replies
11
Views
239
dreid1011
dreid1011
F
  • Solved
Copying and pasting onto mastersheet but files have different formats (need if statement help)
2
Replies
12
Views
905
NateSC
NateSC

Forum statistics

Threads
1,214,925
Messages
6,122,298
Members
449,077
Latest member
Rkmenon

Share this page

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