Forum Discussion
Solved
Top n most common entries in an array of numbers and how often they occur
I have a 3 column x 1000 row array that contains a variety of numbers in one or more of the columns i.e. for any given row it might have a different entry in each of the 3 columns or it may simply ha...
=TAKE(SORT(HSTACK(UNIQUE(TOCOL(B1:D1000)),BYROW(UNIQUE(TOCOL(B1:D1000)),LAMBDA(row,COUNTIF(B1:D1000,row)))),2,-1),10)You can try this formula which works in my sheet.
My solution:
matrix is the 1000x3 array.
'Vector
=TOCOL(matrix)
'Total' Lambda - run each of the numbers through a filter to obtain the counts. Stack, sort, and take top 10.
=LAMBDA(a,v,LET(
filtered, COUNT(FILTER(vector, vector = v)),
stack, VSTACK(a, HSTACK(v, filtered)),
TAKE(SORT(stack, 2, -1), 10)
))
Sheet level formula:
=REDUCE({"Number","Count"},UNIQUE(vector),Total)
Thank you very much for posting a solution to my problem. In the end I used the solution given by Quadruple_Pawn because they showed a table and a result. No other reason than that, It worked perfectly. I could not have done this on my own so thank you so much for answering my question. It's fantastic to see the Excel community in action. Thank you once again.