• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Removing duplicates and consolidating phone number list

R

ran

New Member
In my worksheet I have a list of donors who donate to a charity that I volunteer for.

Column labels are as follows:

F Name M Name L/Name Address City P.code Phone #

Data description:

Each phone number may be listed more than once depending on how many times a family made donations

Each phone number is tied to one address

Each phone number and address may have more than one name entries depending on how many family members made donations.

I want to consolidate the information so that I have unique first names associated with a phone number.

I have uploaded a sample file to Dropbox at the following link.

https://www.dropbox.com/s/isfbq22i9fli5mt/excel%20removing%20duplicates%20problem%20file.xlsx


There are two tabs, before and after. After is what I want from before. Here I did it manually. The complete file has in excess of 7,000 entries.


Any help from the forum, like always is appreciated.


Thank you
 
Hi ,


I am not sure I have understood you , since I do not know why you have brought in the issue of phone numbers ; the first point is that a person is identified by his / her first name , middle name and last name. Can there be two entries for the same person , with either of the following being different - address , city , post code , phone number ?


As far as I can see , all you need to do is to use a helper column , and use the following formula :


=First_name&"|"&Phone_Number


Copy this down to get a concatenated value of the first name and phone number.


Copy this column , and paste special values in a separate sheet.


Remove duplicates from this column.


Use the text to columns facility using the "|" symbol as a delimiter to get back the individual items of first name and phone number.


Use the VLOOKUP function to retrieve the associated entries of Middle Name , Last Name , Address , City and Post Code.


Narayan
 
as per my understanding there is no connection with First name....

you want to make unique id number on the basis of phone number and p.code(pin code)...


is it?


Regards

CA Mahaveer Somani
 
Hello CA Mahaveer Somani.

For now I have used the method suggested by Narayan. It works.

I will bug you if I am stumped again with an excel issue.

Thank you for your response
 
You must log in or register to reply here.
Back
Top