Rearranging the layout of data

[Pete]

I have a database report which has been exported into Excel. I want to
rearrange the presentation of the data. Currently the data is presented as
follows:

COSTCENTRE1 ACCOUNT CODE 1 VALUE
COSTCENTRE1 ACCOUNT CODE 2 VALUE
COSTCENTRE1 ACCOUNT CODE 3 VALUE
COSTCENTRE2 ACCOUNT CODE 1 VALUE
COSTCENTRE2 ACCOUNT CODE 2 VALUE
COSTCENTRE3 ACCOUNT CODE 1 VALUE
COSTCENTRE3 ACCOUNT CODE 2 VALUE
COSTCENTRE3 ACCOUNT CODE 3 VALUE

Note: Each cost centre may have up to three account codes.

I would like to present the data like this:

ACCOUNT CODE 1 ACCOUNT CODE 2 ACCOUNT CODE 3
COSTCENTRE1 VALUE VALUE VALUE
COSTCENTRE2 VALUE VALUE
COSTCENTRE3 VALUE VALUE VALUE

How can this be done on Excel?

Thanks.

[Dave Peterson]

Are these values numeric?

If yes, then you can use a pivottable.

Add a single row of headers to your range.

Select that range (include the header row)

Data|pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the costcenter header button to the row field
drag the accountcode header button to the column field
drag the value header button to the data field
(if you see "Count of Value", then double click on that button and change it to
Sum)

And finish up.

This will only work if the values are numeric.


Pete wrote:
>
> I have a database report which has been exported into Excel. I want to
> rearrange the presentation of the data. Currently the data is presented as
> follows:
>
> COSTCENTRE1 ACCOUNT CODE 1 VALUE
> COSTCENTRE1 ACCOUNT CODE 2 VALUE
> COSTCENTRE1 ACCOUNT CODE 3 VALUE
> COSTCENTRE2 ACCOUNT CODE 1 VALUE
> COSTCENTRE2 ACCOUNT CODE 2 VALUE
> COSTCENTRE3 ACCOUNT CODE 1 VALUE
> COSTCENTRE3 ACCOUNT CODE 2 VALUE
> COSTCENTRE3 ACCOUNT CODE 3 VALUE
>
> Note: Each cost centre may have up to three account codes.
>
> I would like to present the data like this:
>
> ACCOUNT CODE 1 ACCOUNT CODE 2 ACCOUNT CODE 3
> COSTCENTRE1 VALUE VALUE VALUE
> COSTCENTRE2 VALUE VALUE
> COSTCENTRE3 VALUE VALUE VALUE
>
> How can this be done on Excel?
>
> Thanks.

--

Dave Peterson

[Dave O]

I mocked up your current data's format into cells A1:C8. In F1, G1,
and H1 I entered your ACCOUNT CODE headers; in E2, E3, and E4 I entered
row labels (CostCentre1 etc). In cell F2 I entered this formula
=SUMPRODUCT(--($E2=$A$1:$A$8),--(F$1=$B$1:$B$8),$C$1:$C$8)
.... which you can copy and paste into the other cells in that grid.

This formula returns a zero for COSTCENTRE2/ACCOUNT CODE 3. If you
desire you can revise the formula with an IF that enters a blank when
the value is zero, or you can conditionally format the cells with white
font when the value is zero.

[Pete]

Guys, thanks very much for your help. I've tried doing as instructed and
these PivotTables will do the job. Unfortunately at the moment it's reporting
the wrong values, but I'm sure with a bit of playing a round I'll get it
sussed.

Thanks again,
Pete