+ Reply to Thread
Results 1 to 23 of 23

Find Common Values across multiple sheets based on Unique ID

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Find Common Values across multiple sheets based on Unique ID

    Sometime ago I got help from this board with a macro and we have been using the macro up until now, but now we need a little modification in the macro, so I was wondering if someone can help me in either updating the matcro or if it easier to come up with a new macro.
    The old thread is here:
    http://www.excelforum.com/excel-prog...unique-id.html

    There are three sheets in the workbook, Project, Tasks and Details and the expected resulting sheets are RESULT, In_Tasks_but_NOT_in_Projects and In_Details_but_NOT_in_Projects .

    But now what I am looking for help is:
    1. Copy the Projects data as is in the RESULT sheet.

    2. Then in the Tasks sheet, if the ID matches paste the matching rows under the data from Projects (as in the result sheet with Orange colour)
    3. If the ID is present in Tasks but NOT in Projects then copy it into the In_Tasks_but_NOT_in_Projects sheet.

    4. Then If the ID and the Name in the Details tab matches with the data in the RESULT sheet then paste it under the ID and Name (as in the result sheet with Green colour)
    5. If the ID does not match the ID in the results sheet then copy that row into the In_Details_but_NOT_in_Projects sheet.

    Thre result of the current macro that RHCPgergo helped with are in the last sheet.

    The formatting and colour of the rows doesnt matter, it is more of nice to have.

    Can someone please help me with the macro. The last Macro has been really helped us and have saved lots of time in preparing the weekly reports.
    I will really appreciate any help. Thanks and regards in advance.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    Bump ! Can someone please help?

    Also, I forgot to mention that this is only a sample data set. In the real data the columns dont change but the number of rows varies depending on the project and the week.

  3. #3
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    bump ! Can anyone please give me any pointers?

  4. #4
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    I have tried to modify the code, but it is only pulling partial values in the results tab and not the values where only the ID matches between project and details. Can someone please guide me and give me some hints on what I need to further modify? I will appreciate any help.
    This is the code that I have so far:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    This is what I have so far. Can someone please help me come up with the routine to match it with the Details and guide me if I am not doing it right or if there's a better way of doing it? Thanks !!

    Please Login or Register  to view this content.

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

    Re: Find Common Values across multiple sheets based on Unique ID

    Are either of the results posted the way you want them?

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 10-26-2013 at 05:27 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
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Find Common Values across multiple sheets based on Unique ID

    Hi,
    Here's an other solution using a different approauch. Code is longer than I first expected. I started with something in mind and as I was testing I noticed the headers were not in the same orders and a few other things that finally made the code very long...

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  8. #8
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    Hi GC Excel.
    Thanks for the macro, it works very well on the sample data sheet. Let me run it on the "actual data" and will report back to you.

    Hi xladept,
    Thanks for all the efforts but the macro you have provided is not updating the "Results" sheet as it was in the "Results" worksheet in the attached workbook. By both the macros did you mean yours and GC Excel's or did you put in another macro to the solution, since I got notified a couple of times about the thread.

    I do appreciate both of your time and help.

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

    Re: Find Common Values across multiple sheets based on Unique ID

    Nevermind - still in progress.
    Last edited by xladept; 10-27-2013 at 04:40 PM.

  10. #10
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    Hi GC Excel.

    I tested the macro on the master data and it works well, there's just one little thing that I noticed. Let's say if the ID is missing or is blank in Details (row 12, cell A12 in the Details tab in the attached worksheet). Would it be possible to account for the blank values as well and have them show up in the "In_Details_but_NOT_in_Projects" tab?

    Can you please look at it if and when you get a chance? I dont know how difficult or easy it is to accommodate for this condition. This almost works perfectly if it can also account for blanks. Thanks a lot for your time and help. I really do appreciate it.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Find Common Values across multiple sheets based on Unique ID

    Hello desibaduji,
    See attached file and let me know if it's ok...
    Attached Files Attached Files

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

    Re: Find Common Values across multiple sheets based on Unique ID

    Hi Desi,

    I think this is what you want - but the null ID can't be last:

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    Hi GC Excel.
    Thanks for the updated macro, when I run the macro and if there are multiple rows with missing ID, the "In_Details_but_NOT_in_Projects" tab is only displaying the last ID that was blank. I think it is overwriting the values for blank ID. I tested it by just deleting random values in the same worksheet. In the beginning , middle and also the last row. Can you please look at it. Thanks. Appreciate your help.

  14. #14
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    xladept.

    Thanks a lot for your help but the macro is not working, it is giving me duplicate values in the results tab. I appreciate you taking the time to help me.

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

    Re: Find Common Values across multiple sheets based on Unique ID

    It comes out nicely for me - do you have a different sample??

  16. #16
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Find Common Values across multiple sheets based on Unique ID

    Hello again,
    see attached file. This will work if multiple ID are blank.
    Attached Files Attached Files

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

    Re: Find Common Values across multiple sheets based on Unique ID

    Ditto:

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    Thanks GC Excel and xladept.

    This works, I will mark the thread as solved. I appreciate your help.

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

    Re: Find Common Values across multiple sheets based on Unique ID

    You're welcome!

  20. #20
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    Hi GC Excel.

    This solution has been working very well for me but I have come across a condition/situation that needs a little modification to the original macro. Can you please help and modify the macro to accommodate for this situation

    In the sample sheet, in the Details tab there are certain rows that are missing both the ID and the Name, so when I run the macro they get lost, but they should show up in the "In Details but NOT in Project" tab, just like how the rows with missing ID show up there.

    Can you please look at it, I will really appreciate your help. This macro has been working great and has saved me a lot of time to create these reports.
    Thanks in advance for your help.
    Attached Files Attached Files

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

    Re: Find Common Values across multiple sheets based on Unique ID

    Hi Desi,

    Try this:

    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    09-27-2012
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Find Common Values across multiple sheets based on Unique ID

    xladept

    This works Thanks a lot for your help !

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

    Re: Find Common Values across multiple sheets based on Unique ID

    You're welcome! And, thanks for the rep

+ 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 count of Unique or Duplicate Values based on Concatenated values in 2 columns
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-03-2014, 12:42 AM
  2. How to find unique records from multiple sheets
    By Mike Nelson in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2013, 08:38 AM
  3. Replies: 2
    Last Post: 02-06-2013, 04:44 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