Duplicate removal in a large excel sheet

[vj2k]

Hi,
I have an excel sheet with around 12,000 records. This is a database of a set of people. I have around 27 columns and 12,000 rows

I have the following issue

I have multiple entries of people with records having some data for e,g, three records of John Doe but one has an address one has a telephone number and one will have nothing.

How can I merge them such that 4 fields are the same, salutation, first name, last name and company name and records are merged in such a way that all data from rows are in a single record.

Thanks
Vijay

I am on a mac but can get access to a PC

[Miraun]

Hey Vj2k;

So, in Excel 2011, there is a remove duplicates function in the Data Ribbon Tab. What this will do is remove all repeat instances of data for your criteria... But, you're making it a little more complex with potentially having incomplete data in the other columns.

So, I just did some quick testing of the Remove Duplicates option, and what I've found is that it will always keep the first instance that it identifies.

With that in mind, What we can do in this particular instance is add a 5th column that just simply tells us how many previous columns were filled out.

By using a =Counta(a1:e1), pulled down for all 12000 records, it'll flag the complete entries.

From here, sort your data on both the person's name, AND the new Counted field... You want the full counts on the top.

From here, you can remove duplicates on the person's name, and it should only retain the most full entry associated with that person.

I hope this helps, and that you can follow the methodology for the use. Post back if you have anymore questions!

[vj2k]

Hi,
Thanks for your reply,
It does not help me with one thing, I need to merge data for fields which are incomplete

Vijay

[Miraun]

Sorry... I'm not sure what you're trying to say. But if it's what I think, when you use the remove duplicates, you should only be concerned with the completed name fields, and not include the entire 4 columns of data.

[L-Drr]

I think VJ's problem is this: if there are several rows of John Doe that have the same numer of data, but still different ones (e.g. name-address-country and name-phonenumber-country) they both have the most data, but keeping only one will be a loss of information.

But I don't think there is a fully automated solution for this... If you try something like a LOOKUP, you'll always have trouble with adresses that are spelled slightly different (but are in fact the same) etc etc.

[popipipo]

A little excel file will help to find a solution

[FDibbins]

Hi and welcome to the forum

I agree with popipipo that you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that


To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.