Solved

Retrieving unique values in excel

Posted on 2011-09-15
18
182 Views
Last Modified: 2012-05-12
I have a mailing list of 20,000 records on an excel spreadsheet, I am using Excel 2003.

Here is a sample of my recordset.

Column A         Column B                   Column C
John Smith        205 Broad St.,NYC   Product ABC
Mary Jones      123 Main St., NYC     Product ABC
Mary Jones      123 Main St., NYC     Product XYZ
Stan Smith        132 Main St., NYC     Product XYZ

I want to have my list to show only customers who are only ordering one product, i.e. only John Smith and Stan Smith so we can mail their product out right away.  

I have experimented with advanced filtering with no luck.

0
Comment
Question by:JeanHolton
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36543419
If you added a header row you could do a pivot table. Name in row field, product in column field and data and then you can pick out the rows with only one number in them (i.e. one product) with a formula.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36543430
I assume that if they only order 1 product they will only appear once on the list....or can they appear more than once with the same product?

If it's the former then try using this formula in a blank column in row 2

=IF(COUNTIF(C:C,C2)=1,"x","")

copy formula down then filter by "x" in that column

regards, barry
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 36543433
Assuming your list of names is in column A, this formula will give you a list of entries with no duplicates:

=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100="",ROWS($A$1:$A$100),IF(MATCH($A$1:$A$100,$A$1:$A$100,0)=ROW($A$1:$A$100),ROW($A$1:$A$100),ROWS($A$1:$A$100))),ROW()))

Confirm the formula with Ctrl + Shift + Enter rather than just Enter. This will add {} at either end.  Adjust the references to "$A1:$A100" to reflect your range.

Copy down until you get zero as the result.

Thanks
Rob H
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 36543438
Sorry my formula should refer to column A not C, i.e.

=IF(COUNTIF(A:A,A2)=1,"x","")

barry
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 36543710
@StephenJR

You say "product in column field and data"

Excel 2003 doesn't allow a data field to be in more than one table field or am I missing a trick here or misunderstanding your comment? I know 2007 allows this.

Thanks
Rob H
0
 

Author Comment

by:JeanHolton
ID: 36543761
Barry,

I tried your formula, it works fine except when it finds two names the same, the next name underneath, which is listed only once, does not get an "x".

Robhenson,
I tired your formula too.  the names were just repeated unless I didn't put it in the right cell.

Thanks
Jean
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36543777
Rob H - you can put it in more than field in a PT in 2003 (unless I'm misunderstanding you).
pic.JPG
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36543829
And the pivot table?
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 36544088
StephenJR - I stand corrected! I have often had problems doing this but I suspect I have tried adding numerical data to more than one field.

Jean - with your sample data in A1 to C5 with headers, I have put the formula in F1 and copied down.

I get a list:

Name
John Smith
Mary Jones
Stan Smith
0
0
0
0... zeroes continued to bottom of populated cells.

Is this not what you were after, a list of unique entries based on the same assumption from barry that single item orders have only one entry.

Against this list you can then use lookups etc to populate the remaining data; address and product.

Thanks
Rob
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 36544107
On the pivot table option, how would the user then filter the total column for only 1's?

Thanks
Rob H

PS This may come in useful with another question I was looking at earlier.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36544194
Rob - you could put a formula in column E, e.g. =COUNTA(B12:C12) and all the rows with a 1 would people with only one product. Then sort or filter. I think it sounds more time consuming than it actually is (although the formulae posted may well be simpler).

0
 

Author Comment

by:JeanHolton
ID: 36544417
Rob H
I tried your formula again, it works but I also need the addresses with the names so we can print mailing labels.  Also, I noticed that in my example, Mary Jones would be on the list - but I don't want her to be on it since she has ordered two items.  Our mailing dept just wants a list with only people who have ordered one item.

Stephen JR,
I tried the pivot table and it works but again I need to see the address column so after filtering I can copy the list to print mailing labels.

Again, thanks!
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36548115
You could put a formula in col D which flags up the 1s and extract those addresses. If you post a small workbook illustrating the exact results you need we can no doubt show you better.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 36548659
By putting a formula alongside the pivot table we are back to doing a count of a list so might as well put the count against the original list as per Barry's suggestion at the beginning of this thread.

Barry's formula:

=COUNTIF(A:A,A2)

Should be entered in row 2, eg D2, and then copied down. Then filter this column for 1s. You then have your list of names, addresses and products.

Thanks
Rob H
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36549165
The only problem with Barry's formula is if there are cases of the same person ordering the same product but appearing on more than one row. I'm sure Barry could adjust his formula to cater for that though.
0
 

Author Comment

by:JeanHolton
ID: 36549205
Stephen Jr and others:
Here is a sample workbook indicating a sample of the original spreadsheet and what the list needs to look like when done.  Names and addresses are changed to fictional characters.

Normally I would scroll through the list and manually remove those who have more than one order.  However, the list contains over 20,000 names.
0
 

Author Comment

by:JeanHolton
ID: 36549213
Upload failed - I will try again
Sample.xls
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36549557
Because your names are separated I would put in eg I4and copy down

=A4&B4

Then in J4 and down

=IF(COUNTIF($I$4:$I$23,A4&B4)=1,1,"")

And then filter by the 1s in col J (or sort or advanced filter to another sheet) and they're the ones you want. This assumes that there are no cases of the same person ordering the same product but appearing on more than one row.

Please note this is essentially Barry's method above so don't credit me, I just happened to be in the area.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now