How can I combine data that is in 2 rows to 1 row

[albert]

Hello

I have a file that has only 1 data point (name of an institution) in row 1 column 1 and then multiple data points (city of the institution and other results) in row 2 column 1-7. I would like to combine the data in consecutive rows into 1 so I can work (sort results in different columns) with the data. As you can imagine the file has 2000 rows but the data addresses only 1000 institutions.

I Would appreciate any help

thanks

 

[GS]

albert explained :

Hello

I have a file that has only 1 data point (name of an institution) in row 1
column 1 and then multiple data points (city of the institution and other
results) in row 2 column 1-7. I would like to combine the data in consecutive
rows into 1 so I can work (sort results in different columns) with the data.
As you can imagine the file has 2000 rows but the data addresses only 1000
institutions.

I Would appreciate any help

thanks

Try...

Sub MergeRowData()
Dim vData, vResult() 'As Variant
Dim lRows&, lCols&, n&, j&, k& 'As Long
With ActiveSheet.UsedRange
lRows = .Rows.Count
lCols = .Columns.Count
End With 'ActiveSheet.UsedRange
ReDim vResult((lRows - 1), lCols)
For n = 1 To lRows Step 2
vData = Range(Cells(n, 1), Cells(n + 1, lCols))
vResult(j, 0) = vData(1, 1)
For k = 1 To lCols: vResult(j, k) = vData(2, k): Next 'k
j = j + 1
Next 'n
Cells.Clear
Range("A1").Resize(lRows / 2, lCols + 1) = vResult
End Sub

 

[GS]

Minor revision:

Sub MergeRowData()
Dim vData, vResult() 'As Variant
Dim lRows&, lCols&, n&, j&, k& 'As Long
With ActiveSheet.UsedRange
lRows = .Rows.Count
lCols = .Columns.Count
End With 'ActiveSheet.UsedRange

ReDim vResult((lRows / 2) - 1, lCols)

 

[Rick Rothstein]

Here is another macro for you to consider...

Sub CombineData()
Dim X As Long, LastRow
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 2 To LastRow Step 2
Cells(X, "A").Resize(, 7).Copy Cells(X - 1, "B")
Next
Range("H1:H" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Rick Rothstein (MVP - Excel)




"albert" wrote in message

Hello

I have a file that has only 1 data point (name of an institution) in row 1
column 1 and then multiple data points (city of the institution and other
results) in row 2 column 1-7. I would like to combine the data in
consecutive rows into 1 so I can work (sort results in different columns)
with the data. As you can imagine the file has 2000 rows but the data
addresses only 1000 institutions.

I Would appreciate any help

thanks

 

[Rick Rothstein]

Or how about this non-looping method which I think should be very fast...

Sub CombineData()
Dim X As Long, LastRow
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns("A").Insert
Range("B1:B" & LastRow).Copy Range("A2")
Range("C1:C" & (LastRow +
1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

I see I got a bad wordwrap on one of the lines of code. Here is the same
code but using a line continuation character on that line of code so it
wraps and copy/pastes better...

Sub CombineData()
Dim X As Long, LastRow
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns("A").Insert
Range("B1:B" & LastRow).Copy Range("A2")
Range("C1:C" & (LastRow + 1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
End Sub

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

And now I'll remove the extraneous declaration of an unused X variable and
I'll add the data type for the LastRow variable...

Sub CombineData()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns("A").Insert
Range("B1:B" & LastRow).Copy Range("A2")
Range("C1:C" & (LastRow + 1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
End Sub




"Rick Rothstein" wrote in message
I see I got a bad wordwrap on one of the lines of code. Here is the same
code but using a line continuation character on that line of code so it
wraps and copy/pastes better...

Sub CombineData()
Dim X As Long, LastRow
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns("A").Insert
Range("B1:B" & LastRow).Copy Range("A2")
Range("C1:C" & (LastRow + 1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
End Sub

Rick Rothstein (MVP - Excel)

 

[GS]

Rick Rothstein formulated the question :

And now I'll remove the extraneous declaration of an unused X variable and
I'll add the data type for the LastRow variable...

Sub CombineData()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns("A").Insert
Range("B1:B" & LastRow).Copy Range("A2")
Range("C1:C" & (LastRow + 1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
End Sub




"Rick Rothstein" wrote in message
I see I got a bad wordwrap on one of the lines of code. Here is the same
code but using a line continuation character on that line of code so it
wraps and copy/pastes better...

Sub CombineData()
Dim X As Long, LastRow
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns("A").Insert
Range("B1:B" & LastRow).Copy Range("A2")
Range("C1:C" & (LastRow + 1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
End Sub

Rick Rothstein (MVP - Excel)

Rick,
Very slick! Interesting approach I'll be sure to remember for future.