CODEWITHSUNDEEP
excelexcel-formulanumbersrepeat
Karla0103
Karla0103

Reputation: 3

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

Upvotes: 0

Views: 286

Answers (3)

Karla0103
Karla0103

Reputation: 3

Thanks for thinking along, you guys are incredible. You also saved a lot of my time, coz I have over 15k rows of data.

So I got 3 solutions and only 1 of them worked in a way that I wanted.

"=LET(z,SCAN(...." worked exactly like I wanted.

The other 2 solutions probably need slight modifications. But most of the times they worked well too. The problem arises when we look at two consecutive "matches" and if they are exactly the same number, then it won´t recognise the second number as a "match". I gave you an example where 3 different numbers get matched (333, 444 and 555). But for example if we replace the matching 444 with number 333 then it also should be a match but these two solutions/formulas won´t recognise it.

Upvotes: 0

David Leal
David Leal

Reputation: 6759

Another alternative using REDUCE. Similar idea to @JvdV it just avoids using :-Range operator (it doesn't work sometimes inside a LAMBDA function):

=LET(A,A2:A15, seq, SEQUENCE(ROWS(A)),
 DROP(REDUCE("Match", seq, LAMBDA(ac,s,
  VSTACK(ac,IF(ROWS(FILTER(A,(A=INDEX(A,s)) 
   * (seq<= s) * (seq >= XMATCH("Match", ac,,-1))))>1,
    "Match", "Unique Number")))),1))

Here is the output: output

It uses REDUCE/VSTACK pattern(1). The accumulator (ac) is initialized with "Match" value. It iterates over all index positions (seq) of the input (A). On each iteration it filters A by looking for values equal to the corresponding index position (INDEX(A,s)), current index position or previous (seq<=s) and index position greater or equal than XMATCH("Match", ac,,-1), it does a backward search (-1, from bottom up) in ac, to find the first "Match" value, since the accumulator was initialized with this value, it always returns a valid value. The last two FILTER conditions ensure the subset of our interest (star-end) on each iteration. If FILTER output has more than one row, some duplicated was found and this is the condition we are looking for "Match", otherwise it will be "Unique Number".

(1): how to transform a table in Excel from vertical to horizontal but with different length

Upvotes: 0

JvdV
JvdV

Reputation: 75850

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)

Upvotes: 2

Related Questions