VBA Help - Scanning two Columns for string based on lookup table

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
685
Office Version
  1. 2016
Platform
  1. MacOS
Hello All,

I am working on a project and was hoping for some help on the final piece of this puzzle.

I have a sheet Source that has two columns W or X that could contain a possible string of text that if its found I would like to place a value in column AL. I have a small lookup table on a sheet called Lookups that contains a mapping of the possible search strings and the desired replacement value that would go in column AL.

The desired result would be for a script that would Scan Column W on the Source Sheet using the lookup table and if a value matches a value from the lookup table, place the revised lookup value in Column AL, after scanning column W, if no values were found that matched the lookup table, then move on to column X on the Source Sheet and do the same search, if a value matches the lookup table drop in the revised lookup value in Column AL. There will never be a situation where there are two matching values in both W and X, the matching value will always only appear in one of the columns.

Hopefully my explanation was clear. Any help on this would be greatly appreciated!

Here is my sample Data:

Trial Balance - Working File.xlsm
WX
1SAP Doc Header TxtSAP Line Item Text
2P12 18 Accrual DetailP12 18 PDAA Movie Title
3P12 18 Accrual DetailP12 18 PDAA Movie Title
4P03 19 Accrual DetailP03 19PDAA Show Title
5P03 19 Accrual DetailP03 19PDAA Show Title
6P06 19 Accrual DetailP06 19PDAA Show Title
7P06 19 Accrual DetailP06 19PDAA Show Title
8P12 19 Accrual DetailPO9 19PDAA Show Title
9P12 19 Accrual DetailPO9 19PDAA Show Title
10P03 20 Accrual DetailPO3 20PDAA Show Title
11P03 20 AccPO3 20PDAA Show Title
12P03 20 Accrual DetailPO3 20PDAA Show Title
13P03 20 AccPO3 20PDAA Show Title
14BS Movement 05/13/22 0222BS Movement 05/13/22 0222
Source


And here is a sample of my Lookup Table

Trial Balance - Working File.xlsm
OP
1Expense MappingRevised Value
2AccrualAccrual
3AccAccrual
4PublicityPublicity
5PubPublicity
6AmortAmort
7AmortizationAmort
8RebateRebate
Lookups
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,261
Office Version
  1. 365
Platform
  1. Windows
Maybe this?

VBA Code:
Option Explicit
Sub Johnny_Thunder()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim c As Range, d As Range
    Dim FindStrng As String, RepStrng As String
    Set ws1 = Worksheets("Source")
    Set ws2 = Worksheets("Lookups")
    
    For Each c In ws2.Range("O2", ws2.Cells(Rows.Count, "O").End(xlUp))
        FindStrng = c.Value
        RepStrng = c.Offset(, 1).Value
            For Each d In ws1.Range("W2", ws1.Cells(Rows.Count, "X").End(xlUp))
                If InStr(d, FindStrng) > 0 Then ws1.Cells(d.Row, 38) = RepStrng
            Next d
    Next c
End Sub
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
685
Office Version
  1. 2016
Platform
  1. MacOS
Thanks for the Response Kevin, that actually worked out great!
 

Forum statistics

Threads
1,181,702
Messages
5,931,543
Members
436,792
Latest member
oriator

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top