Using UNIQUE but display only Top 10

[karmaimages]

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

 

[Fluff]

How about

=LET(u,UNIQUE(FILTER('Data For Attribution'!A:A,('Data For Attribution'!N:N=B2)*('Data For Attribution'!M:M=B3))),s,SORTBY(u,COUNTIFS('Data For Attribution'!A:A,u,'Data For Attribution'!N:N,B2,'Data For Attribution'!M:M,B3),-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))

 

[karmaimages]

How about

=LET(u,UNIQUE(FILTER('Data For Attribution'!A:A,('Data For Attribution'!N:N=B2)*('Data For Attribution'!M:M=B3))),s,SORTBY(u,COUNTIFS('Data For Attribution'!A:A,u,'Data For Attribution'!N:N,B2,'Data For Attribution'!M:M,B3),-1),INDEX(s,SEQUENCE(MIN(ROWS(s),10))))

Thank you very much

I have one issue, where I've added another filter, 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?

 

[JvdV]

=LET(x,UNIQUE(A:A),TAKE(SORT(DROP(HSTACK(x,BYROW(x,LAMBDA(y,COUNTIFS(A:A,y,N:N,B2)))),1),2,-1),10))

Or, if you don't have access to TAKE() and DROP() yet:

=LET(x,FILTER(A:A,N:N=B2),y,UNIQUE(x),INDEX(SORT(CHOOSE({1,2},y,BYROW(y,LAMBDA(z,SUM(--(x=z))))),2,-1),ROW(1:10),{1,2}))

 

[karmaimages]

=LET(x,UNIQUE(A:A),TAKE(SORT(DROP(HSTACK(x,BYROW(x,LAMBDA(y,COUNTIFS(A:A,y,N:N,B2)))),1),2,-1),10))

Or, if you don't have access to TAKE() and DROP() yet:

=LET(x,FILTER(A:A,N:N=B2),y,UNIQUE(x),INDEX(SORT(CHOOSE({1,2},y,BYROW(y,LAMBDA(z,SUM(--(x=z))))),2,-1),ROW(1:10),{1,2}))

I don't have the Take or Drop Options,

The second option doesn't produce the same results as @Fluff formula produced which was correct.

=LET(x,FILTER('Data For Attribution'!N:N,'Data For Attribution'!S:S=Dashboard!B2,'Data For Attribution'!U:U='Breach Breakdowns'!J3),y,UNIQUE(x),INDEX(SORT(CHOOSE({1,2},y,BYROW(y,LAMBDA(z,SUM(--(x=z))))),2,-1),ROW(1:10),{1,2}))

I amended your formula to the sheets within my workbook but the results weren't correct. The formula was entered into cell BW19

 

[JvdV]

I don't have the Take or Drop Options,

The second option doesn't produce the same results as @Fluff formula produced which was correct.

=LET(x,FILTER('Data For Attribution'!N:N,'Data For Attribution'!S:S=Dashboard!B2,'Data For Attribution'!U:U='Breach Breakdowns'!J3),y,UNIQUE(x),INDEX(SORT(CHOOSE({1,2},y,BYROW(y,LAMBDA(z,SUM(--(x=z))))),2,-1),ROW(1:10),{1,2}))

I amended your formula to the sheets within my workbook but the results weren't correct. The formula was entered into cell BW19

It's because you have butchered the FILTER() function. Note that the 2nd parameter should be written in a Boolean like structure of you have multiple criteria.

In any case, you have a working answer which is important. This would have been able to output two columns. One with unique entries and a 2nd one with a count.

 

[karmaimages]

It's because you have butchered the FILTER() function. Note that the 2nd parameter should be written in a Boolean like structure of you have multiple criteria.

In any case, you have a working answer which is important. This would have been able to output two columns. One with unique entries and a 2nd one with a count.

Unfortunately, that doesn't actually mean anything to me, are you able to assist correcting this?

 

[JvdV]

A boolean-like structure is basically multiplication between different arrays of FALSE/TRUE values. Something like (array1=x)*(arry2=y). In your case:

=LET(x,FILTER(A:A,(N:N=B2)*(M:M=B3)),y,UNIQUE(x),INDEX(SORT(CHOOSE({1,2},y,BYROW(y,LAMBDA(z,SUM(--(x=z))))),2,-1),ROW(1:10),{1,2}))

 

[karmaimages]

A boolean-like structure is basically multiplication between different arrays of FALSE/TRUE values. Something like (array1=x)*(arry2=y). In your case:

=LET(x,FILTER(A:A,(N:N=B2)*(M:M=B3)),y,UNIQUE(x),INDEX(SORT(CHOOSE({1,2},y,BYROW(y,LAMBDA(z,SUM(--(x=z))))),2,-1),ROW(1:10),{1,2}))

Unfortunately it just doesn't work for me, I'm working across multiple sheets, If I change the filters to the correct sheers and columns, it doesn't produce the correct results.

The first column produces a list of names (not the top 10) and the second column produces a #NAME? error

Below is what I changed the formulas to:

=LET(x,FILTER('Data For Attribution'!N:N,('Data For Attribution'!S:S=Dashboard!B2)*('Data For Attribution'!U:U='Breach Breakdowns'!J3)),y,UNIQUE(x),INDEX(SORT(CHOOSE({1,2},y,BYROW(y,LAMBDA(z,SUM(--(x=z))))),2,-1),ROW(1:10),{1,2}))

 

[JvdV]

Unfortunately it just doesn't work for me, I'm working across multiple sheets, If I change the filters to the correct sheers and columns, it doesn't produce the correct results.

The first column produces a list of names (not the top 10) and the second column produces a #NAME? error

Below is what I changed the formulas to:

=LET(x,FILTER('Data For Attribution'!N:N,('Data For Attribution'!S:S=Dashboard!B2)*('Data For Attribution'!U:U='Breach Breakdowns'!J3)),y,UNIQUE(x),INDEX(SORT(CHOOSE({1,2},y,BYROW(y,LAMBDA(z,SUM(--(x=z))))),2,-1),ROW(1:10),{1,2}))

Then you also don't have access to BYROW just yet. Either update your version of Excel to pull in these functions or the most obvious: go with the working answer that you got from @Fluff

 

[Fluff]

if you don't have access to TAKE() and DROP() yet:

Which the OP does not have as I originally used the Take function & the Op said he/she had a #Name error.