Pivot Table: Customer Number and Names side by side on same line, not separately

[fred]

Hi I have 3 columns of raw data (a few thousand lines)


column A: Customer Numbers (one number to one specific customer)

column B: Customer Names

column C: Sales figures


A customer in any given month can have multiple differetn sales figures (multiple deals). So in column A customer 1234 and name ABC bakery can appear multiple times.


In pivot table I use Customer numbers as row labels and sales figures on Total Value. But there are hundreds of customers and no one could remember who customer 1234, or 2345, or 9382 is. So I added "Customer Names" to row labels and it appears on a separate line and the sales figure would duplicate.


How do I "move" customer name to stand on the same line of customer number? I'm using Excel 2010. Thank you very much.

 

[Hui]

Fred


Why not add a new field to the original data

CustName: =Customer No & " " & Customer Name

then use the new field instead of the Customer Number field

 

[fred]

I thought about that, too. it will certainly work. what really bugs me is that I can do this with Excel 2003. for some strange reason this won't work in 2007 or 2010.


Any way, thanks, Hui!