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.
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) )Unfortunately that only works with 'genuine' blanks. Any null strings present within the range will cause the second column in the output to contain one or more #N/A returns.
Regards