Problem
The formula in cell M5 creates unique list of numbers regardless of how many times they appear. The numbers that appears two or more times should show above those starting in cell M4 going across not down.
I try to use the following formula but keep getting errors:
=IFERROR(INDEX(B$2:F$11,MATCH(1,INDEX(((COUNTIF(L2:L$2,B$2:F$11)=0)*(COUNTIF(B$2:F$11,B$2:F$11)>=2)),),0)),"")How do I create a unique list with unique numbers that appears two or more times in cells? Please provide a working formula fr unique values rather than duplicate values.
Solution #1
There may be a simpler formula, but the following is working in Microsoft 365:
=LET(a,SORT(--TEXTSPLIT(TEXTJOIN(",",,INDIRECT("C" & J5 & ":G" & K5)),","),,,TRUE),n,COUNTA(a),i,SEQUENCE(,n-1),b,UNIQUE(IF(INDEX(a,i)=INDEX(a,i+1),INDEX(a,i),"")),TRANSPOSE(UNIQUE(FILTER(TRANSPOSE(b),TRANSPOSE(b)<>""))))Solution #2
Shorter formula:
=LET(a,SORT(TOCOL(INDIRECT("C" & J5 & ":G" & K5))),n,COUNTA(a),i,SEQUENCE(,n-1),b,TRANSPOSE(IF(INDEX(a,i)=INDEX(a,i+1),INDEX(a,i),"")),TRANSPOSE(UNIQUE(FILTER(b,b<>""))))Solution #3
Shorten formula in cell M5 to:
=LET(a,SORT(TOCOL(INDIRECT("C" & J5 & ":G" & K5))),TRANSPOSE(UNIQUE(FILTER(a,a<>""))))I moved the formula for two or more occurrences to cell M4, and put the suggested formula for the sorted list of numbers in cell M6. Your original formula remains in cell M5 for comparison.
