Remove a text string in column B based on column C

[Pete2020]

I am very thankful to the Mr. Excel forum in providing right solutions.

my data consists 70k rows and would be helpful if you Provide a solution through VBA

1. Match every comma separated string of column C with Column B at each row level (C2 with B2)
2. If found - it should be remove the word along with Space and special characters or comma infront of it.
3. The output should be populated in Column D

Search and remove.xlsx
	A	B	C	D
1	School ID	Client Name	Location - match words and remove from column B	Expected Output
2	SC-001	Bachpan Play School, Lohara	MIDC Area,Darwha Road, Lohara, Yavatmal	Bachpan Play Schoo1
3	SC-002	Citizen Technical Institute Washim	Washim, Maharashtra, India	Citizen Technical Institute
4	SC-003	Absolute Spoken English, wardha, maharashtra, india	Hindinagar, Wardha, Maharashtra, India	Absolute Spoken English
5	SC-004	Kasturba Nursing college, KHS, Sevagram	Sevagram, Maharashtra, India	Kasturba Nursing college
6	SC-005	CP Goenka International School, Oshiwara | Andheri West	Oshiwara, Andheri West, Mumbai, Maharashtra	CP Goenka International School
7	SC-006	Sri Sri Ravishankar Vidya Mandir (SSRVM), Mulund, Mumbai	Sarvodaya Nagar, Mulund West, Mumbai, Maharashtra	Sri Sri Ravishankar Vidya Mandir (SSRVM)
8	SC-007	Saleem Zakerya Urdu High School Pusegaon	Pusegaon Main Rd	Saleem Zakerya Urdu High School
Sheet1

 

[GraH]

Hi Pete2020

Since you have 2016 version, you might consider a Power Query alternative.

1. Data - Get & Transform Data -> From Table/Range
2. Add 3 Custom Columns using the following formulas
   1. "List Client Names" = Text.SplitAny(Text.Proper([Client Name]), ", |;")
   2. "List Location" = Text.SplitAny(Text.Proper([Location]), ", |;")
   3. "Remaining" = List.RemoveItems([List Client Names],[List Location])
3. Extract the "Remaining" by clicking the arrows pointing up
4. Delete the helper columns
5. Save and load to Excel

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ListOfName = Table.AddColumn(Source, "List Client Names", each Text.SplitAny(Text.Proper([Client Name]), ", |;")),
    ListOfLocation = Table.AddColumn(ListOfName, "List Location", each Text.SplitAny(Text.Proper([Location]), ",  |;")),
    DifferenceOfLists = Table.AddColumn(ListOfLocation, "Remaining", each List.RemoveItems([List Client Names],[List Location])),
    ExtractRemaining = Table.TransformColumns(DifferenceOfLists, {"Remaining", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    RemoveHelperListColumns = Table.RemoveColumns(ExtractRemaining,{"List Client Names", "List Location"})
in
    RemoveHelperListColumns

Book2
	A	B	C	D
1	School ID	Client Name	Location	Expected Output
2	SC-001	Bachpan Play School, Lohara	MIDC Area,Darwha Road, Lohara, Yavatmal	Bachpan Play Schoo1
3	SC-002	Citizen Technical Institute Washim	Washim, Maharashtra, India	Citizen Technical Institute
4	SC-003	Absolute Spoken English, wardha, maharashtra, india	Hindinagar, Wardha, Maharashtra, India	Absolute Spoken English
5	SC-004	Kasturba Nursing college, KHS, Sevagram	Sevagram, Maharashtra, India	Kasturba Nursing college
6	SC-005	CP Goenka International School, Oshiwara | Andheri West	Oshiwara, Andheri West, Mumbai, Maharashtra	CP Goenka International School
7	SC-006	Sri Sri Ravishankar Vidya Mandir (SSRVM), Mulund, Mumbai	Sarvodaya Nagar, Mulund West, Mumbai, Maharashtra	Sri Sri Ravishankar Vidya Mandir (SSRVM)
8	SC-007	Saleem Zakerya Urdu High School Pusegaon	Pusegaon Main Rd	Saleem Zakerya Urdu High School
9
10
11
12
13	School ID	Client Name	Location	Remaining
14	SC-001	Bachpan Play School, Lohara	MIDC Area,Darwha Road, Lohara, Yavatmal	Bachpan Play School
15	SC-002	Citizen Technical Institute Washim	Washim, Maharashtra, India	Citizen Technical Institute
16	SC-003	Absolute Spoken English, wardha, maharashtra, india	Hindinagar, Wardha, Maharashtra, India	Absolute Spoken English
17	SC-004	Kasturba Nursing college, KHS, Sevagram	Sevagram, Maharashtra, India	Kasturba Nursing College Khs
18	SC-005	CP Goenka International School, Oshiwara | Andheri West	Oshiwara, Andheri West, Mumbai, Maharashtra	Cp Goenka International School
19	SC-006	Sri Sri Ravishankar Vidya Mandir (SSRVM), Mulund, Mumbai	Sarvodaya Nagar, Mulund West, Mumbai, Maharashtra	Sri Sri Ravishankar Vidya Mandir (Ssrvm)
20	SC-007	Saleem Zakerya Urdu High School Pusegaon	Pusegaon Main Rd	Saleem Zakerya Urdu High School
Sheet3

 

[Pete2020]

Thank You for giving a solution through Power Query.I am not much comfortable.

Can some one help me out in giving a VBA for my Problem mentioned above

 

[Mentor82]

Hi,
Check that and let me know it that's what you need.

Sub ReplaceValues()
    Dim lastRow&
    Dim arr() As String
    Dim i&
    Dim el
    Dim str$
    Dim str2split$
    
    
    lastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
    
    For i = 2 To lastRow
        str = vbNullString
        str = CStr(Cells(i, "B"))
        
        Cells(i, "D") = str
        
        str2split = CStr(Cells(i, "C"))
        arr() = Split(str2split, ",")
        
        For Each el In arr
            str = Replace(str, ",", "", 1)
            str = Replace(str, " |", "", 1)
            str = Replace(str, el, "", 1)
        Next el
        
        Cells(i, "D") = str
    
    Next i
    
    MsgBox "Done"
    
End Sub

 

[Pete2020]

Looks awesome, It exactly fits my requirement and also macro is quite understandable to edit as per the requirements.

Thanks You So Much

 

[Peter_SSs]

Match every comma separated string of column C with Column B at each row level (C2 with B2)

Then in your expected results ..

1. Why has 'KHS' been removed from row 5 when it is not listed in column C?
2. Why has 'Muland' been removed in row 7 as the comma separated value in column C is 'Muland West' not 'Muland'?
3. Why has anything been removed from row 8. There are no commas in C8 so the value to search for would be 'Pusegaon Main Rd' which does no appear in B8?

Are you sure that the suggested code does what you want? I ran the code with this sample data but I don't think that is the result you would want?
In the first one, two 'part-words' are removed and the remaining 'part-words' are joined.
In the second one, Kasturba is not removed despite being in the column C list.

Pete2020 2020-05-06 1.xlsm
	B	C	D
9	Indian Western High School India	India, West	nern High School
10	Kasturba Nursing college, KHS, Sevagram	Sevagram, Maharashtra, India, Kasturba	Kasturba Nursing college KHS
Sheet1 (2)

 

[Pete2020]

Thanks Peter.I Just had a look. It looks that macro required a tweak.

Please help me fixing the issue.

 

[Peter_SSs]

Please help me fixing the issue.

Well, what was the response to my three numbered questions?
Were they errors with your expected results or were the requirements not specified correctly?

 

[Pete2020]

hen in your expected results ..

1. Why has 'KHS' been removed from row 5 when it is not listed in column C?
2. Why has 'Muland' been removed in row 7 as the comma separated value in column C is 'Muland West' not 'Muland'?
3. Why has anything been removed from row 8. There are no commas in C8 so the value to search for would be 'Pusegaon Main Rd' which does no appear in B8?

Answers to Above
1. KHS should not be removed
2. Muland should not be removed, The macro should consider 'Muland West" as single word.
3. Nothing to be removed from C8 as there is no comma

In the first one, two 'part-words' are removed and the remaining 'part-words' are joined. - I agree
In the second one, Kasturba is not removed despite being in the column C list. - I agree

You are right - Word match should be based on comma.It should also consider spaces within a word for exact match against column B

Thanks Peter for your Great Observations

 

[Peter_SSs]

See how this goes.

Sub Remove_Unwanted()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  a = Range("B2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    RX.Pattern = "[^A-Za-z\)]*\b(" & Replace(Replace(a(i, 2), ", ", ","), ",", "|") & ")\b[^A-Za-z\(]*"
    a(i, 1) = RX.Replace(a(i, 1), "")
  Next i
  Range("D2").Resize(UBound(a)).Value = a
End Sub

My sample data and results.

Pete2020 2020-05-06 1.xlsm
	B	C	D
1	Client Name	Location - match words and remove from column B	Output
2	Bachpan Play School, Lohara	MIDC Area,Darwha Road, Lohara, Yavatmal	Bachpan Play School
3	Citizen Technical Institute Washim	Washim, Maharashtra, India	Citizen Technical Institute
4	Absolute Spoken English, wardha, maharashtra, india	Hindinagar, Wardha, Maharashtra, India	Absolute Spoken English
5	Kasturba Nursing college, KHS, Sevagram	Sevagram, Maharashtra, India	Kasturba Nursing college, KHS
6	CP Goenka International School, Oshiwara | Andheri West	Oshiwara, Andheri West, Mumbai, Maharashtra	CP Goenka International School
7	Sri Sri Ravishankar Vidya Mandir (SSRVM), Mulund, Mumbai	Sarvodaya Nagar, Mulund, Mumbai, Maharashtra	Sri Sri Ravishankar Vidya Mandir (SSRVM)
8	Saleem Zakerya Urdu High School Pusegaon	Pusegaon Main Rd	Saleem Zakerya Urdu High School Pusegaon
9	Indian Western High School India	India, West	Indian Western High School
10	Kasturba Nursing college, KHS, Sevagram	Sevagram, Maharashtra, India, Kasturba	Nursing college, KHS
Sheet1 (2)