display unique records

[Sophie]

I have 300 customers and for each order an indicator of how it was placed,
i.e telephone, email or website. There have been approx 1500 orders placed to
date from these 300 customers. Some customers order by a different method
each time. I need to know who orders but phone only, who orders by email
only, who orders by website only and who orders by a mixture of these
methods. I have a list of all orders where column A = customer name and
column B = order method. I am using excel 2007

 

[Joel]

You question is very vaque. How do you want to use the data? the simpliest
method is just use the autofilter. Highlight column B and then go to menu
Data - Filter - AutoFilter.

 

[Sophie]

sorry..

altho autofilter will show me those contacts who ordered for eg by
telephone, it could be that one of the contacts in the findings has also
ordered at some point by email too. But I want a list of contacts that have
only ever ordered by phone i.e, there will not be any seperate entries for
this person where they have contact me by another means. I would ideally like
a list of names per preferred contact method as I intend to contact them in
return by this means.

 

[Ragdyer]

Does this help?

Say your datalist runs from A2 to B1500,
with customer name in Column A, and order method in Column B.

In C2, enter this formula, and copy down:

=SUMPRODUCT(($A$2:$A$1500=A2)*($B$2:$B$1500=B2))=COUNTIF($A$2:$A$1500,A2)

This will display "True" or "False" if the order method is unique to the
customer.

 

[Shane Devenshire]

Hi,

You can create a column to control your AutoFilter and then you can auto
filter on TRUE
In 2007 use the formula

=COUNTIFS(A$2:A$1500,A2,B$2:B$1500,D$1)=COUNTIF(A$2:A$1500,A2)

In 2003 or 2007

=SUMPRODUCT(--(A$2:A$1500=A2),--(B$2:B$1500=D$1))=COUNTIF(A$2:A$1500,A2)

In this case the Customer is listed in column A, the method of mailing in
column B and in D1 you enter the method you want to filter for, such as
"mail". Copy one of the above formulas down all the way beside your data and
apply the filter on that column.