Help Please ! Merge two worksheets, then sort, arrange and look for exceptions !

Hello,

I am scratching my head and trying to figure out how can I achieve what I am trying to do. Can you please help me or point me in the right direction? I want to be able to write a script or a macro to be able to merge these worksheets based on the criteria !!

I have data in two sheets, sample 1 and sample 2. The formats will always remain the same as they are export from a pre-defined program and these sheets are emailed out for every project that we work on.
What I am trying to achieve is:

1. I want to be able to merge these two worksheets together.
2. Look up the ID and Name of the resource in the Details sheet.
3. Match it to the assignments in the Tasks Sheet, first based on ID and then Name.
4. If there is a match then put them together in the "RESULT" sheet.
5. For the Non Matching names in the DETAILS worksheet, put them at last as exceptions.

I am not sure how to do or or if even this can be done or not. I have attached a sample sheet with the data, if you need more information please let me know.

Any help is appreciated. Can this be done by writing a macro? or this is something that cannot be achieved? Thanks!


sample_merge.xlsx

In your data, 2 individuals have the same ID. So the ID field does not seem to be a unique identifier. Is the name the only unique identifier?

Hi Antonio,

Thanks for the macro but can you please also help with a couple more things. I think it is very close but just a little more help please

1. if you look at the results for ID 1004 the exceptions is getting listed before the actual data. Can we list the matches first and then the exceptions? (eg for 1004 both Frank and Sam should come under exceptions)

2. Also, looking at the code, i was trying to understand how it works so that I can also try to edit/debug it. Does this segment

Please Login or Register  to view this content.

3. Also I missed out a couple of columns in the details tab, so added those, Sorry about it. I hope it's not too big an issue.

sample_merge_db_v1.xlsm


Does this mean that it will only work if there are only 7 entries in the Details worksheet? Can it be modified so that it will work for the whole worksheet, as the number of rows in both the worksheets will vary. this was just a sample dataset.

I am sorry if I was not clear in the original request. The format of the data will not change but the number of rows for the data will certainly vary. it can be anywhere from 7 to 700 rows

Once again, thanks a lot. I really really do appreciate it.