Convert 1 row of data to columns

Hi,

My spreadsheet has 4 columns of data but I need to convert just the 1 column into a row.

Example:

Column A has the date
Column B has the 30 minute intervals starting from 09:00 to 17:00
Column C has the 5 different stocks (ABL, AGL; ANG; BAW;SLM) I want data for but each stock is listed multiple time per interval
Column D has the actual values of the stocks at the diffferent intervals

I need to convert column C into 5 columns but retain the other columns data applicable to the various stocks so its easy to view in a table-like format.

Using copy/paste/tranpose feauture in Excel is not my solution so it has to be an array formula (I think!).

Please help.

Thanks
Mohoney

Hi Mohony,

I suggest the following appraoch:

Using columns F:J for the split info.
F1 = "ABL"
G1 = "AGL"
and so on

For F1 use the formula
'If($C2=F$1,F$1,"")

And copy this formula to the columns F:J for als the used rows.

HTH.

Wouter.

Hi Wouter,

Thanks...it did help to some extent.

Much obliged.
Mohoney

"Executor" wrote:

> Hi Mohony,
>
> I suggest the following appraoch:
>
> Using columns F:J for the split info.
> F1 = "ABL"
> G1 = "AGL"
> and so on
>
> For F1 use the formula
> 'If($C2=F$1,F$1,"")
>
> And copy this formula to the columns F:J for als the used rows.
>
> HTH.
>
> Wouter.
>
>

Hi Wouter,

Perhaps if I give you an example of what I have and what I require:

Currently, my SS displays data as:

A B C D
date interval code Quantity
May 11 2005 09h00-09h10 AGL 5750
May 11 2005 09h10-09h20 AGL 800
May 11 2005 09h20-09h30 AGL 10121
May 12 2005 09h00-09h10 AGL 3333
May 12 2005 09h10-09h20 AGL 1421
May 12 2005 09h20-09h30 AGL 8364
May 11 2005 09h00-09h10 BAW 16935
May 11 2005 09h10-09h20 BAW 6000
May 11 2005 09h20-09h30 BAW 12225
May 12 2005 09h00-09h10 BAW 22527
May 12 2005 09h10-09h20 BAW 34858
May 12 2005 09h20-09h30 BAW 21659

.....and what I require is.....

A B C D
date interval AGL BAW
May 11 2005 09h00-09h10 5750 16935
May 11 2005 09h10-09h20 800 6000
May 11 2005 09h20-09h30 10121 12225
May 12 2005 09h00-09h10 3333 22527
May 12 2005 09h10-09h20 1421 34858
May 12 2005 09h20-09h30 8364 21659

Hope you can assist with this.

Much obliged,
Mohoney

Hi Wouter,

Perhaps if I give you an example of what I have and what I require:

Currently, my SS displays data as:

A B C
D
date interval code Quantity
May 11 2005 09h00-09h10 AGL 5750
May 11 2005 09h10-09h20 AGL 800
May 11 2005 09h20-09h30 AGL 10121
May 12 2005 09h00-09h10 AGL 3333
May 12 2005 09h10-09h20 AGL 1421
May 12 2005 09h20-09h30 AGL 8364
May 11 2005 09h00-09h10 BAW 16935
May 11 2005 09h10-09h20 BAW 6000
May 11 2005 09h20-09h30 BAW 12225
May 12 2005 09h00-09h10 BAW 22527
May 12 2005 09h10-09h20 BAW 34858
May 12 2005 09h20-09h30 BAW 21659

......and what I require is.....

A B C
D
date interval AGL BAW
May 11 2005 09h00-09h10 5750 16935
May 11 2005 09h10-09h20 800 6000
May 11 2005 09h20-09h30 10121 12225
May 12 2005 09h00-09h10 3333 22527
May 12 2005 09h10-09h20 1421 34858
May 12 2005 09h20-09h30 8364 21659

Hope you can assist with this.

Much obliged,
Mohoney
-------------------------------------------------------------------------

"Executor" wrote:

> Hi Mohony,
>
> I suggest the following appraoch:
>
> Using columns F:J for the split info.
> F1 = "ABL"
> G1 = "AGL"
> and so on
>
> For F1 use the formula
> 'If($C2=F$1,F$1,"")
>
> And copy this formula to the columns F:J for als the used rows.
>
> HTH.
>
> Wouter.
>
>