Using UNIQUE but display only Top 10

K

karmaimages

Board Regular
Joined
Oct 1, 2009
Messages
112
Office Version
  1. 365
Platform
  1. Windows
Hi

I have a list of names in Col N and which I'm filtering on a dashboard to the current year things have been listed to their names using the following formula:

=SORT(UNIQUE(FILTER('Data For Attribution'!N:N,('Data For Attribution'!R:R=Dashboard!B2))))

B2 Contains a drop down of dates which updates all sheets when the user changes the year.

However, I only need to display the Top 10 in the unique list, is there a better way without Pivots to do this?

Many Thanks
 
Sort by date Sort by votes
karmaimages said:
if there are any matches to perform a Top 10 say the filter only has results for 3, it's pulling in random data for the remainder of the top 10 - is there a way to just 10 or fewer where > 10 don't exist?
Click to expand...
Can you post some data that shows this.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Fluff said:
Can you post some data that shows this.
Click to expand...

This is some sample data as I can't post the actual data but the result is the same:

Source:

Sample Data.xlsx
ABCDEFGHIJKLMN
1NamesManagerYear
2Paul TaylorKelsi Carrillo2022
3Patrick BlackKelsi Carrillo2022
4Terry HollowayKelsi Carrillo2022
5Kimberly GarzaKelsi Carrillo2022
6Paul TaylorKelsi Carrillo2022
7Terry HollowayKelsi Carrillo2022
8Terry HollowayKelsi Carrillo2022
9Terry HollowayKelsi Carrillo2022
10Paul TaylorKelsi Carrillo2022
11Nicole WeeksKelsi Carrillo2022
12Allison GillNorman Ho2022
13Heather NicholsKelsi Carrillo2022
14Patrick BlackKelsi Carrillo2022
15Heather NicholsKelsi Carrillo2022
16Heather NicholsKelsi Carrillo2022
17Paul TaylorKelsi Carrillo2022
18Amanda Jones2022
19Paul TaylorKelsi Carrillo2022
20Allison GillNorman Ho2022
21Allison GillNorman Ho2022
22Robert Wrightkelsi Carrillo2022
23Patrick BlackKelsi Carrillo2022
24Nicole WeeksKelsi Carrillo2022
25Nicole WeeksKelsi Carrillo2022
26Allison GillNorman Ho2022
27Nicole WeeksKelsi Carrillo2022
28Patrick BlackKelsi Carrillo2022
29Robert Wrightkelsi Carrillo2022
30Allison GillNorman Ho2022
31Anthony Reyes2022
32Robert Wrightkelsi Carrillo2022
33Nicole WeeksKelsi Carrillo2022
34Calvin Martin2022
35Nicole WeeksKelsi Carrillo2022
36Nicole WeeksKelsi Carrillo2022
37Nicole WeeksKelsi Carrillo2022
38Nicole WeeksKelsi Carrillo2022
39Robert Wrightkelsi Carrillo2022
40Kimberly GarzaKelsi Carrillo2022
41Kimberly GarzaKelsi Carrillo2022
42Kimberly GarzaKelsi Carrillo2022
43Kimberly GarzaKelsi Carrillo2022
44Paul TaylorKelsi Carrillo2022
45Christopher GarnerNorman Ho2022
46Curtis Barron2022
47Terry HollowayKelsi Carrillo2022
48Christopher GarnerNorman Ho2022
49Christopher GarnerNorman Ho2022
50Christopher GarnerNorman Ho2022
51Christopher GarnerNorman Ho2022
52Christopher GarnerNorman Ho2022
53Christopher GarnerNorman Ho2022
54Jose Stevenson2022
55Chelsea LopezKelsi Carrillo2022
56Terry HollowayKelsi Carrillo2022
57Terry HollowayKelsi Carrillo2022
58Joshua HunterNorman Ho2022
59Joshua HunterNorman Ho2022
60Kimberly GarzaKelsi Carrillo2022
61Chelsea LopezKelsi Carrillo2022
62Chelsea LopezKelsi Carrillo2022
63Joshua HunterNorman Ho2022
64Paul TaylorKelsi Carrillo2022
65Joshua HunterNorman Ho2022
66Kenneth Stone2022
67Krystal Ford MD2022
68Linda Patrick2022
69Luke Lynch2022
70Maria Smith2022
71Linda Patrick2022
72Kimberly GarzaKelsi Carrillo2022
73Kimberly GarzaKelsi Carrillo2022
74Levi DunlapKelsi Carrillo2022
75Nancy Terry2022
76Kimberly GarzaKelsi Carrillo2022
77Levi DunlapKelsi Carrillo2022
78Patrick Burke2022
79Linda PatrickNorman Ho2022
80Paul Jackson2022
81Levi DunlapKelsi Carrillo2022
82Linda PatrickNorman Ho2022
83Linda PatrickNorman Ho2022
84Linda PatrickNorman Ho2022
85Sandra Burton2022
86Shawn Kramer2022
87Linda PatrickNorman Ho2022
88Levi DunlapKelsi Carrillo2022
89Levi DunlapKelsi Carrillo2022
90Paul TaylorKelsi Carrillo2022
91Linda PatrickNorman Ho2022
92Tamara TurnerNorman Ho2022
93Andrew ManningKelsi Carrillo2022
94Andrew ManningKelsi Carrillo2022
95Tamara TurnerNorman Ho2022
96Andrew ManningKelsi Carrillo2022
97Terry Hodge2022
98Tim Davis2022
99Tracy Rodriguez2022
100William Perez2022
101Paul TaylorKelsi Carrillo2022
Data For Attribution


Result:
Sample Data.xlsx
ABCDEFGHIJ
1
22022Norman HoRow LabelsCount of Names
3Kelsi Carrillo56Christopher Garner7
4Nicole Weeks9Linda Patrick6
5Paul Taylor9Allison Gill5
6Kimberly Garza9Joshua Hunter4
7Terry Holloway7Tamara Turner2
8Levi Dunlap5Paul Taylor0
9Robert Wright4Patrick Black0
10Patrick Black4Terry Holloway0
11Andrew Manning3Kimberly Garza0
12Heather Nichols3Nicole Weeks0
13Chelsea Lopez3
14Norman Ho24
15Christopher Garner7
16Linda Patrick6
17Allison Gill5
18Joshua Hunter4
19Tamara Turner2
20(blank)20
21Linda Patrick2
22Anthony Reyes1
23Paul Jackson1
24Nancy Terry1
25Calvin Martin1
26Shawn Kramer1
27Curtis Barron1
28Amanda Jones1
29Jose Stevenson1
30Patrick Burke1
31Kenneth Stone1
32Sandra Burton1
33Krystal Ford MD1
34Terry Hodge1
35Tim Davis1
36Tracy Rodriguez1
37William Perez1
38Luke Lynch1
39Maria Smith1
40Grand Total100
Dashboard
Cell Formulas
RangeFormula
I3:I12I3=LET(u,UNIQUE(FILTER('Data For Attribution'!A:A,'Data For Attribution'!N:N=Dashboard!B2,'Data For Attribution'!B:B=Dashboard!C2)),s,SORTBY(u,COUNTIFS('Data For Attribution'!B:B,Dashboard!C2,'Data For Attribution'!A:A,u,'Data For Attribution'!N:N,Dashboard!B2),-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
J3:J12J3=COUNTIFS('Data For Attribution'!A:A,Dashboard!$I3,'Data For Attribution'!B:B,Dashboard!$C$2,'Data For Attribution'!N:N,Dashboard!$B$2)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
'Data For Attribution'!ExternalData_1='Data For Attribution'!$A$1:$A$101J3:J12, I3
Cells with Data Validation
CellAllowCriteria
C2List=Sheet1!$A$1:$A$3


Result 2:
Sample Data.xlsx
ABCDEFGHIJ
1
22022Kelsi CarrilloRow LabelsCount of Names
3Kelsi Carrillo56Paul Taylor9
4Nicole Weeks9Kimberly Garza9
5Paul Taylor9Nicole Weeks9
6Kimberly Garza9Terry Holloway7
7Terry Holloway7Levi Dunlap5
8Levi Dunlap5Patrick Black4
9Robert Wright4Robert Wright4
10Patrick Black4Heather Nichols3
11Andrew Manning3Chelsea Lopez3
12Heather Nichols3Andrew Manning3
13Chelsea Lopez3
14Norman Ho24
15Christopher Garner7
16Linda Patrick6
17Allison Gill5
18Joshua Hunter4
19Tamara Turner2
20(blank)20
21Linda Patrick2
22Anthony Reyes1
23Paul Jackson1
24Nancy Terry1
25Calvin Martin1
26Shawn Kramer1
27Curtis Barron1
28Amanda Jones1
29Jose Stevenson1
30Patrick Burke1
31Kenneth Stone1
32Sandra Burton1
33Krystal Ford MD1
34Terry Hodge1
35Tim Davis1
36Tracy Rodriguez1
37William Perez1
38Luke Lynch1
39Maria Smith1
40Grand Total100
Dashboard
Cell Formulas
RangeFormula
I3:I12I3=LET(u,UNIQUE(FILTER('Data For Attribution'!A:A,'Data For Attribution'!N:N=Dashboard!B2,'Data For Attribution'!B:B=Dashboard!C2)),s,SORTBY(u,COUNTIFS('Data For Attribution'!B:B,Dashboard!C2,'Data For Attribution'!A:A,u,'Data For Attribution'!N:N,Dashboard!B2),-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
J3:J12J3=COUNTIFS('Data For Attribution'!A:A,Dashboard!$I3,'Data For Attribution'!B:B,Dashboard!$C$2,'Data For Attribution'!N:N,Dashboard!$B$2)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
'Data For Attribution'!ExternalData_1='Data For Attribution'!$A$1:$A$101J3:J12, I3
Cells with Data Validation
CellAllowCriteria
C2List=Sheet1!$A$1:$A$3


As we can see from the result if i select Norman as the manager, it populates the first 5 rows because there are only 5 users listed under him (Pivot confirms same) then fills the last 5 rows with random names who aren't even listed under Norman as a manager

If I select Kelsi as the manager it lists the full 10 names with no issues.
 
Upvote 0
Thanks fro that, you are missing the brackets around the criteria in the filter function.
It should be
Excel Formula: 
=LET(u,UNIQUE(FILTER('Data For Attribution'!A:A,('Data For Attribution'!N:N=B2)*('Data For Attribution'!B:B=C2))),s,SORTBY(u,COUNTIFS('Data For Attribution'!B:B,C2,'Data For Attribution'!A:A,u,'Data For Attribution'!N:N,B2),-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
 
Upvote 0
Solution
Fluff said:
Thanks fro that, you are missing the brackets around the criteria in the filter function.
It should be
Excel Formula: 
=LET(u,UNIQUE(FILTER('Data For Attribution'!A:A,('Data For Attribution'!N:N=B2)*('Data For Attribution'!B:B=C2))),s,SORTBY(u,COUNTIFS('Data For Attribution'!B:B,C2,'Data For Attribution'!A:A,u,'Data For Attribution'!N:N,B2),-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
Click to expand...
Thank you @Fluff that worked perfectly!
 
Upvote 0
Fluff said:
You're welcome & thanks for the feedback.
Click to expand...
No problem at all.

Slightly off topic, I have another Unique/Sort/Filter formula, however can't get it in descending order, if i add the -1 to the brackets in various places I get different results just not the results I'm looking for:

=SORT(UNIQUE(FILTER('Data For Attribution'!P:P,('Data For Attribution'!U:U='Breach Breakdowns'!J3)*('Data For Attribution'!S:S=Dashboard!B2))))
 
Upvote 0
It would need to be
Excel Formula: 
=SORT(UNIQUE(FILTER('Data For Attribution'!P:P,('Data For Attribution'!U:U='Breach Breakdowns'!J3)*('Data For Attribution'!S:S=Dashboard!B2))),,-1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Return the top 10 occurrences of an item in a table and the count of that item
Replies
3
Views
457
Fluff
Fluff
N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
340
Nathan Asius
N
J
  • Question
Marking Top 10 in Criteria List
Replies
1
Views
49
Jezzzza
J
P
  • Question
Help using FILTER
2
Replies
15
Views
403
Paulypaul
P
C
  • Question
Using a common value, display unique values that relate/correspond to it
Replies
8
Views
233
chive90
C

Forum statistics

Threads
1,214,978
Messages
6,122,547
Members
449,089
Latest member
davidcom

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