Formula for top 10

%3CLINGO-SUB%20id%3D%22lingo-sub-2572149%22%20slang%3D%22en-US%22%3EFormula%20for%20top%2010%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2572149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EHelp!%26nbsp%3B%20I%20need%20a%20formula%20to%20determine%20the%20top%2010%20most%20often%20found%20codes%20(diagnosis%20codes)%20similar%20to%20this%20list.%26nbsp%3B%20I%20have%20them%20saved%20as%20text.%26nbsp%3B%20Thanks!!%26nbsp%3B%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22379%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2259%22%3EE119%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EE785%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EI10%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EK219%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EE6601%3C%2FTD%3E%3CTD%3EG609%3C%2FTD%3E%3CTD%3EG8929%3C%2FTD%3E%3CTD%3EG894%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE039%3C%2FTD%3E%3CTD%3EE1140%3C%2FTD%3E%3CTD%3EE1165%3C%2FTD%3E%3CTD%3EE118%3C%2FTD%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE785%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EE782%3C%2FTD%3E%3CTD%3EF17200%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3EJ449%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE039%3C%2FTD%3E%3CTD%3EE785%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3EI6529%3C%2FTD%3E%3CTD%3EJ309%3C%2FTD%3E%3CTD%3EM170%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE785%3C%2FTD%3E%3CTD%3EF17200%3C%2FTD%3E%3CTD%3EG894%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3EI4891%3C%2FTD%3E%3CTD%3EI5030%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE782%3C%2FTD%3E%3CTD%3EF0390%3C%2FTD%3E%3CTD%3EF419%3C%2FTD%3E%3CTD%3EG609%3C%2FTD%3E%3CTD%3EG894%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE1165%3C%2FTD%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE669%3C%2FTD%3E%3CTD%3EE785%3C%2FTD%3E%3CTD%3EG629%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3ER32%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3EJ309%3C%2FTD%3E%3CTD%3EK219%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE1122%3C%2FTD%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE162%3C%2FTD%3E%3CTD%3EE669%3C%2FTD%3E%3CTD%3EE785%3C%2FTD%3E%3CTD%3EG894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE039%3C%2FTD%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EE782%3C%2FTD%3E%3CTD%3EG894%3C%2FTD%3E%3CTD%3EI2510%3C%2FTD%3E%3CTD%3EI4891%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJ3089%3C%2FTD%3E%3CTD%3EJ42%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ED631%3C%2FTD%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EE782%3C%2FTD%3E%3CTD%3EG5600%3C%2FTD%3E%3CTD%3EG894%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ED631%3C%2FTD%3E%3CTD%3ED638%3C%2FTD%3E%3CTD%3EE6601%3C%2FTD%3E%3CTD%3EE785%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3EK5730%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE6601%3C%2FTD%3E%3CTD%3EN925%3C%2FTD%3E%3CTD%3EZ6843%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJ849%3C%2FTD%3E%3CTD%3EM069%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE039%3C%2FTD%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EE890%3C%2FTD%3E%3CTD%3EF411%3C%2FTD%3E%3CTD%3EF419%3C%2FTD%3E%3CTD%3EL710%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE039%3C%2FTD%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EE782%3C%2FTD%3E%3CTD%3EF419%3C%2FTD%3E%3CTD%3EG2581%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE1140%3C%2FTD%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE291%3C%2FTD%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EE782%3C%2FTD%3E%3CTD%3EF329%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE559%3C%2FTD%3E%3CTD%3EE785%3C%2FTD%3E%3CTD%3EF419%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3EI509%3C%2FTD%3E%3CTD%3EL209%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE119%3C%2FTD%3E%3CTD%3EE669%3C%2FTD%3E%3CTD%3EE785%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3EI119%3C%2FTD%3E%3CTD%3EI132%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EE785%3C%2FTD%3E%3CTD%3EI10%3C%2FTD%3E%3CTD%3EI2510%3C%2FTD%3E%3CTD%3EN8110%3C%2FTD%3E%3CTD%3EN952%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2572149%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2579110%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20top%2010%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2579110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1109419%22%20target%3D%22_blank%22%3E%40srushing%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20for%20such%20result%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20559px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297965i082935B70B5C3A77%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0A%20topN%2C%2010%2C%0A%20r%2C%20%20%20%20ROWS(range)%2C%0A%20c%2C%20%20%20%20COLUMNS(range)%2C%0A%20k%2C%20%20%20%20SEQUENCE(r*c)%2C%0A%20list%2C%20INDEX(range%2C%20MOD(k-1%2Cr)%2B1%2C%20INT((k-1)%2Fr)%2B1)%2C%0A%20noBlanks%2C%20FILTER(list%2C%20NOT(ISBLANK(list)))%2C%0A%20j%2C%20%20%20%20SEQUENCE(ROWS(noBlanks))%2C%0Apos%2C%20%20%20XMATCH(noBlanks%2CnoBlanks)%2C%0Afreq%2C%20%20SORT(IF(%7B1%2C0%7D%2C%20FREQUENCY(pos%2C%20j)%2C%20j)%2C%2C-1)%2C%0A%20n%2C%20%20%20%20SEQUENCE(topN)%2C%0A%20IF(%7B1%2C0%7D%2C%20%20INDEX(noBlanks%2CINDEX(freq%2Cn%2C2))%2C%20INDEX(freq%2C%20n%2C%201))%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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