# Find a range of matching cells

1. ## 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

2. ## Re: Find a range of matching cells

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

3. ## Re: Find a range of matching cells

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?

4. ## Re: Find a range of matching cells

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!

5. ## Re: Find a range of matching cells

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?

6. ## Re: Find a range of matching cells

Compare:

``Please Login or Register  to view this content.``

7. ## Re: Find a range of matching cells

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.``

8. ## Re: Find a range of matching cells

Perhaps:
``Please Login or Register  to view this content.``

9. ## Re: Find a range of matching cells

Compare:

``Please Login or Register  to view this content.``

Not sure I'm following you on this! What is this doing and where would it go?

10. ## Re: Find a range of matching cells

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

11. ## Re: Find a range of matching cells

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!!

12. ## Re: Find a range of matching cells

I think we need a sample - here's how:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

13. ## Re: Find a range of matching cells

... 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!!
I'm confused. Do you want Gotcha to display the total number of rows matched (which is just SUM(S1:S5))?
Or do you want it to actually show the matched rows/values?

14. ## Re: Find a range of matching cells

This procedure copies the matched Check Rows to Textbox Gotcha, and unmatched Check Rows to Textbox Nope!!:
``Please Login or Register  to view this content.``

15. ## Re: Find a range of matching cells

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

16. ## Re: Find a range of matching cells

This seems to have worked:

``Please Login or Register  to view this content.``

17. ## Re: Find a range of matching cells

This seems to have worked:

``Please Login or Register  to view this content.``

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!!

18. ## Re: Find a range of matching cells

...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...
This has been adjusted to fit your new layout. It counts any row in M:R with one or more matches. (Post #19 offers a version that counts every individual match.) If you no longer need values in column S, select the line in red and delete it:
``Please Login or Register  to view this content.``

19. ## Re: Find a range of matching cells

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.``

20. ## Re: Find a range of matching cells

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!!

21. ## Re: Find a range of matching cells

Clicking the Add Reputation star below helpful posts is a nice way to show appreciation to those who contributed. Regards - Lee

22. ## Re: Find a range of matching cells

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

23. ## Re: Find a range of matching cells

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)

24. ## Re: Find a range of matching cells

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

25. ## Re: Find a range of matching cells

``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

26. ## Re: Find a range of matching cells

Originally Posted by daboho
``Please Login or Register  to view this content.``
...Is same as Join(array([b2],[c2]...[g2]),"~")
I want to know what for to transpose before join...
VBA's Join function requires a 1-dimensional "horizontal" array, but Excel always passes cell values in a 2-dimensional array, even if the source is a single column or row. Transposing such an array is one way to convert it to a SINGLE DIMENSION, but this one was already horizontal, so it had to be transposed a second time to work.

Notice that I switched to a different (more efficient) method in my later code:
``Please Login or Register  to view this content.``
I had tried this approach initially without success because I forgot to include the .Value property. My thanks to @xladept for jogging my memory.

Here's a little demo:
``Please Login or Register  to view this content.``

27. ## Re: Find a range of matching cells

... Is there a simple way to have a message box display the rows that match exactly?...
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.``

28. ## Re: Find a range of matching cells

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!

29. ## Re: Find a range of matching cells

Not exactly; looking for any matching rows in my original data, A1:F to the end of data; just looking for double entries!
Please refer to post#27. I've highlighted the sections that build the string of matched row numbers (both A-F and M-R) shown in the final message. Do you want the actual values, rather than row numbers?

30. ## Re: Find a range of matching cells

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

31. ## Re: Find a range of matching cells

Hey, just cleared up a performance bottleneck. This version processed 10000 test rows (1450 matched) in 0.21 seconds:
``Please Login or Register  to view this content.``
PS Updated 24 Jan 2018 06:42 - added variable DupeCnt to correct the "matched" total

Ok thank you

33. ## Re: Find a range of matching cells

OK, I swear this is the last change. I realized my math was off for Gotcha and Nope. [dc.Count] tells us how many unique patterns there are... but some of those were later duplicated, and must be deducted from [dc.Count] to yield the correct Nope (the number of unduplicated rows). I was already including the relevant rows in the report, just not counting them correctly.

Please see post #31 for the NEW code. Rather than post yet another version, I just updated the code there.

34. ## Re: Find a range of matching cells

https://www.excelforum.com/excel-pro...ml#post4828005

35. ## Re: Find a range of matching cells

Originally Posted by leelnich
OK, I swear this is the last change. I realized my math was off for Gotcha and Nope. [dc.Count] tells us how many unique patterns there are... but some of those were later duplicated, and must be deducted from [dc.Count] to yield the correct Nope (the number of unduplicated rows). I was already including the relevant rows in the report, just not counting them correctly.

Please see post #31 for the NEW code. Rather than post yet another version, I just updated the code there.
Ok, small issue! I have a total of 2108 rows of data as of now but Nope comes up with 2113?? Sorry, thought you had it!!

36. ## Re: Find a range of matching cells

Ok, small issue! I have a total of 2108 rows of data as of now but Nope comes up with 2113?? Sorry, thought you had it!!
I'm guessing 2108 unique rows A:F PLUS 5 unique rows M:R EQUALS 2113 un-duplicated rows. Is that not correct?

37. ## Re: Find a range of matching cells

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!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1