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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Build your own Acct-Customer field.
When you build the pivot table, put this field in the row labels.
- This has the data that you need and avoids the duplicate customer names.