Search textjoined rows for duplicates and consider different orders a duplicate

[andrew_milonavic]

Hi,

I have 10 text-joined rows, A1-A10, each with up to 12 items and separated by a " / " as the delimiter. Is it possible to flag duplicates with a color, including when the order might different? i.e if A1 has bear / fish / cat and A2 has fish / bear / cat then they will be considered duplicates.

Possible with a formula?

If the duplicates cannot be flagged with a color I'm open to other options including adding extra columns or rows. I just need a fast way to show duplicates.

Thanks

Andrew

 

[andrew_milonavic]

Thanks Jimrward,

Unfortunately I'm blocked from using VBA's at work. I've copied the script just in case that ever changes.

Thanks

Andrew

 

[jasonb75]

I think that I've managed to make it work with the joined text, give this formula a go and see if it looks right. Assumed data is in A2:A11, change as needed.

This should return TRUE for any row which is duplicated, as such it can be used as a conditional formatting rule to flag with a colour change.

=SUMPRODUCT((LEN($A$2:$A$11)=LEN(A2))*ISNUMBER(SEARCH("/ "&TRIM(MID(SUBSTITUTE(A2," / ",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&(LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))+1)))-1)*LEN(A2)+1,LEN(A2)))&" /","/ "&$A$2:$A$11&" /")))>1

 

[andrew_milonavic]

Hey Jasonb75,

Awesome!! I'll give it a try and report back shortly.

Thanks

Andrew

 

[andrew_milonavic]

Hey Jasonb75,

I seem to only be able to get it to compare for the first three rows. From row 4-10 it doesn't seem to compare.

Thoughts?

Edit: I think it has something to do with which row the potential comparison is in. I'm getting mixed results.

Thanks

Andrew

 

[jasonb75]

I had only used 3 rows of data for testing, when I tried it with more I found several errors in my logic.

Going back and starting again, I think that I have it right this time. This one is an array formula so must be committed using Crtl Shift and Enter, not just Enter.

=INDEX(FREQUENCY(FREQUENCY(IF(LEN($A$2:$A$11)=LEN(A2),IF(ISNUMBER(SEARCH("/ "&TRIM(MID(SUBSTITUTE(A2," / ",REPT(" ",LEN(A2))),TRANSPOSE(ROW(INDIRECT("1:"&(LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))+1)))-1)*LEN(A2)+1,LEN(A2)))&" /","/ "&$A$2:$A$11&" /")),ROW($A$2:$A$11))),ROW($A$2:$A$11)),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))+{0,1,2}),2)>1

Note that it when comparing examples like bear / cat / fish and bear / cat / bear you could have some false positives.
This should only happen if any word(s) are repeated in the same cell and all of the words match up to another with the same total character count.
Using another example, bear / cat / fish and bear / cat / cat would not give a false positive because the first string contains 17 characters, while the second only contains 16.

Isolating the above will need vba.

 

[andrew_milonavic]

Hey Jasonb75,

That works perfect!!! I set up a rand-between of a list to test it. Then ran through about 100 combinations and didn't have a single error.

I really appreciate all your help!!! I know it wasn't an easy ask.

Thanks

Andrew