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.
This will also discount any null strings ("") present within the range:
=LET(
ζ,B1:D1000,
TAKE(SORT(IF({0,1},FREQUENCY(ζ,0+(0&ζ)),TOCOL(ζ)),2,-1),10)
)- 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.
- Nice. You make better use of FREQUENCY than I did. My sorting and removing duplicates for the bins was unnecessary. Since my array was generated using RANDARRAY blanks were not an issue.
The OP did state "for any given row it might have a different entry in each of the 3 columns or it may simply have an entry in 1 column with the other 2 columns being blank". I just assumed that some or all of those "blanks" could be null strings.
Another possibility is to use the TOCOL function to remove the blanks
= LET( ζ, TOCOL(data,1), TAKE(SORT(HSTACK(ζ,FREQUENCY(ζ,ζ)),2,-1),10) )
