How do i merge rows that have similar colums of data into on

[joeizbored]

Hello, I am new at this so I dont know where to start. Basically I have a spreadsheet with 10,000 rows/record in it. Each row has several items of data in it.

A...B...C
n1..s1..data1
n1..s1..data2
n1..s1..data3
n2..s2..data1
n2..s2..data2
n2..s2..data3
nX..sY..data1
nX..sY..data2
nX..sY..data3

How can i make this into one row? Where data2 and data3 are moved into COLUMN D and E on the first row and then delete the rows they are in. Where it looks like this

A...B...C......D......E
n1..s1..data1..data2..data3
n2..s2..data1..data2..data3
nX..sY..data1..data2..data3


Thanks for any suggestions and help....
This message was edited by joeizbored on 2002-09-19 09:10

 

[Ted Thomas]

The following assumes that there are no numeric values in column C.

Manual method :-

In D1 enter =IF(E1<>1,C2,1) and fill down

In E1 enter =IF(A1&B1=A3&B3,C3,1) and fill down

Select columns D & E, Copy>PasteSpecial>Values

Go to Edit>GoTo>Special>Constants>Numbers (un-check Text/Logicals/Errors)

Edit>Delete>EntireRow


Macro :-

Dim rng As Range
Set rng = Range([A1], [A65536].End(xlUp)).Offset(0, 3)
With rng
.FormulaR1C1 = "=IF(RC[1]<>1,R[1]C[-1],1)"
.Offset(0, 1).FormulaR1C1 = "=IF(RC[-4]&RC[-3]=R[2]C[-4]&R[2]C[-3],R[2]C[-2],1)"
.Resize(, 2).Value = .Resize(, 2).Value
.SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
End With