Duplicated strings text in different order

[excel_2009]

Hi excel gurus!

I was wondering if someone could help me with the following:

I have a list of words such as

Black T Shirt
T Shirt Black
black t shirt

Irrespective of case they are duplicated strings albeit the text being in a different order, is it possible to use excel to group these duplicates together? I.e. the above would be in column A however beside it would be a duplicated batch number I.e. for this "batch" it would be 1

Many thanks!

 

[vicedo]

You will need to post as many samples containing the different text. But this works for the sample =IFERROR(IF(SEARCH("Black",A1),1,""),"")

 

[Peter_SSs]

Test this in a copy of your workbook to see if it does what you want.
If not, please provide more details/samples of data & expected results.

Sub GroupNumbers()
  Dim d As Object, AL As Object
  Dim a As Variant
  Dim bits() As String, s As String
  Dim i As Long, j As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  Set AL = CreateObject("System.Collections.ArrayList")
  d.CompareMode = 1
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    bits = Split(a(i, 1))
    For j = 0 To UBound(bits)
      AL.Add bits(j)
    Next j
    AL.Sort
    s = Join(AL.ToArray)
    AL.Clear
    If d.exists(s) Then
      a(i, 1) = d(s)
    Else
      d(s) = d.Count + 1
      a(i, 1) = d.Count
    End If
  Next i
  Range("B2").Resize(UBound(a)).Value = a
End Sub

My sample data & results:

Excel Workbook
	A	B
1	Item	Group
2	Black T Shirt	1
3	T Shirt Black	1
4	black t shirt	1
5	White T Shirt	2
6	T Shirt White	2
7	Blue Tie	3
8	White t shirt	2
9	Green shirt	4
10	tie blue	3
11	T shirt green	5
Sheet1

 

[Marcelo Branco]

Peter

If i understood correctly your code i noticed the use of ArrayList to sort alphabetically the strings' bits, and a Dictionary to number the groups.
Very clever!

M.

 

[Peter_SSs]

Peter

If i understood correctly your code i noticed the use of ArrayList to sort alphabetically the strings' bits, and a Dictionary to number the groups.
Very clever!

M.

Thanks Marcelo. Yes, that's exactly what I was trying to do - and using CompareMode = 1 for the dictionary to remove case-sensitivity.