+ Reply to Thread
Results 1 to 16 of 16

Find matching number from 2 sheets and combine related data

  1. #1
    Registered User
    Join Date
    02-02-2013
    Location
    texas
    MS-Off Ver
    Excel 2003/2010
    Posts
    59

    Find matching number from 2 sheets and combine related data

    On Sheet1 I have an ID# in Column A, with its value in Column B:

    (A--B)
    .#--$
    .#--$
    .#--$
    .#--$

    On Sheet2 I have the same type of ID#s in Column A, with Names In B:

    (A----B)
    .#--Name
    .#--Name
    .#--Name
    .#--Name

    I have been trying to figure out how to get the matching IDs with their corresponding Name and Value on Sheet3 like so:

    (A-----B-----C)
    .#--Name--$
    .#--Name--$
    .#--Name--$
    .#--Name--$

    Only "twist" to throw in, is i would need some way to see what was left over, Or the Non-matches that were not moved to Sheet3. Reason being, some of the IDs that SHOULD match, may be "different" in the sense that there could be a "#" included or a letter at the end (ex: 01234A)

    Any help on this annoying issue would be greatly appreciated, Thanks!

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Find matching number from 2 sheets and combine related data

    Does this help?

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-02-2013
    Location
    texas
    MS-Off Ver
    Excel 2003/2010
    Posts
    59

    Re: Find matching number from 2 sheets and combine related data

    ALmost! On Sheet3, it's returning all values of Sheet2 as "No-Matches".

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Find matching number from 2 sheets and combine related data

    Can you attach a sample? I tested it on some data I made up, but real data would be better. On my test it seemed to comply with your requirements.

  5. #5
    Registered User
    Join Date
    02-02-2013
    Location
    texas
    MS-Off Ver
    Excel 2003/2010
    Posts
    59

    Re: Find matching number from 2 sheets and combine related data

    I hope i did this correctly..
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Find matching number from 2 sheets and combine related data

    Very slow code, but have a go

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    02-02-2013
    Location
    texas
    MS-Off Ver
    Excel 2003/2010
    Posts
    59

    Re: Find matching number from 2 sheets and combine related data

    That's perfect, Thanks AB!

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Find matching number from 2 sheets and combine related data

    When you ran the macro did you change the (VLOOKUP) ranges to match your sheet?

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find matching number from 2 sheets and combine related data

    Give this a try

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Find matching number from 2 sheets and combine related data

    I was about to post a code based on dictionary, but Mike has Ninja me. I bet you Mike's code runs much faster by miles.

  11. #11
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find matching number from 2 sheets and combine related data

    I think maybe I had my comparison backwords. Try this one

    Please Login or Register  to view this content.
    Last edited by mike7952; 02-26-2013 at 04:57 PM.

  12. #12
    Registered User
    Join Date
    02-02-2013
    Location
    texas
    MS-Off Ver
    Excel 2003/2010
    Posts
    59

    Re: Find matching number from 2 sheets and combine related data

    oh wow. . it's almost instant? how is that? even if i write a small loop to search for a string and highlight, it performs slower than this does. . I don't understand?? How and why is it so fast?

  13. #13
    Registered User
    Join Date
    02-02-2013
    Location
    texas
    MS-Off Ver
    Excel 2003/2010
    Posts
    59

    Re: Find matching number from 2 sheets and combine related data

    As amazing as it is, other codes wont run without the error message "out of memory" Is there a way to clear memory entirely without going through and setting all variables in every module = Nothing ?

  14. #14
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Find matching number from 2 sheets and combine related data

    Assigning a range to an array and then writing back to range is much faster than looping through a range. My code loops through each cell and this takes its tall on the speed of the code, while Mike's one use array and dictionary.

  15. #15
    Registered User
    Join Date
    02-02-2013
    Location
    texas
    MS-Off Ver
    Excel 2003/2010
    Posts
    59

    Re: Find matching number from 2 sheets and combine related data

    very nice. Both are greatly appreciated!

  16. #16
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find matching number from 2 sheets and combine related data

    Quote Originally Posted by downtown1933 View Post
    As amazing as it is, other codes wont run without the error message "out of memory" Is there a way to clear memory entirely without going through and setting all variables in every module = Nothing ?
    Is this error with my solution? if so try the below, I reduced the rows in the array.

    Please Login or Register  to view this content.
    Last edited by mike7952; 02-26-2013 at 05:48 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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