Finding and removing duplicate wording in each row of Excel

Hi, I'm new here so I'm not sure how to go about this. I've been searching around on the forum but I couldn't quite seem to get the answer I needed for my particular situation. So here I have 3 columns of data. I am trying to delete the duplicate words from Column A and B that are in Column C. I am trying to find a quick, automated way to do this for 1000+ entries. Does anyone have any ideas? Much appreciated.


ExcelForum Example.xlsx

Ah sorry, I messed up in the excel sheet. So with the format I have currently in the sheet, I want to remove the second "Cooler Master CO., LTD." and "RR-LIE-L9E1-GP".

@tehneXus Where would I add the separation string? Does that mean I have to add it to each entry?

ExcelForum Example.xlsx

Isn't it

=TRIM(SUBSTITUTE(SUBSTITUTE(C1,A1,"",2),B1,"",2))

?

@jindon I tried your method and but it doesn't exactly work for my purposes.


1. SEAGATE TECHNOLOGY


2. STBU500102


3. SEAGATE TECHNOLOGY STBU500102 SEAGATEBACKUPPLUSP


Say if that was in each respective Column 1, 2, and 3. I was wondering if it's possible to automate excel to scan column 3 for that second "SEAGATE" word and remove it while leaving the "BAKCUPPLUSP".

Then UDF

Use in cell like

=RemoveNthLikeWords(C1,2,A1,B1)

Where ;
C1 = whole string to be processed
2 = reference # for n th from the start
A1,B1 = contains string to be removed and you can add more like A1,B1,D1,F1.... etc

To a standard module

Function RemoveNthLikeWords(txt As String, n As Long, ParamArray a()) As String
    Dim e, s
    For Each e In a
        For Each s In Split(e)
            txt = Application.Substitute(txt, s, "", n)
        Next
    Next
    RemoveNthLikeWords = txt
End Function

Thanks jindon. Your function seems to work for most of the data.

However I came across some cells where the function didn't seem to work? I had to change the 'n reference' # to fix that cell but I would I have go through every cell to double check. Is there a fix for that?

For data like:

1. LSI LOGIC CORPORATION

2. LSI00182

3. LSI LOGIC CORPORATION LSI00182 LSI SAS3081E-R SINGLE PACK

How do want the result?

If possible, I want it to look like

LSI LOGIC CORPORATION LSI00182 SAS3081E-R SINGLE PACK

so the second "LSI" is removed.

Try change to

=TRIM(RemoveLastLikeWords(C1,A1,B1))

Function RemoveLastLikeWords(txt As String, ParamArray a()) As String
    Dim e, s, m As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        For Each e In a
            For Each s In Split(e)
                .Pattern = "\b" & Trim$(s)
                If .test(txt) Then
                    Set m = .Execute(txt)
                    If m.Count > 1 Then
                        txt = Application.Substitute(txt, Trim$(s), "", m.Count)
                    End If
                End If
            Next
        Next
    End With
    RemoveLastLikeWords = txt
End Function

That's perfect! Thanks jindon