Jul 21 2021 12:15 PM - edited Jul 21 2021 12:20 PM
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 |
Jul 23 2021 03:39 AM
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))
)