Formula for top 10

Copper Contributor

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!!  

 

E119E785I10K219  
E559E6601G609G8929G894I10
E039E1140E1165E118E119E785
E559E782F17200I10J449 
E039E785I10I6529J309M170
E785F17200G894I10I4891I5030
E782F0390F419G609G894I10
E1165E119E669E785G629I10
E119E559I10R32  
E119E559I10J309K219 
E1122E119E162E669E785G894
E039E559E782G894I2510I4891
J3089J42    
D631E119E559E782G5600G894
D631D638E6601E785I10K5730
E119E6601N925Z6843  
J849M069    
E039E559E890F411F419L710
E039E119E559E782F419G2581
E1140E119E291E559E782F329
E559E785F419I10I509L209
E119E669E785I10I119I132
E785I10I2510N8110N952 
1 Reply

@srushing 

As variant for such result

image.png

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