Find duplicate including numbers are in different sequence/combination

[bianbianchaoren]

Ive been scratching my head on this for a while, appreciate any help I can get ?

basically I’m trying to identify if there is any duplicates in a column including numbers that are in different sequence/combination, the list of numbers will always be in a fixed length.

I.e
Column A
1234
2345
2234
3412

Result: cell for 1234 and 3412 will become red indicating the duplicates even though it’s in different combination or in another column B indicating Y/N works too

thanks in advance!

 

[jasonb75]

Something like this, perhaps?

Book1 (version 2).xlsb
	A	B	C
2	1234	1234	Y
3	2345	2345	N
4	2234	2234	N
5	3412	1234	Y
Sheet7

Cell Formulas
Range	Formula
B2:B5	B2	=CONCAT(AGGREGATE(15,6,--MID(A2,SEQUENCE(LEN(A2)),1),SEQUENCE(LEN(A2))))
C2:C5	C2	=IF(COUNTIF($B$2:$B$5,B2)>1,"Y","N")

 

[Peter_SSs]

Something like this, perhaps?

Suggestion for alternative for B2

=CONCAT(SORT(MID(A2,SEQUENCE(LEN(A2)),1)))

@bianbianchaoren
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The suggestions above all require Microsoft 365

 

[Peter_SSs]

Or still with MS 365 if it includes the LET function, it could be done without a helper column using Conditional Formatting with this formula or else use this same formula in an adjacent column to return True/False for dupes/non dupes.

21 03 21.xlsm
	A
1
2	1234
3	2345
4	2234
5	3412
Dupes

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A5	Expression	=LET(L,LEN(A2),seqa,SEQUENCE(,L),r,A$2:A$5,n,ROWS(r),seqb,SEQUENCE(,L*n,,9),seqc,SEQUENCE(,L*n,8,9),seqd,SEQUENCE(,n,,L),tj,TEXTJOIN("#",1,TEXT(ROW(r),"000000")&"|"&MID(r,seqa,1)),COUNT(FILTER(MID(CONCAT(MID(TEXTJOIN("|",1,SORT(MID(tj,seqb,8),,,1)),seqc,1)),seqd,L)+0,MID(CONCAT(MID(TEXTJOIN("|",1,SORT(MID(tj,seqb,8),,,1)),seqc,1)),seqd,L)+0=CONCAT(SORT(MID(A2,seqa,1),,,1))+0,""))>1)	text	NO