Cleanup Credit Card Merchant Names Using VBA

[austinb]

Hello all,

I'm basically looking to create a macro that will search a column of cells for text from a list located on another tab and if found, replaces the text in the original cell with the text from the cell in the other tab.

For example,in Sheet1, B2 = 7-ELEVEN 38587 00073PITTSBURGH PA & B3 = 7-ELEVEN 38671 00073COPPELL TX

I would like the macro to search data found in Sheet2 for "7-ELEVEN" (not case sensitive) and replace both cells B2 & B3 in Sheet1 with just "7-Eleven".

This is somewhat of a continuation of a posted question that hasn't been updated since August of 2019 (Bank Transaction Description/Merchant Cleanup & Auto Categorize (like Mint)).

I am a VBA newbie and any help would be greatly appreciated. Thank you

-Austin

 

[alansidman]

Sample worksheets make this easier to help. Use XL2BB and provide some sample data and then show us a mocked up solution for the sample data.

 

[ER_Neha]

Hello can you please help understanding the query better ?

Suppose in sheet2 B2 there is "7-Eleven" and in C2 is " austin" , so in sheet1 what do you want in the cells B2 &B3
1. 7-Eleven

Or
2. austin

Regards
Neha

 

[austinb]

Thank you both for your time looking into my questions. Here is some sample data (hopefully this works).

Data in Sheet 1

activity.csv
	A	B	C
1	Date	Description	Amount
2	1/29/2021	7-ELEVEN 38587 00073PITTSBURGH PA	5.67
3	2/8/2021	7-ELEVEN 38671 00073COPPELL TX	49.50
4	2/14/2021	Abbey Resort	-455.80
5	2/20/2021	ADOBE CREATIVE CLOUDSAN JOSE CA	55.90
6	2/4/2021	ADOBE PHOTOGPHY PLANSAN JOSE CA	10.54
7	2/3/2021	ADOBE PS CREATIVE CLSAN JOSE CA	22.14
8	2/20/2021	Adobe Systems Incorporated	15.81
9	2/19/2021	AMAZON KIDS+*DU9FX5D888-802-3080 WA	6.99
10	2/3/2021	Amazon Web Services	1647.36
11	2/19/2021	Amazon.com	248.24
12	2/20/2021	AMAZON.COM*G03ZN92W3AMZN.COM/BILL WA	80.80
13	2/3/2021	AMAZON.COM*GX93V14H3AMZN.COM/BILL WA	1486.49
14	2/8/2021	AMAZON.COM*PD7207QO3AMZN.COM/BILL WA	46.41
15	2/16/2021	American Airlines	255.81
16	2/6/2021	AMERICAN AIRLINES 800-433-7300 TX	750.40
Data Entry

Data in Sheet 2

activity.csv
	B
1	Number
2	7-Eleven
3	Adobe Systems Incorporated
4	Amazon Web Services
5	Amazon.com
6	American Airlines
CoA, Vendors, Dept, Classes

 

[austinb]

And here is how I would like the Data in Sheet 1 to appear after the macro

activity.csv
	A	B	C
1	Date	Description	Amount
2	1/29/2021	7-Eleven	5.67
3	2/8/2021	7-Eleven	49.50
4	2/14/2021	Abbey Resort	-455.80
5	2/20/2021	Adobe Systems Incorporated	55.90
6	2/4/2021	Adobe Systems Incorporated	10.54
7	2/3/2021	Adobe Systems Incorporated	22.14
8	2/20/2021	Adobe Systems Incorporated	15.81
9	2/19/2021	AMAZON KIDS+*DU9FX5D888-802-3080 WA	6.99
10	2/3/2021	Amazon Web Services	1647.36
11	2/19/2021	Amazon.com	248.24
12	2/20/2021	Amazon.com	80.80
13	2/3/2021	Amazon.com	1486.49
14	2/8/2021	Amazon.com	46.41
15	2/16/2021	American Airlines	255.81
16	2/6/2021	American Airlines	750.40
Data Entry

 

[alansidman]

Look at this tutorial on doing a fuzzy match in Power Query

Fuzzy Match with Power Query - Excel University

Here's how to perform fuzzy matching in Excel using Power Query. Improve your data matching accuracy and efficiency!

www.excel-university.com

 

[austinb]

Look at this tutorial on doing a fuzzy match in Power Query

Fuzzy Match with Power Query - Excel University

Here's how to perform fuzzy matching in Excel using Power Query. Improve your data matching accuracy and efficiency!

www.excel-university.com

Thank you for this. I had downloaded the Fuzzy Lookup add-in long ago but never really learned how to use it. I have now downloaded the Power Query Add-In and went through the steps but I am not seeing the "Use fuzzy matching to perform the merge" option when combining my queries.

This is what I get:

 

[austinb]

OK, I think I've got it working with the Fuzzy Lookup add-in. Thank you very much for pointing me in the right direction Alan. I really appreciate it!

 

[ER_Neha]

Hello I am also providing you with the macro, I hope it works for you, Please let me know if this solves your query

Sub mycode()
Dim lastrowsh1 As Integer
Dim lastrowsh2 As Integer

Dim cell1 As Range
Dim cell2 As Range

lastrowsh1 = Sheets(1).Range("a2").End(xlDown).Row
lastrowsh2 = Sheets(2).Range("a2").End(xlDown).Row

    For Each cell1 In Sheets(1).Range("b2:b" & lastrowsh1)
        For Each cell2 In Sheets(2).Range("a2:a" & lastrowsh2)
            If InStr(1, UCase(cell1.Value), UCase(cell2.Value), 1) <> 0 Then
                cell1.Value = cell2.Value
                Exit For
            End If
        Next cell2
    Next cell1
End Sub

 

[austinb]

Hello I am also providing you with the macro, I hope it works for you, Please let me know if this solves your query

Sub mycode()
Dim lastrowsh1 As Integer
Dim lastrowsh2 As Integer

Dim cell1 As Range
Dim cell2 As Range

lastrowsh1 = Sheets(1).Range("a2").End(xlDown).Row
lastrowsh2 = Sheets(2).Range("a2").End(xlDown).Row

    For Each cell1 In Sheets(1).Range("b2:b" & lastrowsh1)
        For Each cell2 In Sheets(2).Range("a2:a" & lastrowsh2)
            If InStr(1, UCase(cell1.Value), UCase(cell2.Value), 1) <> 0 Then
                cell1.Value = cell2.Value
                Exit For
            End If
        Next cell2
    Next cell1
End Sub

Hi Neha,

Thank you for your time however I could not get this to work for me. It doesn't appear to be doing anything when I run it.