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

 

[jimrward]

As you are looking at whole words/strings you would need a custom function to split the cell on slash into parts sort parts into order then rebuild string with sorted parts and slash to then compare so would need vba custom function unless of course someone comes up with a different solution

 

[jasonb75]

Would Bear / Cat / Fish be considered a duplicate of Bear / Dog / Cat / Fish, or should it only be rows where all words are duplicated?

 

[andrew_milonavic]

Hi Jimrward,

Thanks for the reply.

Is it any easier to search for duplicates prior to all the items being text joined?

Thanks

Andrew

 

[andrew_milonavic]

Hi Jasonb75,

No, I wouldn't want that to be considered a duplicate.

Thanks

Andrew

 

[jimrward]

The function would return an ordered string to compare with another ordered string

Unless you wanted to return a string in c1 etc then use countif to catch multiple duplicates

 

[andrew_milonavic]

Hi Jimrward,

My preferred way would be to search the text-joined cells for duplicates. If that's not possible using formulas than I'm open to any other approach that can show the duplicates. I'll make adjustments to my data where needed.

Thanks

Andrew

 

[jasonb75]

Is it any easier to search for duplicates prior to all the items being text joined?

It would be a lot easier because it would remove the need to split the joined text back into individual words.

 

[andrew_milonavic]

Hey Jasonb75,

Ok, if it's easier and you have a solution in mind then I'll make it work. I appreciate the help!

Thanks!

Andrew

 

[jimrward]

quickly knocked together the following

Function Ordered(str As String) As String

arr = Split(str, "/")

Call BubbleSort(arr)
Ordered = Join(arr, "/")
End Function
Sub BubbleSort(arr)
  Dim strTemp As String
  Dim i As Long
  Dim j As Long
  Dim lngMin As Long
  Dim lngMax As Long
  lngMin = LBound(arr)
  lngMax = UBound(arr)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If arr(i) > arr(j) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
      End If
    Next j
  Next i
End Sub

can be used say in cell c1 =ordered(a1)

or =IF(ordered(A1)=ordered(A2),"dup","")