Separate one columns data into two columns

[adam.priest]

Hi,

I apologise if this is a really simple request but I just can't get my head around the best way to get some data into the format that I want.

I've run a pivot table report which has given me our reference and then the corresponding client’s details for that reference.

The pivot table has however given me the data all within the one column. E.g. in column A row 1 we have our reference and in column A row 2 we have the clients name.

Column A
1111111
Client 1 Name
22222222
Client 2 Name
3333333
Client 3 Name


Is there any way to take the clients name and put them into column B so the data would then look like this;

Column A Column B

1111111 Client 1 Name
2222222 Client 2 Name
3333333 Client 3 Name


I've originally tried to swap the clients name data into the column row but the pivot table says it can't do this as there are too many fields.

Any help would be most appreciated.

[tom1977]

The pivot table has however given me the data all within the one column. E.g. in column A

Hi pivot table does not work this way (I think that you should format your pivot)
please send the sample

[benishiryo]

hi adam, reformat your data source with this formula first before doing the pivot:
=INDIRECT("A"&COLUMN(A1)+((ROW(A1)-1)*2))

this is assuming your data starts from A1. then drag the formula down & across

[adam.priest]

It's going to be quite hard for me to re-format the pivot simply because it's a pre set report which can be used to gather information on a number of different criteria.

The data it's picking it up from is 56 columns wide and has 25,000+ rows in it.

[adam.priest]

I've managed to pull a simple formula together to sort this one out.

Thanks for all your help guys.