Find duplicates in multiple worksheets and consolidate based on unique id

hello,

Can I please get some help in coming up with a macro.

We get these three reports from our different routes, attached is a sample workbook with worksheets (Projects, Tasks and Details), the data in them varies by location and month but the format remains the same. Currently I manually consolidate and merge these sheets to combine the data together.

The ID is the unique identifier among all the three sheets.

1. First I combine the data from Projects and Tasks into Results, if the ID is common and duplicate, then the data is copied and pasted into the result.
2. Then I look up the duplicates between the Details and the Result, and paste the duplicate records under the ID's, to come up with the RESULT sheet as it is displayed in the workbook.

I have color coded them in the Results worksheet to better help in understanding the data.

What I want to be able to do is:
1. Have a Macro that looks up the duplicates between Projects and Tasks and Details.
2. Merge and consolidate the results in the Result sheet.
3. If there are any missing records (eg id 1005 is missing in Tasks) then either note it as missing or if possible throw it out as an exception in maybe another sheet.

Can someone please help me with this? I will really appreciate it.
If you need any more clarification please let me know.

Regards and thanks in advance.

Can someone please help? or point/guide me how it can be done? I will really appreciate any help.

Hi,

here's a macro that does something like that. I'm not sure what exactly you want to achieve. To me it seems that arranging the data like that may not be the most efficient, because there are a lot of empty cells. Hard to make charts, pivot tables, things like that.

Please note that I've made this with Excel 2003, that's what I have at the moment. I hope it works with Excel 2010, too.

Hello RHCPgergo.

Thanks a lot for your help. I agree that arranging the data like this might not be the best way to get the report. Let me re-think about the report and how can we gather and distribute the information

Hi RHCPgergo,

This works perfectly, can i just ask you for a little tweak please? And then it will be all done. Can you please add the functionality to check to see if there's a row that has been matched from the "details" but the name is missing from the data from "tasks", just insert MISMATCH in the corresponding column E eg row 11, 12 and 20 in the results tab has names that are not present in the peach part so just put word mismatch in cell E11 and E12 and E21 (as in the attached RESULT tab)

ie in rows 8 to 12, Jason Lopez in green matches with Jason Lopez in Peach but Susan Morris and Dave Ortiz are not present and similarly in row 21 Sam Cota doesnt match.

Thanks you so much for your help, this is perfect. You helping with this is really helpful and means a lot. If you can add this then this will be perfect and exactly what is needed. Thanks !

Hey,

sorry for the slow answer, I'm kind of busy lately. I managed to implement the changes you requested. I hope you're happy with it.

Thanks ! This is perfect