Find a range of matching cells

Good morning/afternoon everyone!

How can I find a range of cells, say A?:G?, that exactly match another range of cells, say M?:R? Also repeat process to the end of the data in rows A:G!

Lets say I have in A2:G2 I have 1,2,3,4,5,6,7 respectively and if M12:R12 match exactly Count it as an occurrence then check the next row A3:G3 for the same until the end of data in the A to G data, incrementing the count each time they match exactly. Then have it loop back and do the same search using M13:R13 thru M16:R16 counting each time the data in the M:R range matches exactly to the A:G range. Each Count of exact matching M:R should have it's own count cell, S1 to S5.
Like if any of the A:G row 2 = M:R row 2 match exactly then Cell S1 would increment to 1 and keep incrementing until the end of data in A:G. At that point have it satrt again at A2:G2 and match M13:R13 doing the same except the count cell would be S2 repeating this sequence until it reaches the the end of data in M16:R16.

Any help would be greatly appreciated!!

I will try to clarify more if needed, if I can lol!!

TIA

A2:G2 is seven cells; M12:R12 is six cells. How can they match exactly?

Let's see if I've got this. You want to compare A2:G2, A3:G3, A4:G4, etc. with L12:R12 and log the number of matches in S1.
Then you want to compare A2:G2, A3:G3, A4:G4, etc. with L13:R13 and log the number of matches in S2.
Then the same for L14:R14, logging in S3.
Then the same for L15:R15, logging in S4.
And finally the same for L16:R16, logging in S5.

Is that it?

Originally Posted by Alan Beban

A2:G2 is seven cells; M12:R12 is six cells. How can they match exactly?

Sorry, I did notice that after going thru the code one more time!! Changed A2 to B2!

Originally Posted by Alan Beban

Let's see if I've got this. You want to compare A2:G2, A3:G3, A4:G4, etc. with L12:R12 and log the number of matches in S1.
Then you want to compare A2:G2, A3:G3, A4:G4, etc. with L13:R13 and log the number of matches in S2.
Then the same for L14:R14, logging in S3.
Then the same for L15:R15, logging in S4.
And finally the same for L16:R16, logging in S5.

Is that it?

Yes sir but with B2 instead of A2 lol! Sorry about the bad info!!

OK. I used A2:G11 and L12:R16. If you paste the Sub and Function procedure into a module in your relevant workbook and run
the Sub procedure, it will produce your desired results. At least it did for me. If you used B2 instead of A2, and I assume M12:R16, you'll have to edit the Sub procedure accordingly--also to have it apply to the appropriate number of rows for the A2/B2:Gsomething range. The required edits apply only to the Sub procedure--the Function you can treat as a black box.

Please Login or Register  to view this content.

I am a newbie at this and know just enough to be dangerous lol!!

Alan and leelnich,

In my haste, I forgot to mention to have them keep track of the ones that do repeat and those that don't! I then have the ones that do "count total" display in a textbox called Gotcha and those that don't in textbox Nope!! My apologies!!

Ok, I've uploaded a sample with basically no code! The form should load when the book is opened. I also put a command button to open it on the worksheet.
The Clear button isn't an issue, I can deal with that. The Get It button is supposed to compare the data in A2:F2, just for this workbook, with the data
in M12:R12 thru M16:R16. If there is an exact match then increment a variable "yes" or "no" if it doesn't. At the end of the process, it should show the
total number of "yes" in the Gotcha textbox and the total of "no" in the Nope textbox!

TIA

Originally Posted by xladept

This seems to have worked:

Please Login or Register  to view this content.

Xladept,

It looks like it's doing something to the S column in my Find file that I uploaded but not exactly what I need since I've tweeked it a little with the form. Check my above entry and it explains what I am looking to get put into the textboxes! But definitely thanks for what you have gotten!!

Originally Posted by leelnich

Here's a version that counts individual matches rather than rows in M:R with one or more matches:

Please Login or Register  to view this content.

leelnich,

You got it, thank you so much!! You people rock!!

Just for my own information, if I were to change

Please Login or Register  to view this content.

, would that do the same thing for that range or would I need something else? basically looking to see if there are repeats of rows in my data A1:F! TIA

Originally Posted by xladept

Looks like it would give at least the rows count any excess would be repeated rows:

Please Login or Register  to view this content.

(I think)

Thanks xladept! Is there a simple way to have a message box display the rows that match exactly? Not a big deal if not, just curious! TIA

Leelnich please what mean

Please Login or Register  to view this content.

Exampe in b2:g2.value ={1:6}
Is same as Join(array([b2],[c2]...[g2]),"~")
I want to know what for to transpose before join
Application.transpose(rows(2) what for to transpose before join i hope you talk me thank before

Originally Posted by leelnich

This uses a dictionary object to spot and track matches, building a string of row numbers for output message. Is that what you wanted?:

Please Login or Register  to view this content.

Not exactly; looking for any matching rows in my original data, A1:F to the end of data; just looking for double entries! Let me know if this helps!

Actually I misinterpreted what you did!! My apologies, that's perfect, thanks!!

Ok thank you

please help with compare but different problem
https://www.excelforum.com/excel-pro...ml#post4828005

Originally Posted by leelnich

I'm guessing 2108 unique rows A:F PLUS 5 unique rows M:R EQUALS 2113 un-duplicated rows. Is that not correct?

You're right, I didn't think about the M:R lines, thanks for clearing that up for this old guy lol!!