Pivot table question

[lj]

Hi, I'm using an excel spreadsheet and I have a list of customer
numbers in column A and a list of account numbers in column B. Many
customers have more than 1 account number. What I would like to do is
change the layout so that I have each customer number listed only once
and have all of the account numbers corresponding to the customer
number in the same row (so for example I would have customer number in
A:1, account number 1 in B:1, account number 2 in B:2, account number 3

in B:3 ect) . Is there a way to do this? Is it possible to do this in

a pvt table? Thanks.

 

[Guest]

Hi

You could do this with subtotals. Subtotal the customers first, and then run
again with the accounts. Make sure that when you run it for the second time
you uncheck 'Replace current subtotals'

Hope this helps.
Andy.

 

[lj]

Use Subtotal in the pvt table? I tried adjusting it in the pvt table
but this didn't seem to adjust to the layout i want. How do I use this
function to get what i'm looking for?

 

[Guest]

Hi

I didn't mean subtotals in the pivot table. I mean just straight subtotals.

Andy.

 

[steven1001]

If your account numbers are really numbers then you could try this.
Assume column A contains the Customer name, column B the customer
number.
Add a column C .. call it 'Ref'

Sort the data by Customer then Cust Number.
In Column C enter the value "1" in cell C2.
In C3 use a formula which says ..
=if(A3=A2,C2+1,1) and copy it down.

The objective is to get data which looks like...
Cust CustNo Ref
Fred 10012 1
Fred 24683 2
Fred 34685 3
Sue 12345 1
Sue 22345 2
Vera 34567 1 etc..

Then create a Pivot table with Ref across the Customer in the ROW area
and REF in the Column area and CustNo in the DATA area. Hide the row
and column totals. This gives...

Sum of CustNo Ref
Cust 1 2 3
Fred 10012 24683 34685
Sue 12345 22345
Vera 34567

... which seems to what you were asking for.

Hope this helps.