Search textjoined rows for duplicates and consider different orders a duplicate

andrew_milonavic

Board Regular
Joined
Nov 16, 2016
Messages
95
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
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,603
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 16, 2016
Messages
95
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

Board Regular
Joined
Nov 16, 2016
Messages
95

ADVERTISEMENT

Hi Jasonb75,

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

Thanks

Andrew
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

Board Regular
Joined
Nov 16, 2016
Messages
95

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,603
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 16, 2016
Messages
95
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
quickly knocked together the following

Code:
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","")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,446
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top