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.
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.
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
The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
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
JeanHoltonAuthor Commented:
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.
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
JeanHoltonAuthor Commented:
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.
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.
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.
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
JeanHoltonAuthor Commented:
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.
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
Solve your biggest tech problems alongside global tech experts with 1:1 help.