Forum Discussion
Formula for top 10
Help! I need a formula to determine the top 10 most often found codes (diagnosis codes) similar to this list. I have them saved as text. Thanks!! E119 E785 I10 K219 E559 E660...
As variant for such result
formula could be
=LET(
topN, 10,
r, ROWS(range),
c, COLUMNS(range),
k, SEQUENCE(r*c),
list, INDEX(range, MOD(k-1,r)+1, INT((k-1)/r)+1),
noBlanks, FILTER(list, NOT(ISBLANK(list))),
j, SEQUENCE(ROWS(noBlanks)),
pos, XMATCH(noBlanks,noBlanks),
freq, SORT(IF({1,0}, FREQUENCY(pos, j), j),,-1),
n, SEQUENCE(topN),
IF({1,0}, INDEX(noBlanks,INDEX(freq,n,2)), INDEX(freq, n, 1))
)