Pvt table

[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.

 

[Peo Sjoblom]

Yes and no, you could apply a pseudo pivot table, select the table, apply
the pivot table,
drag the customer field to the row, then drag the account field also to the
row, then drag
the customer field to the data, click finish. Now right click in the pivot
table and select entire table, copy
and paste it to a new sheet, apply autofilter on the first row, click the
dropdown in the first column and select custom, select

does not contain

and type

total

in the adjacent box, click OK, now select the first 2 columns in the
visible table, press F5, select special and visible cells only, copy and
paste somewhere else. That will give you your table in less than 5 minutes


--
Regards,

Peo Sjoblom

Portland, Oregon

 

[flummi]

Assume the following layout (hope the formatting doesn't get messed
up):

custno. acctno. custno acct1 acct2 acct3 acct4 acct5 acct6
111111 11 111111 11
222222 22 222222 22 23 24
222222 23
222222 24
333333 33 333333 33 34 35 36 37 38
333333 34
333333 35
333333 36
333333 37
333333 38
444444 44 444444 44 45
444444 45

Column A2 down is your cuctomer no.
Column B2 down is your account no.
The table is sorted on A and B.

In C2:I2 use the following formulae (assuming a maximum of 6 accout
numbers per customer):

=IF(A2<>A1;A2;"")
=IF(C2<>"";B2;"")
=IF($A2<>$A1;IF($A3=$A2;$B3;"");"")
=IF($A2<>$A1;IF($A4=$A2;$B4;"");"")
=IF($A2<>$A1;IF($A5=$A2;$B4;"");"")
=IF($A2<>$A1;IF($A6=$A2;$B6;"");"")
=IF($A2<>$A1;IF($A7=$A2;$B7;"");"")

Copy C2:I2 down as required. That will show the above picture.
Highlight the row headers in row 1 and turn on autofilter.
If you click C1 and select "nonblanks" it will show the picture below:

custno. acctno. custno acct1 acct2 acct3 acct4 acct5 acct6
111111 11 111111 11
222222 22 222222 22 23 24
333333 33 333333 33 34 35 36 37 38
444444 44 444444 44 45

If you want you can hide columns A and B. Please bear in mind that for
this to work correctly the table must be sorted on A and B!

Hope this makes sense?

Hans

 

[lj]

I tried this and am having problems with I select the first 2 columns
and hit F5 and select "visible cells only" I get a message that says
the table is already showing only visible cells. Any suggestions?

 

[Peo Sjoblom]

Did you add the autofilter and the help column first

--
Regards,

Peo Sjoblom

Portland, Oregon