p45cal,

Yes, it was past my bedtime. Thanks for hanging in here! Well, as I said, it is complicated. In a sense the answer to your question is yes.
My first approach to achieve the required out put was to loop through every row of the RS_SHEET

So when we hit row 299 we have: "Sentinel Overlay Demo Data.zip//Sentinel Overlay Demo Data/DemoData_0000015_Import.zip//X Emails/Email 3.pdf"
Then I looped through every row of NDS_SHEET and checked to see if the data in column 8 was InStr of the last step
So when we hit row 24, we have "DemoData_0000015_Import.zip"
If InStr("Sentinel Overlay Demo Data.zip//Sentinel Overlay Demo Data/DemoData_0000015_Import.zip//X Emails/Email 3.pdf", "DemoData_0000015_Import.zip") > 0
Bingo, we have first match. record it.
 If Use First Match Only then
get out
Else
We continue looping
We hit row 25 which again is "DemoData_0000015_Import.zip" and again is InStr so we record the line 25 record

So if you change NDS_SHEET row 25 column 8 from "DemoData_0000015_Import.zip" to "Email 3.pdf" and run another overlay, you will see the same result for a normal overlay. If you run it with the Duplicate Reference Row you will see a different result:


That required a lot of looping. With some actual data with 440,000 RS_SHEET rows it takes about 45 seconds.

My current approach (what you see) works like this:

I loop through each data row of the NDS_SHEET (Rows 5 to 26 rows)
In each loop, I find any RS_SHEET Row index(es) where the NDS_SHEET row index, column 8 data is found and add to a collection
So when NDS_SHEET row index = 24 the RS_SHEET Row 299 is the collection item
I then Loop through the collection (In this case only 1 item)
I add 299 to another DupCollection. If successful , I add the data for row 299 to the Overlay
When NDS_SHEET row index = 25 RS_SHEET Row 299 is again the only index returned
I attempt to add it to the DupCollection and it fails
If Match First Only - Get Out
If Duplicate Ref Row - Record it

With the actual data this process takes about 12 seconds.

I hope that makes sense. Thank you again.






Example.jpg