reformatting data to create a table

[mjb]

Is there an easy way to reformat data as follows

Jan Mike 200
Jan Dave 300
Jan Jim 400
Jan Ken 500
Feb Mike 600
Feb Dave 800
Feb Jim 150
Feb Ken 900

to

________Mike____Dave____Jim____Ken
Jan_____200_____300_____400____500
Feb_____600_____800_____150____900
This message was edited by mjb on 2002-03-01 05:50
This message was edited by mjb on 2002-03-01 05:53

 

[Ian Mac]

On 2002-03-01 05:49, mjb wrote:
Is there an easy way to reformat data as follows

Jan Mike 200
Jan Dave 300
Jan Jim 400
Jan Ken 500
Feb Mike 600
Feb Dave 800
Feb Jim 150
Feb Ken 900

to

________Mike____Dave____Jim____Ken
Jan_____200_____300_____400____500
Feb_____600_____800_____150____900
This message was edited by mjb on 2002-03-01 05:50
This message was edited by mjb on 2002-03-01 05:53

Assuming that that data you posted goes from A2:C9 and the number ARE numbers, in A18 put Jan and copy down i.e. Feb, Mar etc.
In B17 put your first name and fill the rest in along that row. (these might seem like strange places, it's just where put them to test),
In b18 put:

=SUMPRODUCT(($A$2:$A$8=$A18)*($B$2:$B$8=B$17)*($C$2:$C$8))

Using the $'s in exactly the same place as above, you should be able to drag right then down this formula.

Any prob give me a shout.

Ian Mac

 

[Mark W.]

Add column headers to your data list and create a PivotTable.