Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables - Can you just list data and not SUM | Excel Worksheet Functions | |||
Showing duplicates in pivot tables | Excel Worksheet Functions | |||
Pivot tables off a List | Excel Discussion (Misc queries) | |||
Where has my field list gone for my Pivot Tables? | Excel Worksheet Functions | |||
My pivot table field list show duplicates | Excel Discussion (Misc queries) |