Data conversion

[H. Eilers]

What is the best way to convert 3-column data into multi-
column data.

Example: The data file has three columns that look like:

1516,1,59
1517,1,59
1518,1,65
1519,1,58
1520,1,57
1516,2,56
1517,2,57
1518,2,68
1519,2,58
1520,2,59
1516,3,59
1517,3,59
1518,3,65
1519,3,58
1520,3,57

What I want is:

1516,59,56,59
1517,59,57,59
1518,65,68,65
1519,58,58,58
1520,57,59,57

The real data file would have 512 rows and anywhere
between 50 and 200 columns.

Any suggestions would be appreciated.

Thanks.

Hergen

 

[Frank Kabel]

Hi
do you always have 3 values for each number or does this depend as
well. One way withou using a macro could be:
- copy the data for all rows with a '1' as second column to a new sheet
- remove the column with the one in it. Now you should have a column A
+ B
- enter the following array formula in cell C1 (entered with
CTRL+SHIFT+ENTER):
=INDEX('sheet1'!$C$1:$C$100,MATCH(1,('sheet1'!$A$1:$A$100=$A1)*('sheet1
'!$B$1:$B$100=COLUMN(B:b)),0))
and copy this formula down / to the right

afterwards copy the formula data and insert it with 'Edit - Paste
Special - Values' to remove the formulas