excel - find same names but with different addresses

0
votes

Ok I've been racking my brain about this but can't come up with a clean way of getting the data I need.

I have a large list of customer information. I am interested in finding out where customers have bought different items, the layout of my cells is like:

Customer Name, Customer Address, Product

Joe Bloggs, 123 The Avenue, Apples

Joe Bloggs, 123 The Avenue, Apples

Jim Smith, 456 The Road, Oranges

Jim Smith, 456 The Road, Apples

So in this case, I'm not interested in Joe Bloggs as he bought Apples both times. I'm interested in Jim as he bought Oranges AND Apples. Is there a helper column I could use to help me filter this data out? So, counting if the customer name and address appears more than once and if the products are different? Thanks.

1
excelexcel-formulavba
Are you OK with VBA macros or do you only want Excel worksheet functions? – mcy
Fine with either thanks – user2672288
thanks pnuts, apols the commas in my layout above should actually represent different columns of data as opposed to one cell per row in comma delimited. – user2672288
Try =COUNTIFS(A:A,A2,B:B,B2,C:C,C2) in row 2 and drag down in a helper column and filtering only on the 1 (unique rows, if that is what you are looking for). – Jerry
You can put everything in a pivot-table and then screen out the stuff you want to look at. Sometimes easier to eyeball it like that than jump through hoops with convoluted formulae – hnk

1 Answers

0
votes

the best way to do this is creating a pivot tabel wich is based on the customer and the product

then sort on amount of products

Succes Toontje