Search code examples
excelexcel-formulanumbersrepeat

Excel - how to find repeating numbers and how to create a new searching range after we have found a match?

I cannot find a good solution to achieve the desired results described on the picture:

enter image description here

Can someone please help me with this?

I have tried googling and also asked from AI, but still haven´t managed to get the right solution.

Example Data:

Number Expected outcome
222 unique number
333 unique number
555 unique number
666 unique number
888 unique number
333 match
444 unique number
888 unique number
222 unique number
444 match
555 unique number
666 unique number
555 match
777 unique number
Solution

  • With ms365, though I'm probably overthinking this, try:

    enter image description here

    Formula in B3:

    =LET(z,SCAN(3,ROW(A3:A16),LAMBDA(x,y,IF(SUM(N(INDEX(A:A,x):INDEX(A:A,y)=INDEX(A:A,y)))=2,y,x))),IF(z=VSTACK(MIN(z),DROP(z,-1)),"Unique Number","Match"))

    Or, a little less verbose:

    =DROP(REDUCE("Match",A3:A16,LAMBDA(x,y,VSTACK(x,IF(SUM(N(INDEX(A:A,XMATCH("Match",x,,-1)+2):y=y))=2,"Match","Unique Number")))),1)