Merging selected lines in a worksheet to one line - removing dupes - Office 2013

[cultivator]

Hi there!

I have a problem I can not solve by myself and it's a lot of work to do this by hand. Hope I'll find some help here.

I merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums.
I added a few columns and have 1 large list now.

As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns.
Some of the lines have empty fields (i.e. no address or mail)

All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:

company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx

I atteched an example of the full list and the result i want

Can anyone help me with this? Thx in advance for your help.

Cheers!
Tommy

[Leith Ross]

Hello Tommy,

I am not sure what to do with repeated information. Are you simply using the last value and ignoring any other data in the column?

For Example
The number in blue is what you show in your example.

Please Login or Register  to view this content.

[cultivator]

OK my mistake!

The mobile number or any other field should always be the same information in the list. I will clean the list before i run any script or macro.

Just assume per line and field there is an entry or no entry.

if there is one -> compare it to the final line and ad it if the final field is empty
if there is one -> compare it to the final line and ad it if the final field is not compare -> if the same -> delete it
if there is one -> compare it to the final line and ad it if the final field is not compare -> if not the same -> leave and highlight it (so I can decide later which to use -> shpould be very rare occasion)
if there is no entry in the field -> compare it to the final line and ignore it
All merged lines should then be deleted and only one line should be left with all the data in it from the other lines.

Tommy

[cultivator]

Edit: The example I uploaded is only one data block 16 dupes to be merged to one. Above an below this entry there are more adresses and offcourse other dupes.

[Leith Ross]

Hello Tommy,

This macro will combine the data into a single row. The worksheet data is cleared except for the headers in row one and the combined data is copied to row 2. There is a button on the worksheet to run the macro.

This macro is written for the example you provided. If the actual data is arranged differently or you have multiple groups of data, the macro will fail.

I have added the code below to the attached workbook. Let me know how this works for you.

Please Login or Register  to view this content.

[cultivator]

It works! Many Thanks! You made my day!

Is there also a possibility to merge the whole list with this Macro?
I sent you a excerpt of the real list earlier by PM.
Do I have to mark the addresses I want to merge?
any tipp how to use this to merge the whole list?
Sorry for these "dumb" questions but this is my first time with macros in excel

Tommy

[Leith Ross]

Hello Tommy,

You sent me a link to this post by PM but not a link to another workbook. I would need to see the actual data layout before I can modify the macro.

If it contains sensitive data you can either change it before posting or email a copy to me. I can send you my email by PM if needed.

[cultivator]

I sent you a PM with my mail address. If you didn't get it, just use the one I used here to register.
THX!

[Leith Ross]

Hello Tommy,

I received it and sent you my email.

[cultivator]

list is in your inbox.