Merge two columns with non-duplicates and sort

[Zaeguzah]

Hi guys. I have a new problem that I hope you can help me out.

In my workbook I have three ranges of data. Each range contains 4 columns. I would like to merge the values from B:E with L:O only if the name in col B cannot be found in col L. All the names that can't be found will be added in the L:O list in a new range T:W. Where I would also like the data to be sorted alphabetically. A sample with an expected result has been made

Hope this makes sence and thanks for looking into it.
Alex

[Marcol]

Try pasting List B:E appended to List L:O

With Excel 2007 you can then go

1/. Highlight L:O

2/. Data > Remove duplicates
Choose your criteria > Okay

3/. Then sort the List

Is it a macro you are looking for?

[EDIT]
Macro added to workbook now attached.
It is speciffically for 2007
I can't test it in 2003 at the moment, but I would expect it to crash

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[Zaeguzah]

Hi Marc. One thing I would like to add to your script.
In range C:E I have formulas with absolute references. So, would it be possible to copy them as values? Right now, when I use your macro, it copies the name corectly but adds only "0" values.

[Marcol]

Try this

Please Login or Register  to view this content.

Paste all of the above in place of the original macro

Should do the job

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[Zaeguzah]

Hey Mark. The macro you posted works great. One more thing I would like to ask of you. The macro you just made were for a specific type of data. I do have another type similar to this one but with a few minor changes.

In the workbook the three ranges will now contain the date as well. I would like to merge the ranges B:F with L:P if the date from the names in B:F matches the date in L:P.
So, only check for duplicates the range that has the same date as B:F and skip all other dates.
Then sort alphabetically each range with a unique date. An edited sample has been uploaded.

Thank you Marc for your help.

[Zaeguzah]

Can someone help me out with what i wrote in post #5?

[Marcol]

Hi again

This code is a little more general

Please Login or Register  to view this content.

The previous code was not data specific that I could see
However after runing the code, "Remove Duplicates" seems to clear the cells of found duplicates of all formatting This seems to happen when the list is closed up.

Hence a number of rows after the new list is formed have no formatting.

The number of rows affected = number of rows before removal - number of rows after removal

To fix this in this instance I have reformatted column L in the code.

Because your concatinated names in the example sheet will not sort as you have indicated.

Please Login or Register  to view this content.

I have commented out this line

Please Login or Register  to view this content.

so you can compare the result with your expected result

Remove the comment (apostrophe) before you run it on the real lists.

Column J is for testing purposes and can be removed whenever you want.
I have left in Sheet1 (BU) so that you can copy and paste back in the original data at will.



Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[Zaeguzah]

Thanks Marc. I waited to make sure I don't encouter any problems with the code. It works great as usual. Thanks for your help (all of it) and I'm sorry if I rushed you (i thought you left my thread for good). I will add some reputation to you for your help.
Thanks you so much and I guess I'll see you around. SOLVED!!