+ Reply to Thread
Results 1 to 37 of 37

Find a range of matching cells

  1. #1
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    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. #2
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    Re: Find a range of matching cells

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

  3. #3
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    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. #4
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by Alan Beban View Post
    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. #5
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by Alan Beban View Post
    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!!

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Find a range of matching cells

    Compare:

    Please Login or Register  to view this content.
    Last edited by xladept; 01-18-2018 at 05:28 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  7. #7
    Registered User
    Join Date
    08-27-2017
    Location
    Modesto, CA
    MS-Off Ver
    Microsoft Office Excel 2003
    Posts
    91

    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. #8
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Find a range of matching cells

    Perhaps:
    Please Login or Register  to view this content.
    Last edited by leelnich; 01-18-2018 at 06:43 PM.
    Clicking the Add Reputation star below any helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion. (Thread Tools above Post # 1) Thanks!-Lee

  9. #9
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by xladept View Post
    Compare:

    Please Login or Register  to view this content.
    xladept,

    Not sure I'm following you on this! What is this doing and where would it go?
    Last edited by jarhead58; 01-19-2018 at 12:10 PM.

  10. #10
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

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

  11. #11
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    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. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Find a range of matching cells

    Hi jarhead,

    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. #13
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Find a range of matching cells

    Quote Originally Posted by jarhead58 View Post
    ... 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?
    Last edited by leelnich; 01-19-2018 at 02:50 PM.

  14. #14
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    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.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    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
    Attached Files Attached Files

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Find a range of matching cells

    This seems to have worked:

    Please Login or Register  to view this content.
    Last edited by xladept; 01-19-2018 at 05:02 PM.

  17. #17
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by xladept View Post
    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!!

  18. #18
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Find a range of matching cells

    Quote Originally Posted by jarhead58 View Post
    ...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.
    Attached Files Attached Files
    Last edited by leelnich; 01-19-2018 at 07:02 PM.

  19. #19
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    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.
    Last edited by leelnich; 01-19-2018 at 06:55 PM.

  20. #20
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by leelnich View Post
    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. #21
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Find a range of matching cells

    Excellent, glad we could help! If that concludes your thread, please mark it as SOLVED (Thread Tools, up top). And since you're new...
    Clicking the Add Reputation star below helpful posts is a nice way to show appreciation to those who contributed. Regards - Lee
    Last edited by leelnich; 01-22-2018 at 04:42 PM.

  22. #22
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    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. #23
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    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. #24
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by xladept View Post
    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. #25
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,239

    Re: Find a range of matching cells

    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

  26. #26
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Find a range of matching cells

    Quote Originally Posted by daboho View Post
    Leelnich please what mean
    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.
    Last edited by leelnich; 01-24-2018 at 12:15 AM.

  27. #27
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Find a range of matching cells

    Quote Originally Posted by jarhead58 View Post
    ... 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.
    Last edited by leelnich; 01-23-2018 at 08:09 PM.

  28. #28
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by leelnich View Post
    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. #29
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Find a range of matching cells

    Quote Originally Posted by jarhead58 View Post
    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?
    Last edited by leelnich; 01-23-2018 at 08:17 PM.

  30. #30
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

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

  31. #31
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    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
    Last edited by leelnich; 01-24-2018 at 08:19 AM. Reason: Logical error corrected

  32. #32
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,239

    Re: Find a range of matching cells

    Ok thank you

  33. #33
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    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.
    Last edited by leelnich; 01-24-2018 at 08:17 AM.

  34. #34
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,239

    Re: Find a range of matching cells

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

  35. #35
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by leelnich View Post
    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!!
    Last edited by jarhead58; 01-25-2018 at 09:29 AM.

  36. #36
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Find a range of matching cells

    Quote Originally Posted by jarhead58 View Post
    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. #37
    Registered User
    Join Date
    10-10-2017
    Location
    North Charleston, SC
    MS-Off Ver
    2016
    Posts
    72

    Re: Find a range of matching cells

    Quote Originally Posted by leelnich View Post
    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!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find rows matching on 3 columns - date range; dollar range; and A or B
    By strassbergere in forum Excel General
    Replies: 0
    Last Post: 06-28-2016, 08:21 PM
  2. Find Timestamp in a range of non-matching timestamps
    By kalffiend in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-10-2015, 11:56 AM
  3. [SOLVED] lookup a range of cells and populate specific cells based on matching data
    By RobertM01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2014, 08:18 PM
  4. Find the heading matching with range
    By turist in forum Excel General
    Replies: 2
    Last Post: 04-19-2014, 07:59 PM
  5. Replies: 2
    Last Post: 09-07-2013, 07:50 PM
  6. [SOLVED] Find complex name in range and check off matching cells
    By Durarara in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-07-2012, 01:54 PM
  7. need to find matching cells in 2 columns
    By riotstar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2005, 07:33 AM

Bookmarks

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