This may sound ridiculously complicated, but here a stab.
Looking for a practical/proficient process to identify matches between data in two sheet columns. For example lets say I have Sheet 1 with columns A and B and 300,000 rows.


Ref# Folder Path

1 C:\Folder 1\Test.png
...

243 C:\Folder 2\Subfolder 1\Testing.jpg

... .....

300000 E:\Folder1\SubFolder2\Test.doc





I have Sheet 2 with 5 columns and 100 rows




SN Type Title Date Name

1-23 A Dogs 1//1/25 Test.doc
....

35-456 A Cats 1/2/24 Test.png

...
100-D-3 B Pigs 1/4/20 Testing.jpg


I need to create a third sheet that gets the data from Sheet 1 Column A where the data in Sheet 2 Column 5 "Name" is "found" in Sheet 1 Column B.

So in the example. Sheet 3 would look like this:



A
1 35-456 Cats 1/2/24 Test.png
243 100-D-3 Pigs 1/4/20 Testing.jpg
300000 1-23 Dogs 1/1/25 Test.doc


My initial process was like this
Loop through each row in Sheet 1 (300,000)
Loop through each row in Sheet 2 (100)
Check InStr(Sheet1.Cell, Sheet2,Cell) > 1
Yes we have a match, develop Sheet3 row
This process takes about a minute to run.


I revised the process to:
Create a collection

Loop through each row in Sheet 2 (100)
Check if Sheet2.Column 5 is Found in Sheet1
Yes
For Each find
Add row index to collection
For Each Index in Collection
Develop Sheet3 row
This process takes about 12 seconds.


Either method is working, but the potential exists for the row counts in sheet 1 or sheet 2 to grow considerably and the only result is longer processing time.


Wondering if there is a better way. I know nothing about Power Query and very little about Excel. Hoping some expert knows a more efficient process to minimize looping to identify all row indexes in sheet 1 that requires processing?


Thanks


cross posted at Identify Matches between Sheet Columns

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/