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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

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,181,580
Messages
5,930,718
Members
436,757
Latest member
Marijn

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