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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
I do not know what version of excel are you using, but in 365 you can use:

Excel Formula: 
=TAKE(SORT(UNIQUE(FILTER('Data For Attribution'!N:N,('Data For Attribution'!R:R=Dashboard!B2)))),10)
 
Upvote 0
I suggest that you update your Account details (or 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’)

Maybe
Excel Formula: 
=TAKE(SORT(UNIQUE(FILTER('Data For Attribution'!N:N,'Data For Attribution'!R:R=B2))),10)
 
Upvote 0
KOKOSEK said:
I do not know what version of excel are you using, but in 365 you can use:

Excel Formula: 
=TAKE(SORT(UNIQUE(FILTER('Data For Attribution'!N:N,('Data For Attribution'!R:R=Dashboard!B2)))),10)
Click to expand...
I am on 365 - When I pasted this in, I get a #NAME? Error? - It appears that I don't have the Take Function in the version of Excel that we are using
 
Upvote 0
Ok, how about
Excel Formula: 
=LET(s,SORT(UNIQUE(FILTER('Data For Attribution'!N:N,'Data For Attribution'!R:R=B2))),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
 
Upvote 0
Fluff said:
Ok, how about
Excel Formula: 
=LET(s,SORT(UNIQUE(FILTER('Data For Attribution'!N:N,'Data For Attribution'!R:R=B2))),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
Click to expand...
Thank you, so that gives me the first 10 of the list but not a Top 10 of how many times they appear in the source (if that makes sense?)
 
Upvote 0
In that case can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Sample Data.xlsx
ABCDEFGHIJKLMN
1NamesYear
2Paul Taylor2022
3Patrick Black2022
4Terry Holloway2022
5Kimberly Garza2022
6Paul Taylor2022
7Terry Holloway2022
8Terry Holloway2022
9Terry Holloway2022
10Paul Taylor2022
11Nicole Weeks2022
12Dennis Richardson MD2022
13Heather Nichols2022
14Patrick Black2022
15Heather Nichols2022
16Heather Nichols2022
17Paul Taylor2022
18Amber White2022
19Paul Taylor2022
20Sandra Burton2022
21Steven Rodriguez2022
22Robert Wright2022
23Patrick Black2022
24Nicole Weeks2022
25Nicole Weeks2022
26Amber Pruitt2022
27Nicole Weeks2022
28Patrick Black2022
29Robert Wright2022
30Andrew Norris2022
31Emily Pittman2022
32Robert Wright2022
33Nicole Weeks2022
34Amanda Jones2022
35Nicole Weeks2022
36Nicole Weeks2022
37Nicole Weeks2022
38Nicole Weeks2022
39Robert Wright2022
40Kimberly Garza2022
41Kimberly Garza2022
42Kimberly Garza2022
43Kimberly Garza2022
44Paul Taylor2022
45Phillip Cohen2022
46Tamara Turner2022
47Terry Holloway2022
48Jose Stevenson2022
49Hannah Pugh2022
50Patrick Burke2022
51Tamara Turner2022
52Nancy Terry2022
53Terry Hodge2022
54Joshua Hunter2022
55Chelsea Lopez2022
56Terry Holloway2022
57Terry Holloway2022
58Luke Lynch2022
59Christopher Garner2022
60Kimberly Garza2022
61Chelsea Lopez2022
62Chelsea Lopez2022
63Tim Davis2022
64Paul Taylor2022
65Angela King2022
66Michael Schultz2022
67Jennifer Hernandez2022
68Allison Gill2022
69Kenneth Miller2022
70Patrick Watson2022
71Krystal Ford MD2022
72Kimberly Garza2022
73Kimberly Garza2022
74Levi Dunlap2022
75Anthony Reyes2022
76Kimberly Garza2022
77Levi Dunlap2022
78Shelby Green2022
79Tracy Rodriguez2022
80Paul Jackson2022
81Levi Dunlap2022
82Kathleen Morris2022
83Maria Smith2022
84Linda Patrick2022
85Calvin Martin2022
86Daniel Robinson2022
87William Perez2022
88Levi Dunlap2022
89Levi Dunlap2022
90Paul Taylor2022
91Curtis Barron2022
92Kelly Warner2022
93Andrew Manning2022
94Andrew Manning2022
95Kenneth Stone2022
96Andrew Manning2022
97Rebecca Smith2022
98Richard Camacho2022
99Shawn Kramer2022
100Douglas Thompson2022
101Paul Taylor2022
Data For Attribution

Sample Data.xlsx
ABCDEFGH
1
22022Row LabelsCount of Names
3Nicole Weeks9Allison Gill
4Paul Taylor9Amanda Jones
5Kimberly Garza9Amber Pruitt
6Terry Holloway7Amber White
7Levi Dunlap5Andrew Manning
8Robert Wright4Andrew Norris
9Patrick Black4Angela King
10Andrew Manning3Anthony Reyes
11Heather Nichols3Calvin Martin
12Chelsea Lopez3Chelsea Lopez
13Grand Total56
14
15
16
17
Dashboard
Cell Formulas
RangeFormula
G3:G12G3=LET(s,SORT(UNIQUE(FILTER('Data For Attribution'!A:A,'Data For Attribution'!N:N=B2))),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
'Data For Attribution'!ExternalData_1='Data For Attribution'!$A$1:$A$101G3
 
Upvote 0
Ok, thanks for that, how about
Excel Formula: 
=LET(u,UNIQUE(FILTER('Data For Attribution'!A:A,'Data For Attribution'!N:N=b2)),s,SORTBY(u,COUNTIFS('Data For Attribution'!A:A,u,'Data For Attribution'!N:N,b2),-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
 
Upvote 0
Fluff said:
Ok, thanks for that, how about
Excel Formula: 
=LET(u,UNIQUE(FILTER('Data For Attribution'!A:A,'Data For Attribution'!N:N=b2)),s,SORTBY(u,COUNTIFS('Data For Attribution'!A:A,u,'Data For Attribution'!N:N,b2),-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))
Click to expand...

That works Perfectly!

If I wanted to add months as a filter, Say Month is in B3 and list of Months on Sheet Data Attribution Column M - Is that easy to add additional criteria?

Thank You
 
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
452
Fluff
Fluff
N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
319
Nathan Asius
N
P
  • Question
Help using FILTER
2
Replies
15
Views
392
Paulypaul
P
C
  • Question
Using a common value, display unique values that relate/correspond to it
Replies
8
Views
215
chive90
C
C
  • Question
Limit slicer options
Replies
0
Views
334
chriswhincup
C

Forum statistics

Threads
1,214,641
Messages
6,120,691
Members
448,978
Latest member
rrauni

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