Split, match, replace, Join, having trouble with the Join

[Tim Hanson]

Hello,

I am trying to split each cell from column A to the last column on ", " in sheet orgs

Then test each element against a master list in sheet A to see if any of the split elements have a match in the list

If so the replace the matched element of the split cell from Column B in sheet A

I can not get how to properly Re-Join my split cell with the replace values from the match and replacement

Ex:
sheet(Orgs)
Cells(2,1) = a,b,c,d

Sheet(A)

ColA  ColB
a        a
b        w
c        c
d        z

Then
Cells(2,1) = a,w,c,z

I appreciate any help on this I have been trying for hours, to no avail

I have uploaded a file

Thanks

Sub FindReplace_Orgs()
Dim FindValues As Variant, ReplaceValues As Variant, G As Variant, temp As Variant
Dim wsFV As Worksheet, wsRV As Worksheet
Dim sLR As Long, tLR As Long, i As Long, j As Long, n As Long

    'Sheets("Orgs").Select

    Set wsFV = ThisWorkbook.Worksheets("Orgs")
    Set wsRV = ThisWorkbook.Worksheets("A")

    sLR = wsFV.Range("A" & wsFV.Rows.Count).End(xlUp).Row
    tLR = wsRV.Range("A" & wsRV.Rows.Count).End(xlUp).Row

    For i = 2 To sLR
      For j = 2 To tLR
  
          G = Split(wsFV.Cells(i, 1).value, ", ")
          temp = G
          For n = LBound(G) To UBound(G)
            If splitCell(wsFV.Range("A" & i), ", ", n) = wsRV.Cells(j, "A") Then
              temp(i) = Join(wsRV.Cells(j, "B").value, ", ")
              'MsgBox G(n) & "  " & temp(i)
            End If
          Next n
    
      Next j
    Next i
    wsFV.Range("A2:A" & sLR).value = temp
End Sub

Helper Function

Function splitCell(strValue As String, delim As String, Optional n As Long) As String

  If strValue <> "" Then
    splitCell = Split(strValue, delim)(n)
  Else
    strValue = ""
  End If

End Function

 

[NARAYANK991]

Hi ,

See if this is what you wanted.

Narayan

 

[Tim Hanson]

Narayan, thank you, this worked perfectly

I greatly appreciate your help

 

[Tim Hanson]

@Narayan

If I may ask you, is it possible to alter your code such that if my replacement data has empty cells it only update for cells with data

I have tried but I do not understand what is going on well enough to do it myself

Thank you

Ex

 

[NARAYANK991]

Hi ,

Can you take the following example :

Los Angeles Sherrif Dept, Whole Person Care, Housing for Health, Office of Immigrant Affairs

and post what the output will be ?

Narayan

 

[Tim Hanson]

Sorry for my lack of clarity

Here is what your example would become:
RRR_25, Whole Person Care, RRR_7, Office of Immigrant Affairs

If there is a replacement value then replace that for its corresponding value in List otherwise keep all the original values in the string as they are

Thanks again for your interest in my question

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Tim Hanson]

Narayan,

Thank you that works great, just what I needed