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

joeizbored

New Member
Joined
Sep 18, 2002
Messages
4
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Ted Thomas

New Member
Joined
Jul 16, 2002
Messages
3
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
 

Forum statistics

Threads
1,144,274
Messages
5,723,440
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top