Newbie needs helps in filtering out data

[wannabelean]

Hello all

I have 4 columns which have :

Customer Name
Work Phone
Home Phone
Cell Phone
Business Given

Each customer has all three telephone numbers
The number of customers is about 60000

The issue I'm having is :

We have many customers who have common numbers ( can be work, cell or home). The customers are usually the 2nd,3rd or 4th family member of the house.

What is the best way to gather the following data :

1. The number of uniques customer names
2. The number of unique customers

I know how to clean up duplicates and I've read the tips on the site but I'm not sure how to go about this.

Thanks in advance

 

[Armando Montes]

Try advanced filter:
http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP005200178.aspx
http://office.microsoft.com/en-us/e...s-or-remove-duplicate-values-HP010073943.aspx

 

[Peter_SSs]

Welcome to the MrExcel board!

What is the best way to gather the following data :

1. The number of uniques customer names
2. The number of unique customers

What's the difference between 1. and 2.?

(I was also wondering about your 4 columns for which you gave 5 titles? )

 

[chekree]

Hello,
Your question is not complete and it is confusing. You are failed to explain your real problem.. Please try to explain what you really want.. Please upload a few sample rows too.. Your task is very simple and it can be done with advanced filter(press Alt, A, E one by one to access advanced filter), and remove duplicates (Alt, A, M) options.

 

[Peter_SSs]

Hello,
Your question is not complete and it is confusing. You are failed to explain your real problem.. Please try to explain what you really want..

Interesting. If the above is true, how can you be so sure that the task is so simple and that the solution is as follows?

Your task is very simple and it can be done with advanced filter(press Alt, A, E one by one to access advanced filter), and remove duplicates (Alt, A, M) options.

 

[chekree]

I am sorry Sir.. I didn't criticized you. I am a minnow compared to your expertize and efficiency. But, I had put that reply to that person asking him to explain his problem in detail..so that some one like you will solve his problem..

 

[Peter_SSs]

I am sorry Sir.. I didn't criticized you. I am a minnow compared to your expertize and efficiency. But, I had put that reply to that person asking him to explain his problem in detail..so that some one like you will solve his problem..

I am sorry if I offended you. It just seemed unusual that you said the problem wasn't clear but then said the solution was simple. I didn't understand how those two things could fit together.

 

[wannabelean]

Welcome to the MrExcel board!

What's the difference between 1. and 2.?

(I was also wondering about your 4 columns for which you gave 5 titles? )

Thanks for the welcoming note Peter. My bad it should be 5 columns. 1 will have names of 60,000 names. 2 will not have that many because they have a common number with another individual which means they are fromt the same house.

The list is a food delivery based business customer list. Sometimes we have 2 or 3 different people from the same house calling for delivery orders using the same telephone numbers.

 

[Peter_SSs]

Then I think Armando's suggestion of Advanced Filter may be what you need. You should be able to use that on the 'Custome Name' column to get a list of unique customer names.

I'm still a little unclear about the 'Unique Customers'. I understand about different people calling from the same phone but you have three different columns for phone numbers and you have stated that each customer has all 3 phone numbers. Therefore I assume a 'Unique Customer' is really a unique set of 3 phone numbers (Work, Home & Cell). Again Advanced filter used on those 3 columns together should be able to generate a list of unique phone numer combinations.

Here is a simple example. Original data is in columns A:D

Advanced Filter for unique items on column A gives the list of 'Unique Custome Names in column F.

Advanced Filter used again on columns B:C (unique items) produces the list of unique phone number combinations in columns I:K.

If this is not what you want, could you make up a small (say 10-15 rows) set of dummy and the corresponding results that you would expect from that data?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Work	Home	Cell			Name		Work	Home	Cell
2	Name 1	1	2	3			Name 1		1	2	3
3	Name 2	4	2	5			Name 2		4	2	5
4	Name 3	1	2	3			Name 3
5	Name 2	4	2	5
6	Name 3	1	2	3
7	Name 2	4	2	5
8	Name 1	1	2	3
9
Customers