Forum Discussion

srushing's avatar
srushing
Copper Contributor
Jul 21, 2021

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

 

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

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 23, 2021

    srushing 

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