This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Show Customer Account & Name
Problem: My source data comes from the sales reps. They don’t use consistent names, so I end up with multiple customer names for one account number.
Figure 915 Bad for pivot tables.
This forces me to put both Acct and Customer in the row area. This looks horrible. The customer name doesn’t appear on the totals for each account.
Figure 916 You want Acct and Customer on the total row.
Strategy: This common problem would be solved if the Excel team would add First and Last to this dropdown. Look; we don’t really care if we get G.E. or General Electric...either one is a million times better than an empty cell on the total row.
Figure 917 This addition would solve the problem.
But, for now, we don’t have First or Last. Your best bet is likely to be creating a pivot table with Acct and Revenue. Copy the entire pivot table. Paste as Values.
Figure 918 Convert the pivot table to values.
You are now allowed to insert a blank column between Acct and Revenue. Use a VLOOKUP back into the original data set to fill in the customer name.
Figure 919 Use a VLOOKUP to get the text fields.
Alternate Strategy: You can do the FIRST yourself back in the original data set. Add a column called Acct - Customer. Use a formula to concatenate the Acct with a VLOOKUP to return the first customer from the list.
Figure 920 Build your own Acct-Customer field.
When you build the pivot table, put this field in the row labels.
Figure 921 This has the data that you need and avoids the duplicate customer names.