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.

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.

bump ! Can anyone please give me any pointers?

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.

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.

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.

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.

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.

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.

Thanks GC Excel and xladept.

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

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.

xladept

This works Thanks a lot for your help !