# Formula for top 10

Occasional Visitor

# 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 E6601 G609 G8929 G894 I10 E039 E1140 E1165 E118 E119 E785 E559 E782 F17200 I10 J449 E039 E785 I10 I6529 J309 M170 E785 F17200 G894 I10 I4891 I5030 E782 F0390 F419 G609 G894 I10 E1165 E119 E669 E785 G629 I10 E119 E559 I10 R32 E119 E559 I10 J309 K219 E1122 E119 E162 E669 E785 G894 E039 E559 E782 G894 I2510 I4891 J3089 J42 D631 E119 E559 E782 G5600 G894 D631 D638 E6601 E785 I10 K5730 E119 E6601 N925 Z6843 J849 M069 E039 E559 E890 F411 F419 L710 E039 E119 E559 E782 F419 G2581 E1140 E119 E291 E559 E782 F329 E559 E785 F419 I10 I509 L209 E119 E669 E785 I10 I119 I132 E785 I10 I2510 N8110 N952

# Re: Formula for top 10

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))
)``````