List any duplicates once - no pivot tables

  • Thread starter Thread starter Driftwood
  • Start date Start date
D

Driftwood

Hello,

What I'm looking for is (without creating a pivot table):
In Column Q:
List all customers - only once and no empty rows- that appear in column B
from row 10 through row 2000.

For more information on my sheet setup, please read below.

My sheet setup is:
-Row 1-10 are empty cells & macro buttons-
-All column have autofilter starting in row 10-

Column A: Dates of orders starting from 1/1/09 - YTD (duplicates no problem)
Column B: List of customer names (customers appear more than once)
Column I: Amount of the invoice for orders ($$)
Column L: Month&CustomerName like:"7SmithRubber"

What I'm looking for is (without creating a pivot table):
Column Q:
List all customers - only once - that appear in column B from row 10 through
2000
Then I can do a SUMTIF:
SUMIF(B:B,Q1,I:I) per customer YTD and another per customer - per month.

I would like to eventually have this as a template, as the customer names
will constantly be changing/updated, so I never know who's name will be added
- or omitted from column B - that's where I'm struggling with the listing a
customer only once.

Thanks
Driftwood
 
In Q11:
=B11
In Q12**:
=IF(COUNT(MATCH($B$11:$B$2000,$Q$11:Q11,0))<COUNT(1/($B$11:$B$2000<>""))
INDEX($B$11:$B$2000,MATCH(0,($B$11:$B$2000<>"")-ISNA(MATCH($B$11:$B$2000,$Q$11:Q11,0)),0)),"")

** This is an array formula, and needs to be confirmed using
Ctrl+Shift+Enter, not just Enter.
You can then copy this formula down to Q2000. Note that it will fill the
unneeded cells with blanks.
 
Luke's approach is one way. Another way is to use Excel's Advanced filtering.

Select the column of customers, then go to Data->Filter->Advanced Filter
(I've been using 2007 for a while, so my recall of 2003 menus is a little
rusty) Use the 'copy to new location' and 'Unique records only' to get a
list of unique customers.

Use the macro recorder to record your steps if you want to automate for
future use.
 
Back
Top