Convert 1 row of data to columns

[Mohoney]

Hi,

My spreadsheet has 4 columns of data but I need to convert just the
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 wan
data for but each stock is listed multiple time per interval
Column D has the actual values of the stocks at the diffferen
intervals

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

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

Please help.

Thanks
Mohone

 

[Executor]

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.

 

[Mohoney]

Hi Wouter,

Thanks...it did help to some extent.

Much obliged.
Mohoney

 

[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

 

[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