Remove duplicate words

[giannip]

Hi everyone,
I am having an issue with removing duplicating words. I have a bunch of data I need combined separated by commas. Example : High Fructose Corn Syrup, corn starch, water, sugar, corn starch, palm oil, salt, yeast, Palm oil, sugarcane, bleached sugar. Desired outcome: High Fructose Corn Syrup, corn starch, water, sugar, palm oil, salt, yeast, sugarcane, bleached sugar.
CONDITIONS: if column I (Supplier) is "Own Brand" then concatenate and remove duplicate values in R(allergens), S(Yes or No), T(ingredients), U(shared facilities) respectively, ONLY if values in column C match. Paste new concatenated string values into R, S, T, U, for Own brand rows in column I.

I do not want a function, I would like a VBA code that I can use in a Macro please. Any help would be deeply appreciated as I am extremely new to this. Baby steps please as I have been using Chatgbt to generate code and it does not provide the exact solution I need.

 

[rowebca]

Look here: Removing duplicates from a string accounting for commas

Regards,
GB

 

[CorrieAnn]

I have this same issue and did see that solution you referred to. However, I believe it creates a function. That isn't what I need and I don't know enough to alter it to my requirements. In my case, I just need it to concatenate values in A:C and place the results in column E where values in D match. (list is sorted by Column D, which holds customer IDs).

 

[Fluff]

@CorrieAnn please do not "hijack" other peoples threads.
If you have a question then you need to start your own thread. Thanks

 

[giannip]

CorrieAnn I appreciate you reiterating my issue. I mentioned a did not want a function but a Sub. the current solution attached by GB did not work for me.

 

[Max1616]

For the sake of keeping this as simple as possible, I've created some VBA that will dedupe values within a single comma delimited list. I've added code comments to hopefully make this easier to understand, but it uses nested loops, so it might be a bit confusing. The macro currently runs on column 5 (E), so if you'd like to change the column, you need to edit the first variable "clm":

Sub RemoveDupes()

Dim clm As Integer
Dim i As Long
Dim lrow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ContainsFlag As Boolean
Dim j As Integer
Dim k As Integer
Dim lclm As Integer
Dim chkValue As String
Dim FixedValue As String

clm = 5 'Column that you would like to run the deduping on. Currently set to column "E"

Set ws1 = ActiveSheet
Set ws2 = Sheets.Add 'Create a workbook for us to do our process in


lrow = ws1.Cells(Rows.Count, clm).End(xlUp).Row


For i = 2 To lrow
    ContainsFlag = False
    
    If InStr(LCase(ws1.Cells(i, clm)), "contains:") > 0 Then ContainsFlag = True Else ContainsFlag = False 'Determine if the cell has the word "Contains:"
    ws2.Range("A1") = ws1.Cells(i, clm) 'Move the contents of the cell to a new sheet so we can split it out and remove dupes
    If ContainsFlag = True Then ws2.Range("A1") = RTrim(LTrim(Application.WorksheetFunction.Substitute(ws2.Range("A1"), "Contains:", ""))) 'Remove "Contains:" if it exists, and also remove any trailing spaces
    
    ws2.Range("A:A").TextToColumns Destination:=ws2.Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True 'Split the field out into multiple cells
    
    lclm = ws2.Cells(1, Columns.Count).End(xlToLeft).Column 'Determine # of items to dedupe against
    
    
    'Loop through each value in our delimited list
    For j = 1 To lclm
        chkValue = LCase(LTrim(RTrim(ws2.Cells(1, j)))) 'Variable we are deduping
         
        
        For k = j + 1 To lclm 'Check every value after the dedupe value, to see if duplicates exist
            
            If LCase(LTrim(RTrim(ws2.Cells(1, k)))) = chkValue And chkValue <> "" Then 'If a duplicate exists, remove it
                ws2.Cells(1, k).EntireColumn.Delete
                k = k - 1 'Push the loop variable back one since we just deleted the existing column.
            End If
        
        Next k
        
        If chkValue <> "" Then FixedValue = FixedValue & chkValue & ", " 'Don't add blanks to our fixed value list
        
    Next j

    FixedValue = Application.WorksheetFunction.Proper(Left(FixedValue, Len(FixedValue) - 2)) 'After deduping the value, add it to a variable and capitolize each word's first letter.
    If ContainsFlag = True Then FixedValue = "Contains: " & FixedValue 'Add back "Contains:" if it existed originally
    
    ws1.Cells(i, clm) = FixedValue 'Replace the old value with the new value
    FixedValue = ""
    ws2.Range("1:1").ClearContents

Next i

Application.DisplayAlerts = False 'Turn off the error notification for deleting a sheet
ws2.Delete 'delete our temp sheet
Application.DisplayAlerts = True 'Turn back on error notifications

End Sub

note - I didn't add any of the conditions with your other fields, so you may want to build those into a separate macro, and then call this macro when needed.

 

[CorrieAnn]

@CorrieAnn please do not "hijack" other peoples threads.
If you have a question then you need to start your own thread. Thanks

My apologies. I wasn't trying to hijack. I was trying to support her post by saying there are others who are in need of an exact solution: a macro, not a function. In fact, I believe if you break down the fundamentals of each, they are identical. Again, my apologies.

 

[Peter_SSs]

I am having an issue with removing duplicating words.

It appears actually to be duplicate phrases or groups of words that you want to remove rather than just words.

We cannot see what columns of rows your data is in and we cannot copy it for testing**.
Because of that we (or at least I) cannot see what your 'conditions' are about.

** I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. It also makes it easy to copy your sample data for testing.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Anyway, perhaps to get you started you could try something like this in a test sheet. It simply removes duplicate comma-separated items in a column of data.

My test data

giannip.xlsm
	E
1
2	High Fructose Corn Syrup, corn starch, water, sugar, corn starch, palm oil, salt, yeast, Palm oil, sugarcane, bleached sugar
3	High Fructose Corn Syrup, corn starch
4	Corn starch, corn starch, CORN STARCH
Sheet3

My code
(Your profile does not include what platform you are using and this code will not work on a Mac)

Sub Remove_Repeats()
  Dim d As Object
  Dim a As Variant, itm As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.comparemode = 1
  With Range("E2", Range("E" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      d.RemoveAll
      For Each itm In Split(Replace(a(i, 1), ", ", ","), ",")
        d(itm) = 1
      Next itm
      a(i, 1) = Join(d.Keys, ", ")
    Next i
    .Value = a
  End With
End Sub

My results

giannip.xlsm
	E
1
2	High Fructose Corn Syrup, corn starch, water, sugar, palm oil, salt, yeast, sugarcane, bleached sugar
3	High Fructose Corn Syrup, corn starch
4	Corn starch
Sheet3

 

[kvsrinivasamurthy]

Try this macro.

Sub Remove_duplicate_words()
Dim Lr&, T&, Ta&, Clm&, S$
Dim M, N, K

Lr = Range("A" & Rows.Count).End(xlUp).Row
For T = 2 To Lr
M = Range("C" & T)
K = Range("R" & T & ":U" & T)
S = ""
    For Clm = 1 To UBound(K, 2)
    If K(1, Clm) <> "" Then
    N = Split(K(1, Clm), ", ")
        For Ta = 0 To UBound(N)
        If InStr(1, S & ", ", ", " & N(Ta) & ", ") = 0 And InStr(1, ", " & M & ", ", ", " & N(Ta) & ", ") > 0 Then
        S = S & ", " & N(Ta)
        End If
        Next Ta
    If S <> "" Then K(1, Clm) = Mid(S, 3): S = ""
    End If
    Next Clm

Range("R" & T & ":U" & T) = K: K = ""
Next T
End Sub

 

[giannip]

Mrexcel sample data.xlsx
	C
6
single

and

Mrexcel sample data.xlsx
	I
13
desired result

I have included my cells sample i am trying to combined and an example of desired result