Convert Formula to VBA

[Sufiyan97]

I am using below formula which works Perfectly for Fatching Vendor Name based on partial text match
It searches text from Cells G2 to J1000
finds it in column A and Puts Vendor name and account from Column E to F

Is there a way to convert this formula into VBA so when I run the code it does the same job as formula

Book5
	A	B	C	D	E	F	G	H	I	J
1	Description	Vendor Name	Account		Vendor Name	Account	Description1	Description2	Description3	Description4
2	MERCURY INS DES:PAYMENT ID:1000514717-9306 INDN:JENSEN, DAVID CO ID:GXXXXXXXXX PPD	Mercury Insurance	Insurance Expense		7-11	Auto and Truck Expenses	7-ELEVEN	7ELEVEN	7ELE	#N/A
3	ALLSTATE INS CO DES:INS PREM ID:000000981945736 INDN:JENSEN CO ID:1360719665 PPD	Allstate Insurance	Insurance Expense		Advance Auto	Other Job Related Costs	ADVANCE AUTO P	#N/A	#N/A	#N/A
4	CHECKCARD 1230 OLIVE GARDEN 0021 ORLANDO FL 55310208365091727000057 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Agilis Linxup	#N/A	AGILIS	#N/A	#N/A	#N/A
5	CHECKCARD 1230 OLIVE GARDEN 000 ORLANDO FL 15410198365140493695099 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Allstate Insurance	Insurance Expense	ALLSTATE	#N/A	#N/A	#N/A
6	CHECKCARD 0101 DOMINO'S 9450 407-852-9595 FL 05436849002500098516305 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Domino's	Owner's Draw		Amazon	Owner's Draw	AMAZON.COM	AMAZON DIGITAL	#N/A	#N/A
7	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02				Amtrust	#N/A	AMTRUST	#N/A	#N/A	#N/A
8	CHECKCARD 0102 AGILIS LINXUP MOT 877-732-4980 MO 55432869002200773574177 CKCD 5734 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Agilis Linxup			Annual Pass Flex	Owner's Draw	ANNUAL PASS FLEX	#N/A	#N/A	#N/A
9	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-03				Applebee’s	Owner's Draw	APPLEBEES	#N/A	#N/A	#N/A
10	SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX INDN:David Jensen CO ID:1455176354 PPD	Snap Finance	Ask My Accountant		Auto Air & Vacuum	Auto and Truck Expenses	AUTO AIR & VACUUM	#N/A	#N/A	#N/A
11	CHECKCARD 0104 MCDONALD'S F38 AUBURNDALE FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	McDonalds	Owner's Draw		aynax	Other Job Related Costs	AYNAX	#N/A	#N/A	#N/A
12	BKOFAMERICA ATM 01/04 #000007878 WITHDRWL HAINES CITY HAINES CITY FL				Beefy King	Owner's Draw	BEEFY KING	#N/A	#N/A	#N/A
13	DOLLAR GENERAL 01/04 #000044661 PURCHASE 49571 HIGHWAY 27 DAVENPORT FL	Dollar General	Owner's Draw		BP	Owner's Draw	BP#	#N/A	#N/A	#N/A
14	Bridgecrest DES:DT RETAIL ID:7178605 INDN:David Jensen CO ID:2860677984 PPD	Bridgecrest			Bridgecrest	#N/A	Bridgecrest	#N/A	#N/A	#N/A
15	CHECKCARD 0104 OCBCC SOLID WASTE ORLANDO FL 55480779004200033100227 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Solid Waste	Utilities		Burger King	Owner's Draw	BURGER KING	#N/A	#N/A	#N/A
16	CHECKCARD 0104 WAWA 5207 000 ORLANDO FL 15410199004637000434115 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Wawa	Auto and Truck Expenses		Cardtronics	Owner's Draw	Cardtronics	#N/A	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=IFERROR(INDEX($E$2:$E$70,AGGREGATE(15,6,ROW($E$2:$E$70)-ROW($E$2)+1/ISNUMBER(SEARCH($G$2:$J$70,A2)),1)),"")
C2:C16	C2	=IFERROR(INDEX($F$2:$F$70,AGGREGATE(15,6,ROW($E$2:$E$70)-ROW($E$2)+1/ISNUMBER(SEARCH($G$2:$J$70,A2)),1)),"")

 

[aaewalsh]

Sure! Here is an example of how the VBA code could look like, this is a simple example based on the information you provided, the range and the columns may change depending on the data set.

Sub MatchVendorName()
    Dim rng As Range
    Dim cel As Range
    Dim search As String

    'Set the range to search
    Set rng = Range("G2:J1000")

    'Iterate through each cell in the range
    For Each cel In rng
        'Store the partial text to search for
        search = cel.Value

        'Use the Find method to search for the partial text in column A
        Set found = Columns("A:A").Find(search, LookAt:=xlPart)

        'Check if a match was found
        If Not found Is Nothing Then
            'Use the Offset method to retrieve the values from column E and F
            cel.Offset(0, 1).Value = found.Offset(0, 4).Value
            cel.Offset(0, 2).Value = found.Offset(0, 5).Value
        End If
    Next cel
End Sub

 

[Sufiyan97]

Hi Thanks for the response

Getting below error

 

[Alex Blakenburg]

The error is caused by search being dimmed as "String" but containing #N/A.

I think there are some other errors in the code so here is a modified version.

Sub MatchVendorName_mod()
    Dim rngWords As Range, celWords As Range, rowWords As Range
    Dim lastRowWords As Long
    Dim rngA As Range
    Dim lastRowA As Long
    Dim search As String
    Dim found As Range

    'Set the range to search
    lastRowWords = Range("G" & Rows.Count).End(xlUp).Row
    Set rngWords = Range("G2:J" & lastRowWords)
    lastRowA = Range("A" & Rows.Count).End(xlUp).Row
    Set rngA = Range("A2:A" & lastRowA)
   
    'Iterate through each cell in the range
    For Each rowWords In rngWords.Rows
        For Each celWords In rowWords.Cells
            'Store the partial text to search for
            search = Application.IfError(celWords.Value, "")
            If search <> "" Then
                'Use the Find method to search for the partial text in column A
                Set found = rngA.Find(search, LookAt:=xlPart, MatchCase:=False, LookIn:=xlValues)
       
                'Check if a match was found
                If Not found Is Nothing Then
                    found.Offset(0, 1).Value = Cells(celWords.Row, "E")
                    found.Offset(0, 2).Value = Application.IfError(Cells(celWords.Row, "F"), "")
                    Exit For           ' found - Move on to next row
                End If
            Else
                Exit For                ' no more words on row - Move on to next row
            End If
        Next celWords
    Next rowWords
End Sub

 

[Sufiyan97]

Hi Alex
Thank you!
This works but it gives 68 Vendor name and Accounts out of 2400+ rows of data,
while if I use the formula posted in My original post, it gives 1500+ Vendor name and accounts.

Is there a way to make a code to work same like formula.

 

[Alex Blakenburg]

Can you please show an example of one the formula found and the code didn't ?
Need to see both the left hand side and matching right hand side.

 

[Sufiyan97]

I have added formula in 2nd row so for Mercury Insurance Code does not fetch the vendor name

Book7
	A	B	C	D	E	F	G	H	I	J
1	Transaction Description	Vendor Name	Account		Vendor Name	Account	Description1	Description2	Description3	Description4
2	MERCURY INS DES:PAYMENT ID:1000514717-9306 INDN:JENSEN, DAVID CO ID:GXXXXXXXXX PPD	Mercury Insurance	Insurance Expense		7-11	Auto and Truck Expenses	7-ELEVEN	7ELEVEN	7ELE	#N/A
3	ALLSTATE INS CO DES:INS PREM ID:000000981945736 INDN:JENSEN CO ID:1360719665 PPD	Allstate Insurance	Insurance Expense		A Direct Auto Service	#N/A	A DIRECT	#N/A	#N/A	#N/A
4	CHECKCARD 1230 OLIVE GARDEN 0021 ORLANDO FL 55310208365091727000057 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Advance Auto	Other Job Related Costs	ADVANCE AUTO P	#N/A	#N/A	#N/A
5	CHECKCARD 1230 OLIVE GARDEN 000 ORLANDO FL 15410198365140493695099 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Agilis Linxup	#N/A	AGILIS	#N/A	#N/A	#N/A
6	CHECKCARD 0101 DOMINO'S 9450 407-852-9595 FL 05436849002500098516305 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Domino's	Owner's Draw		Air Vac Connection	#N/A	AIR VAC CONNECTIO	#N/A	#N/A	#N/A
7	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02				Allstate Insurance	Insurance Expense	ALLSTATE	#N/A	#N/A	#N/A
8	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02				Amazon	Owner's Draw	AMAZON.COM	AMAZON DIGITAL	#N/A	#N/A
9	CHECKCARD 0102 AGILIS LINXUP MOT 877-732-4980 MO 55432869002200773574177 CKCD 5734 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Agilis Linxup			Ambassador	#N/A	AMBASSADO	#N/A	#N/A	#N/A
10	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-03				Amtrust	#N/A	AMTRUST	#N/A	#N/A	#N/A
11	SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX INDN:David Jensen CO ID:1455176354 PPD	Snap Finance	Ask My Accountant		Annual Pass Flex	Owner's Draw	ANNUAL PASS FLEX	#N/A	#N/A	#N/A
12	CHECKCARD 0104 MCDONALD'S F38 AUBURNDALE FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	McDonalds	Owner's Draw		Anytime Fitness	#N/A	ANYTIME F	#N/A	#N/A	#N/A
13	BKOFAMERICA ATM 01/04 #000007878 WITHDRWL HAINES CITY HAINES CITY FL				Applebee’s	Owner's Draw	APPLEBEES	#N/A	#N/A	#N/A
14	DOLLAR GENERAL 01/04 #000044661 PURCHASE 49571 HIGHWAY 27 DAVENPORT FL	Dollar General	Owner's Draw		Auto Air & Vacuum	Auto and Truck Expenses	AUTO AIR & VACUUM	#N/A	#N/A	#N/A
15	Bridgecrest DES:DT RETAIL ID:7178605 INDN:David Jensen CO ID:2860677984 PPD	Bridgecrest			aynax	Other Job Related Costs	AYNAX	#N/A	#N/A	#N/A
16	CHECKCARD 0104 OCBCC SOLID WASTE ORLANDO FL 55480779004200033100227 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Solid Waste	Utilities		Beefy King	Owner's Draw	BEEFY KING	#N/A	#N/A	#N/A
17	CHECKCARD 0104 WAWA 5207 000 ORLANDO FL 15410199004637000434115 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Wawa	Auto and Truck Expenses		BP	Owner's Draw	BP#	#N/A	#N/A	#N/A
18	CHECKCARD 0104 FLOOR AND DECOR 1 SANFORD FL 55310209005207636700108 CKCD 5713 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	FLoor & Decor	Job Materials Purchased		Bridgecrest	#N/A	Bridgecrest	#N/A	#N/A	#N/A
19	CHECKCARD 0104 STAPLES 001 OVIEDO FL 15410199005105028401168 CKCD 5943 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Bright House Networks	#N/A	BRIGHT HOUSE N	#N/A	#N/A	#N/A
20	CHECKCARD 0104 DOMINO'S 9450 407-852-9595 FL 05436849005100051736458 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				BSP Petro Inc.	#N/A	BSP PETRO	#N/A	#N/A	#N/A
21	CHECKCARD 0104 FLORIDA TEAM 1000 8007040154 FL 55429509005286299800067 CKCD 6513 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Florida Team	Ask My Accountant		Burger King	Owner's Draw	BURGER KING	#N/A	#N/A	#N/A
22	CHECKCARD 0105 WORLD OF DISNEY LAKE BUENA VIFL 55310209006893006409815 CKCD 5947 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Cardtronics	Owner's Draw	Cardtronics	#N/A	#N/A	#N/A
23	CHECKCARD 0105 RFC DISNEY WORLD LAKE BUENA VIFL 55310209006206188000213 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Champions Gate	#N/A	CHAMPIONS GATE	#N/A	#N/A	#N/A
24	CHECKCARD 0105 INSTACART 8882467822 CA 55429509006637714688710 CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Checkers	Owner's Draw	CHECKERS	#N/A	#N/A	#N/A
25	CHECKCARD 0105 INSTACART 8882467822 CA 55429509006637718070196 CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Chevron	Auto and Truck Expenses	CHEVRON	#N/A	#N/A	#N/A
26	7ELEVEN-FCTI 01/06 #000123218 WITHDRWL 6070 W IRLO BRONS CELEBRATION FL				Chick-fil-A	Owner's Draw	CHICK-FIL-A	#N/A	#N/A	#N/A
27	CHECKCARD 0106 SQU*SQ *FREEZE YO Kissimmee FL 55432869006200602321002 CKCD 7299 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Circle K	Auto and Truck Expenses	CIRCLE K	#N/A	#N/A	#N/A
28	FUN SPOT FUN 01/06 #000913604 PURCHASE FUN SPOT FUN SPO KISSIMMEE FL	Fun Spot	Owner's Draw		Citgo	#N/A	Citgo	#N/A	#N/A	#N/A
29	FUN SPOT FUN 01/06 #000920799 PURCHASE FUN SPOT FUN SPO KISSIMMEE FL				Davenport	#N/A	DAVENPORT CONV	#N/A	#N/A	#N/A
30	RACETRAC 201 01/07 #000163200 PURCHASE 8890 W IRLO BRONS KISSIMMEE FL	Racetrac	Owner's Draw		DMC Motors	#N/A	DMC MOTORS	#N/A	#N/A	#N/A
31	7-ELEVEN 01/07 #000138323 PURCHASE 7-ELEVEN SANFORD FL	7-11	Auto and Truck Expenses		Dollar General	Owner's Draw	DOLLAR GENERAL	#N/A	#N/A	#N/A
32	CHECKCARD 0107 MCDONALD'S M43 CLERMONT FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Domino's	Owner's Draw	DOMINO	#N/A	#N/A	#N/A
33	CHECKCARD 0107 WM SUPERCENTER CLERMONT FL CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Duke	Utilities	DUKE-ENE	#N/A	#N/A	#N/A
34	Wire Transfer Fee				EFX	Owner's Draw	EFX	#N/A	#N/A	#N/A
35	7ELEVEN-FCTI 01/06 #000123218 WITHDRWL 6070 W IRLO BRONS CELEBRATION FL FEE CKCD XXXXXXXXXXXX3705				Exxon	Auto and Truck Expenses	EXXONMOBIL	#N/A	#N/A	#N/A
36	CHECKCARD 0106 BURGER KING #4267 KISSIMMEE FL 55431809007091943000454 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Burger King	Owner's Draw		Finnegan's Bar & Grill	#N/A	Finnegan`s	#N/A	#N/A	#N/A
37	BKOFAMERICA ATM 01/07 #000003760 WITHDRWL FOUR CORNERS CLERMONT FL				FLoor & Decor	Job Materials Purchased	FLOOR & DECOR	#N/A	#N/A	#N/A
38	NST THE HOME D 01/08 #000277101 PURCHASE 4600 W LAKE MARY LAKE MARY FL	The Home Depot	Job Materials Purchased		FLoor & Decor	Job Materials Purchased	FLOOR AND DECOR	#N/A	#N/A	#N/A
39	CHECKCARD 0107 SHELL OIL 1248182 DAVENPORT FL 55308769008547358000490 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Shell Service	Auto and Truck Expenses		Florida Team	Ask My Accountant	FLORIDA TEAM	#N/A	#N/A	#N/A
40	CHECKCARD 0107 BURGER KING #1549 CLERMONT FL 55431809008091504001155 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Fun Spot	Owner's Draw	FUN SPOT	#N/A	#N/A	#N/A
41	Online Banking transfer to CHK 7734 Confirmation# 7116802902				Google	#N/A	GOOGLE	#N/A	#N/A	#N/A
42	CHECKCARD 0108 RACETRAC 201 000 KISSIMMEE FL 15410199009974776380098 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Harbor Freight	Tools and Small Equipment	HARBOR FREIGHT	#N/A	#N/A	#N/A
43	CHECKCARD 0110 GOOGLE *iHeartMed 855-836-3987 CA 55432869010200410446904 RECURRING CKCD 5968 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Google			Hungry Howies	#N/A	HUNGRY HOWIE	#N/A	#N/A	#N/A
44	SJT GAS & FOOD 01/10 #000542587 WITHDRWL 404 US HIGHWAY 17 DAVENPORT FL	Sjt Gas & Food Inc.	Owner's Draw		Ispa	Owner's Draw	ISPA	#N/A	#N/A	#N/A
45	SJT GAS & FOOD 01/10 #000542587 WITHDRWL 404 US HIGHWAY 17 DAVENPORT FL FEE CKCD XXXXXXXXXXXX3705				Joe's Express Car Wash	Auto and Truck Expenses	JOE'S CARWASH	#N/A	#N/A	#N/A
46	SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX INDN:David Jensen CO ID:1455176354 PPD				Laundromart of Four Corners	#N/A	LAUNDROMAT	#N/A	#N/A	#N/A
47	CHECKCARD 0109 EXXONMOBIL 975 APOPKA FL 15486809011378002888776 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Exxon	Auto and Truck Expenses		Lowes	Job Materials Purchased	LOWE'S	#N/A	#N/A	#N/A
48	CHECKCARD 0109 OCBCC SOLID WASTE ORLANDO FL 55480779009200033602524 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Lowes	Job Materials Purchased	LOWE'S	#N/A	#N/A	#N/A
49	CHECKCARD 0110 CIRCLE K # 04883 DAVENPORT FL 55432869011200511432968 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Circle K	Auto and Truck Expenses		Lumber Liquidation	#N/A	LUMBER LIQUIDATOR	#N/A	#N/A	#N/A
50	CHECKCARD 0110 SQUARE *SQ *JAVSC Kissimmee FL 55432869010200482677782 CKCD 7230 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				McDonalds	Owner's Draw	MCDONALD'S	#N/A	#N/A	#N/A
51	CHECKCARD 0110 SQ *SQ *GLORIA MU Kissimmee FL 55432869010200487679817 CKCD 7230 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Mercury Insurance	Insurance Expense	MERCURY INS	#N/A	#N/A	#N/A
52	CHECKCARD 0110 SUBWAY 002 KISSIMMEE FL 15410199011255223701938 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Subway	Owner's Draw		Murphy	#N/A	Murphy	#N/A	#N/A	#N/A
53	SPEEDWAY 06658 01/11 #000673741 PURCHASE SPEEDWAY 06658 WINTER PARK FL	Speedway			Netflix	#N/A	Netflix	#N/A	#N/A	#N/A
54	NST THE HOME D 01/11 #000106701 PURCHASE 4600 W LAKE MARY LAKE MARY FL				Nutty Irishman	#N/A	THE NUTTY IRISHMA	#N/A	#N/A	#N/A
55	7-ELEVEN 01/11 #000363863 PURCHASE 7-ELEVEN SANFORD FL				OpenSky	#N/A	OPENSKY	#N/A	#N/A	#N/A
56	5/3 BK RACE TR 01/11 #000009781 WITHDRWL 8890 W IRLO BRONS KISSIMMEE FL	Racetrac	Owner's Draw		Peyto LLC	#N/A	Peyto LLC	#N/A	#N/A	#N/A
57	5/3 BK RACE TR 01/11 #000009781 WITHDRWL 8890 W IRLO BRONS KISSIMMEE FL FEE CKCD XXXXXXXXXXXX3705				Planet Fitness	Owner's Draw	PLANET FIT	#N/A	#N/A	#N/A
58	Online Banking transfer to CHK 7734 Confirmation# 5529690599				PNC	Owner's Draw	PNC BANK	#N/A	#N/A	#N/A
59	CHECKCARD 0110 RACETRAC 201 000 KISSIMMEE FL 15410199011974776380110 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705				Polk County	Business Licenses and Permits	POLK COUNTY UTILI	#N/A	#N/A	#N/A
60	CHECKCARD 0111 STAPLES 001 LAKE MARY FL 15410199012105026329727 CKCD 5943				Prime Rate	#N/A	PRIME RATE	#N/A	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IFERROR(INDEX($E$2:$E$1000,AGGREGATE(15,6,ROW($E$2:$E$1000)-ROW($E$2)+1/ISNUMBER(SEARCH($G$2:$J$1000,A2)),1)),"")
C2	C2	=IFERROR(INDEX($F$2:$F$1000,AGGREGATE(15,6,ROW($E$2:$E$1000)-ROW($E$2)+1/ISNUMBER(SEARCH($G$2:$J$1000,A2)),1)),"")

 

[Alex Blakenburg]

I don't understand both the formula and macro get the Mercury information.
What is an example of where they behave differently ?

 

[Sufiyan97]

I don't understand both the formula and macro get the Mercury information.
What is an example of where they behave differently ?

When I run the code, I do not get result for Mercury Insurance as vendor


Here is the result when I run the code

Column B and C have formula results and
C & D have code results

Category Formula Practice.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Transaction Description	Vendor Name	Account	Vendor Name	Account		Vendor Name	Account	Description1	Description2	Description3	Description4
2	MERCURY INS DES:PAYMENT ID:1000514717-9306 INDN:JENSEN, DAVID CO ID:GXXXXXXXXX PPD	Mercury Insurance	Insurance Expense				7-11	Auto and Truck Expenses	7-ELEVEN	7ELEVEN	7ELE	#N/A
3	ALLSTATE INS CO DES:INS PREM ID:000000981945736 INDN:JENSEN CO ID:1360719665 PPD	Allstate Insurance	Insurance Expense	Allstate Insurance	Insurance Expense		A Direct Auto Service	#N/A	A DIRECT	#N/A	#N/A	#N/A
4	CHECKCARD 1230 OLIVE GARDEN 0021 ORLANDO FL 55310208365091727000057 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Advance Auto	Other Job Related Costs	ADVANCE AUTO P	#N/A	#N/A	#N/A
5	CHECKCARD 1230 OLIVE GARDEN 000 ORLANDO FL 15410198365140493695099 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Agilis Linxup	#N/A	AGILIS	#N/A	#N/A	#N/A
6	CHECKCARD 0101 DOMINO'S 9450 407-852-9595 FL 05436849002500098516305 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Domino's	Owner's Draw	Domino's	Owner's Draw		Air Vac Connection	#N/A	AIR VAC CONNECTIO	#N/A	#N/A	#N/A
7	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02						Allstate Insurance	Insurance Expense	ALLSTATE	#N/A	#N/A	#N/A
8	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-02						Amazon	Owner's Draw	AMAZON.COM	AMAZON DIGITAL	#N/A	#N/A
9	CHECKCARD 0102 AGILIS LINXUP MOT 877-732-4980 MO 55432869002200773574177 CKCD 5734 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Agilis Linxup		Agilis Linxup			Ambassador	#N/A	AMBASSADO	#N/A	#N/A	#N/A
10	OVERDRAFT ITEM FEE FOR ACTIVITY OF 01-03						Amtrust	#N/A	AMTRUST	#N/A	#N/A	#N/A
11	SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX INDN:David Jensen CO ID:1455176354 PPD	Snap Finance	Ask My Accountant	Snap Finance	Ask My Accountant		Annual Pass Flex	Owner's Draw	ANNUAL PASS FLEX	#N/A	#N/A	#N/A
12	CHECKCARD 0104 MCDONALD'S F38 AUBURNDALE FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	McDonalds	Owner's Draw	McDonalds	Owner's Draw		Anytime Fitness	#N/A	ANYTIME F	#N/A	#N/A	#N/A
13	BKOFAMERICA ATM 01/04 #000007878 WITHDRWL HAINES CITY HAINES CITY FL						Applebee’s	Owner's Draw	APPLEBEES	#N/A	#N/A	#N/A
14	DOLLAR GENERAL 01/04 #000044661 PURCHASE 49571 HIGHWAY 27 DAVENPORT FL	Dollar General	Owner's Draw	Dollar General	Owner's Draw		Auto Air & Vacuum	Auto and Truck Expenses	AUTO AIR & VACUUM	#N/A	#N/A	#N/A
15	Bridgecrest DES:DT RETAIL ID:7178605 INDN:David Jensen CO ID:2860677984 PPD	Bridgecrest		Bridgecrest			aynax	Other Job Related Costs	AYNAX	#N/A	#N/A	#N/A
16	CHECKCARD 0104 OCBCC SOLID WASTE ORLANDO FL 55480779004200033100227 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Solid Waste	Utilities	Solid Waste	Utilities		Beefy King	Owner's Draw	BEEFY KING	#N/A	#N/A	#N/A
17	CHECKCARD 0104 WAWA 5207 000 ORLANDO FL 15410199004637000434115 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Wawa	Auto and Truck Expenses	Wawa	Auto and Truck Expenses		BP	Owner's Draw	BP#	#N/A	#N/A	#N/A
18	CHECKCARD 0104 FLOOR AND DECOR 1 SANFORD FL 55310209005207636700108 CKCD 5713 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	FLoor & Decor	Job Materials Purchased	FLoor & Decor	Job Materials Purchased		Bridgecrest	#N/A	Bridgecrest	#N/A	#N/A	#N/A
19	CHECKCARD 0104 STAPLES 001 OVIEDO FL 15410199005105028401168 CKCD 5943 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Bright House Networks	#N/A	BRIGHT HOUSE N	#N/A	#N/A	#N/A
20	CHECKCARD 0104 DOMINO'S 9450 407-852-9595 FL 05436849005100051736458 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Domino's	Owner's Draw				BSP Petro Inc.	#N/A	BSP PETRO	#N/A	#N/A	#N/A
21	CHECKCARD 0104 FLORIDA TEAM 1000 8007040154 FL 55429509005286299800067 CKCD 6513 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Florida Team	Ask My Accountant	Florida Team	Ask My Accountant		Burger King	Owner's Draw	BURGER KING	#N/A	#N/A	#N/A
22	CHECKCARD 0105 WORLD OF DISNEY LAKE BUENA VIFL 55310209006893006409815 CKCD 5947 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Cardtronics	Owner's Draw	Cardtronics	#N/A	#N/A	#N/A
23	CHECKCARD 0105 RFC DISNEY WORLD LAKE BUENA VIFL 55310209006206188000213 CKCD 5812 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Champions Gate	#N/A	CHAMPIONS GATE	#N/A	#N/A	#N/A
24	CHECKCARD 0105 INSTACART 8882467822 CA 55429509006637714688710 CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Checkers	Owner's Draw	CHECKERS	#N/A	#N/A	#N/A
25	CHECKCARD 0105 INSTACART 8882467822 CA 55429509006637718070196 CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Chevron	Auto and Truck Expenses	CHEVRON	#N/A	#N/A	#N/A
26	7ELEVEN-FCTI 01/06 #000123218 WITHDRWL 6070 W IRLO BRONS CELEBRATION FL	7-11	Auto and Truck Expenses				Chick-fil-A	Owner's Draw	CHICK-FIL-A	#N/A	#N/A	#N/A
27	CHECKCARD 0106 SQU*SQ *FREEZE YO Kissimmee FL 55432869006200602321002 CKCD 7299 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Circle K	Auto and Truck Expenses	CIRCLE K	#N/A	#N/A	#N/A
28	FUN SPOT FUN 01/06 #000913604 PURCHASE FUN SPOT FUN SPO KISSIMMEE FL	Fun Spot	Owner's Draw	Fun Spot	Owner's Draw		Citgo	#N/A	Citgo	#N/A	#N/A	#N/A
29	FUN SPOT FUN 01/06 #000920799 PURCHASE FUN SPOT FUN SPO KISSIMMEE FL	Fun Spot	Owner's Draw				Davenport	#N/A	DAVENPORT CONV	#N/A	#N/A	#N/A
30	RACETRAC 201 01/07 #000163200 PURCHASE 8890 W IRLO BRONS KISSIMMEE FL	Racetrac	Owner's Draw	Racetrac	Owner's Draw		DMC Motors	#N/A	DMC MOTORS	#N/A	#N/A	#N/A
31	7-ELEVEN 01/07 #000138323 PURCHASE 7-ELEVEN SANFORD FL	7-11	Auto and Truck Expenses	7-11	Auto and Truck Expenses		Dollar General	Owner's Draw	DOLLAR GENERAL	#N/A	#N/A	#N/A
32	CHECKCARD 0107 MCDONALD'S M43 CLERMONT FL CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	McDonalds	Owner's Draw				Domino's	Owner's Draw	DOMINO	#N/A	#N/A	#N/A
33	CHECKCARD 0107 WM SUPERCENTER CLERMONT FL CKCD 5411 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Duke	Utilities	DUKE-ENE	#N/A	#N/A	#N/A
34	Wire Transfer Fee						EFX	Owner's Draw	EFX	#N/A	#N/A	#N/A
35	7ELEVEN-FCTI 01/06 #000123218 WITHDRWL 6070 W IRLO BRONS CELEBRATION FL FEE CKCD XXXXXXXXXXXX3705	7-11	Auto and Truck Expenses				Exxon	Auto and Truck Expenses	EXXONMOBIL	#N/A	#N/A	#N/A
36	CHECKCARD 0106 BURGER KING #4267 KISSIMMEE FL 55431809007091943000454 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Burger King	Owner's Draw	Burger King	Owner's Draw		Finnegan's Bar & Grill	#N/A	Finnegan`s	#N/A	#N/A	#N/A
37	BKOFAMERICA ATM 01/07 #000003760 WITHDRWL FOUR CORNERS CLERMONT FL						FLoor & Decor	Job Materials Purchased	FLOOR & DECOR	#N/A	#N/A	#N/A
38	NST THE HOME D 01/08 #000277101 PURCHASE 4600 W LAKE MARY LAKE MARY FL	The Home Depot	Job Materials Purchased	The Home Depot	Job Materials Purchased		FLoor & Decor	Job Materials Purchased	FLOOR AND DECOR	#N/A	#N/A	#N/A
39	CHECKCARD 0107 SHELL OIL 1248182 DAVENPORT FL 55308769008547358000490 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Shell Service	Auto and Truck Expenses	Shell Service	Auto and Truck Expenses		Florida Team	Ask My Accountant	FLORIDA TEAM	#N/A	#N/A	#N/A
40	CHECKCARD 0107 BURGER KING #1549 CLERMONT FL 55431809008091504001155 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Burger King	Owner's Draw				Fun Spot	Owner's Draw	FUN SPOT	#N/A	#N/A	#N/A
41	Online Banking transfer to CHK 7734 Confirmation# 7116802902						Google	#N/A	GOOGLE	#N/A	#N/A	#N/A
42	CHECKCARD 0108 RACETRAC 201 000 KISSIMMEE FL 15410199009974776380098 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Racetrac	Owner's Draw				Harbor Freight	Tools and Small Equipment	HARBOR FREIGHT	#N/A	#N/A	#N/A
43	CHECKCARD 0110 GOOGLE *iHeartMed 855-836-3987 CA 55432869010200410446904 RECURRING CKCD 5968 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Google		Google			Hungry Howies	#N/A	HUNGRY HOWIE	#N/A	#N/A	#N/A
44	SJT GAS & FOOD 01/10 #000542587 WITHDRWL 404 US HIGHWAY 17 DAVENPORT FL	Sjt Gas & Food Inc.	Owner's Draw	Sjt Gas & Food Inc.	Owner's Draw		Ispa	Owner's Draw	ISPA	#N/A	#N/A	#N/A
45	SJT GAS & FOOD 01/10 #000542587 WITHDRWL 404 US HIGHWAY 17 DAVENPORT FL FEE CKCD XXXXXXXXXXXX3705	Sjt Gas & Food Inc.	Owner's Draw				Joe's Express Car Wash	Auto and Truck Expenses	JOE'S CARWASH	#N/A	#N/A	#N/A
46	SNAP FINANCE DES:PAYMENT ID:PXXXXXXXXX INDN:David Jensen CO ID:1455176354 PPD	Snap Finance	Ask My Accountant				Laundromart of Four Corners	#N/A	LAUNDROMAT	#N/A	#N/A	#N/A
47	CHECKCARD 0109 EXXONMOBIL 975 APOPKA FL 15486809011378002888776 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Exxon	Auto and Truck Expenses	Exxon	Auto and Truck Expenses		Lowes	Job Materials Purchased	LOWE'S	#N/A	#N/A	#N/A
48	CHECKCARD 0109 OCBCC SOLID WASTE ORLANDO FL 55480779009200033602524 CKCD 4900 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Solid Waste	Utilities				Lowes	Job Materials Purchased	LOWE'S	#N/A	#N/A	#N/A
49	CHECKCARD 0110 CIRCLE K # 04883 DAVENPORT FL 55432869011200511432968 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Circle K	Auto and Truck Expenses	Circle K	Auto and Truck Expenses		Lumber Liquidation	#N/A	LUMBER LIQUIDATOR	#N/A	#N/A	#N/A
50	CHECKCARD 0110 SQUARE *SQ *JAVSC Kissimmee FL 55432869010200482677782 CKCD 7230 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						McDonalds	Owner's Draw	MCDONALD'S	#N/A	#N/A	#N/A
51	CHECKCARD 0110 SQ *SQ *GLORIA MU Kissimmee FL 55432869010200487679817 CKCD 7230 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705						Mercury Insurance	Insurance Expense	MERCURY INS	#N/A	#N/A	#N/A
52	CHECKCARD 0110 SUBWAY 002 KISSIMMEE FL 15410199011255223701938 CKCD 5814 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Subway	Owner's Draw	Subway	Owner's Draw		Murphy	#N/A	Murphy	#N/A	#N/A	#N/A
53	SPEEDWAY 06658 01/11 #000673741 PURCHASE SPEEDWAY 06658 WINTER PARK FL	Speedway		Speedway			Netflix	#N/A	Netflix	#N/A	#N/A	#N/A
54	NST THE HOME D 01/11 #000106701 PURCHASE 4600 W LAKE MARY LAKE MARY FL	The Home Depot	Job Materials Purchased				Nutty Irishman	#N/A	THE NUTTY IRISHMA	#N/A	#N/A	#N/A
55	7-ELEVEN 01/11 #000363863 PURCHASE 7-ELEVEN SANFORD FL	7-11	Auto and Truck Expenses				OpenSky	#N/A	OPENSKY	#N/A	#N/A	#N/A
56	5/3 BK RACE TR 01/11 #000009781 WITHDRWL 8890 W IRLO BRONS KISSIMMEE FL	Racetrac	Owner's Draw	Racetrac	Owner's Draw		Peyto LLC	#N/A	Peyto LLC	#N/A	#N/A	#N/A
57	5/3 BK RACE TR 01/11 #000009781 WITHDRWL 8890 W IRLO BRONS KISSIMMEE FL FEE CKCD XXXXXXXXXXXX3705	Racetrac	Owner's Draw				Planet Fitness	Owner's Draw	PLANET FIT	#N/A	#N/A	#N/A
58	Online Banking transfer to CHK 7734 Confirmation# 5529690599						PNC	Owner's Draw	PNC BANK	#N/A	#N/A	#N/A
59	CHECKCARD 0110 RACETRAC 201 000 KISSIMMEE FL 15410199011974776380110 CKCD 5542 XXXXXXXXXXXX3705 XXXX XXXX XXXX 3705	Racetrac	Owner's Draw				Polk County	Business Licenses and Permits	POLK COUNTY UTILI	#N/A	#N/A	#N/A
60	CHECKCARD 0111 STAPLES 001 LAKE MARY FL 15410199012105026329727 CKCD 5943						Prime Rate	#N/A	PRIME RATE	#N/A	#N/A	#N/A
Durfani Creations

Cell Formulas
Range	Formula
B2:B60	B2	=IFERROR(INDEX($G$2:$G$1000,AGGREGATE(15,6,ROW($G$2:$G$1000)-ROW(GE$2)+1/ISNUMBER(SEARCH($I$2:$L$1000,A2)),1)),"")
C2:C60	C2	=IFERROR(INDEX($H$2:$H$1000,AGGREGATE(15,6,ROW($G$2:$G$1000)-ROW(GE$2)+1/ISNUMBER(SEARCH($I$2:$L$1000,A2)),1)),"")

 

[Alex Blakenburg]

Can you see if this fixes the issue ?

Sub MatchVendorName_mod()
    Dim rngWords As Range, celWords As Range, rowWords As Range
    Dim lastRowWords As Long
    Dim rngA As Range
    Dim lastRowA As Long
    Dim search As String
    Dim found As Range
    Dim foundAddr1 As String

    'Set the range to search
    lastRowWords = Range("G" & Rows.Count).End(xlUp).Row
    Set rngWords = Range("G2:J" & lastRowWords)
    lastRowA = Range("A" & Rows.Count).End(xlUp).Row
    Set rngA = Range("A2:A" & lastRowA)
    
    'Iterate through each cell in the range
    For Each rowWords In rngWords.Rows
        For Each celWords In rowWords.Cells
            'Store the partial text to search for
            search = Application.IfError(celWords.Value, "")
            If search <> "" Then
                'Use the Find method to search for the partial text in column A
                Set found = rngA.Find(search, LookAt:=xlPart, MatchCase:=False, LookIn:=xlValues)
        
                'Check if a match was found
                If Not found Is Nothing Then
                    foundAddr1 = found.Address
                    Do
                        found.Offset(0, 1).Value = Cells(celWords.Row, "E")
                        found.Offset(0, 2).Value = Application.IfError(Cells(celWords.Row, "F"), "")
                        Set found = rngA.FindNext(After:=found)
                    Loop Until found.Address = foundAddr1
                End If
            Else
                Exit For                ' no more words on row - Move on to next row
            End If
        Next celWords
    Next rowWords
End Sub