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 is similar to the others
= LET(
occurrences, TOCOL(COUNTIFS(array#,array#)),
valuesList, TOCOL(array#),
sorted, SORT(HSTACK(valuesList, occurrences),{2,1},-1),
TAKE(UNIQUE(sorted),10)
)The most significant differences are that I applied COUNTIFS to the 2D array whilst is was still a Range and only then reduced the results to a column. I used LET to avoid excessive function nesting but did not place the formula within a LAMBDA despite the fact that I am moving in that direction.
I also noticed the use of FREQUENCY as an alternative to COUNTIFS.
= LET(
list, TOCOL(array#),
distinct, SORT(UNIQUE(list)),
occurrences, DROP(FREQUENCY(list, distinct),-1),
sorted, SORT(HSTACK(distinct, occurrences),{2,1},-1),
TAKE(sorted, 10)
)That also seems to work well.
Thank you very much for posting a solution to my problem. In the end I used the solution given by 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.