Finding Rows with Multiple Cell Matches Between Two Data Sets

do you need any reference on sheet 3 to the row in sheet one that generated the result otherwise the output doesnt seem to tell you a lot?

Jmac

No worries, I admit to using some of these posts on the forum as a means of forcing myself to learn new things as well as helping other users, it is a form of repayment for previous help i have received)

Your reply suggests that the basic logic is the same, we just need to add some more data in the output (this is before I look at your second attachment).

One question, will the always be names in each row always be present until the first "null" cell is found, ie is the situation where col a = name, col b = null, col c = name valid or will the first null col = finished this row??

Jmac

Hi
just to confirm I have your requiremetns understood, why is new project 4 (wheat crossovers) not included in the output report as it seems old project 6 (wheat crops in middle america) seem to have romero & graves in common??

Or have I missed an important point??

Jmac

Try the attached. It works for the example workbook, but I did not extensively test it with a larger set of data.

AgroData_Solution.xlsm

Subject to both of us finding a potential report output line that you didnt I think this example will also produce the results you are looking for.

I haven't look closely at solution from millz (it does produce the same results) but I will learn from it as it looks a more elegant solution for an experienced VBA coder. I am only learning myself so I often take a slow methodical long route to the solution. In my defence I am not a current professional software developer

jmac

Originally Posted by jmac1947

but I will learn from it as it looks a more elegant solution

As long as it works, it is still a working solution. That was actually what I was trying to avoid - having too many loops, since user mentioned that there are about 3000 * 30000 rows, and each row have individual names to look through as well.

I learned something from your solution too never knew about GoSub and Return combination.

Hi Everyone,

I'm familiar using formulas, but I have a new challenge that I think might be better suited to VBA, which I've just started learning. Here's the situation.

On Sheet1, I have a data set 15 columns wide, with 3,000 rows down.
On Sheet2, I have a data set 15 columns wide, with 30,000 rows down.

The goal is:

A) Find all the rows in Sheet1 that have two or more cell matches with any row on Sheet2, and
B) Display the matches, row by row on Sheet3.

A (tiny) mock-up (with randomly generated names) is attached. Hoping someone can help.

Hi Jmac,

Nice catch. I totally missed that. Yes, that also should have been included in the report.

It's getting late here, so I'll stop back in and check this thread tomorrow after work.

Actually, it's more helpful to have code from both an experienced coder and from someone a bit newer to things. That gives me an opportunity to compare and contrast, and learn. It's actually a plus.

Thanks so much for your help. Honestly, it's going to take some time for me to digest the code, since I'm still new to VBA, but I'll go ahead and mark this as solved.

Cheers