Skip to Content

Solved: What is the function to create unique list of numbers that appear two or more times in Microsoft Excel?

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.

Alex Lim is a certified IT Technical Support Architect with over 15 years of experience in designing, implementing, and troubleshooting complex IT systems and networks. He has worked for leading IT companies, such as Microsoft, IBM, and Cisco, providing technical support and solutions to clients across various industries and sectors. Alex has a bachelor’s degree in computer science from the National University of Singapore and a master’s degree in information security from the Massachusetts Institute of Technology. He is also the author of several best-selling books on IT technical support, such as The IT Technical Support Handbook and Troubleshooting IT Systems and Networks. Alex lives in Bandar, Johore, Malaysia with his wife and two chilrdren. You can reach him at [email protected] or follow him on Website | Twitter | Facebook

    Ads Blocker Image Powered by Code Help Pro

    Your Support Matters...

    We run an independent site that is committed to delivering valuable content, but it comes with its challenges. Many of our readers use ad blockers, causing our advertising revenue to decline. Unlike some websites, we have not implemented paywalls to restrict access. Your support can make a significant difference. If you find this website useful and choose to support us, it would greatly secure our future. We appreciate your help. If you are currently using an ad blocker, please consider disabling it for our site. Thank you for your understanding and support.