List any duplicates once - no pivot tables
 

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

List any duplicates once - no pivot tables
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Driftwood" wrote:

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

List any duplicates once - no pivot tables
 

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.

"Driftwood" wrote:

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