Dynamic lookup list for VAT or sales tax

[greengairs]

Hi

I need some help here - basically i want to lookup a list of bank transactions (sheet 2 column f on a row by row) and return the word "NEVER" in column G next to the transaction if it can match part of a word as found in Sheet 1 column A
I need to find the unique words on sheet 1 (eg. smith is not good enough i need to find "J Smith dividend" as opposed to a transaction which may say "WH Smith"

What im trying to do is build a manual list of transaction descriptions which do not attract VAT or sales tax, so these can be excluded up front with a flag rather going through 100s of transactions. I cant create fully unique id as the banking file add on dates and other alpha numeric codes for each transaction.


Data Examples

Sheet 1 Column A1:A100 [ I am manually creating these data lists in sheets A - UNSORTED]
Uber
Tesco
J Smith Salary
J Jones Expenses
J Smith Dividend

Sheet 2 Column F1:F500 [A list of bank transaction descriptions from Bank - UNSORTED]
UBER BV 24018
Tesco Stores Scotland AA
WH Smith - England
J Smith Salary Feb 2018
J Smith Salary March 2018
Tesco Stores England AA

Ive tried index and list products and cant get it to work. Hope someone can help this will save me tonnes of time

thanks graeme.

 

[Kamolga]

Hi,

This macro checks if cells of sheet 1 are included in Sheet 2 ("OK") and writes "Never" in column G if it does not

Sub test()

[COLOR=#006400]'Determine last row in sheet1 column A and sheet 2 column G
[/COLOR]
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
 Set Sh1 = ActiveWorkbook.Sheets("Sheet1")
 Set Sh2 = ActiveWorkbook.Sheets("Sheet2")
Dim LRSh1 As Long
Dim LRSh2 As Long
 LRSh1 = Sh1.Cells(Sh1.Rows.Count, "A").End(xlUp).Row
 LRSh2 = Sh2.Cells(Sh2.Rows.Count, "F").End(xlUp).Row

[COLOR=#006400]'Look up if a value of Sh1 A is included in SH2 F[/COLOR]

Dim Rng1 As Range
Dim Rng2 As Range
    Set Rng1 = Sh1.Range("A1:A" & LRSh1)
    Set Rng2 = Sh2.Range("F1:F" & LRSh2)
Dim CellA As Range
Dim CellB As Range
Dim Str1 As String
Dim Str2 As String

For Each CellB In Rng2
 Str2 = UCase(CellB.Value)
        For Each CellA In Rng1
            Str1 = UCase(CellA.Value)
                 If InStr(1, Str2, Str1) > 0 Then
                    CellB.Offset(0, 1).Value = "OK"
                    GoTo ThisOk
                 End If       
                    CellB.Offset(0, 1).Value = "Never"
        Next CellA
ThisOk:
Next CellB     
End Sub

 

[greengairs]

excellent and thanks - works great - the OK and NEVER labelling in the code just need to be switched around

This macro checks if cells of sheet 1 are included in Sheet 2 ("OK") and writes "Never" in column G if it does not

Sub test()

[COLOR=#006400]'Determine last row in sheet1 column A and sheet 2 column G
[/COLOR]
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
 Set Sh1 = ActiveWorkbook.Sheets("Sheet1")
 Set Sh2 = ActiveWorkbook.Sheets("Sheet2")
Dim LRSh1 As Long
Dim LRSh2 As Long
 LRSh1 = Sh1.Cells(Sh1.Rows.Count, "A").End(xlUp).Row
 LRSh2 = Sh2.Cells(Sh2.Rows.Count, "F").End(xlUp).Row

[COLOR=#006400]'Look up if a value of Sh1 A is included in SH2 F[/COLOR]

Dim Rng1 As Range
Dim Rng2 As Range
    Set Rng1 = Sh1.Range("A1:A" & LRSh1)
    Set Rng2 = Sh2.Range("F1:F" & LRSh2)
Dim CellA As Range
Dim CellB As Range
Dim Str1 As String
Dim Str2 As String

For Each CellB In Rng2
 Str2 = UCase(CellB.Value)
        For Each CellA In Rng1
            Str1 = UCase(CellA.Value)
                 If InStr(1, Str2, Str1) > 0 Then
                    CellB.Offset(0, 1).Value = "OK"
                    GoTo ThisOk
                 End If       
                    CellB.Offset(0, 1).Value = "Never"
        Next CellA
ThisOk:
Next CellB     
End Sub

[/QUOTE]