Combine multiple rows into one.
 

I am trying to combine multiple rows into one. The data is in five columns.
The main identifiers are the first few characters of column A (F75 through
F77 with varying number of occurances). I would like to extract each grouping
(first F75 row through F77 row and so on) and place into one row. A space in
between data would be fine as I imagine I could do a text to column delimit
later. Here is an example of what it looks like. I am truncating the data for
ease of viewing.

Thank you in advance.

F75 00141 09022 0
F75
F75 0712 0
F76 08 010 0
F76 09022009 0
F77 0
F75 00141 090 0
F75 24 2
F75 090 01
F76 08 01 0
F76 090 0
F76 08 01 0
F76 09 0
F77 0

Combine multiple rows into one.
 

Try this macro

Sub CombineRows()

Set OldSht = Sheets("sheet1")
Set NewSht = Sheets("sheet2")

With OldSht
NewRowCount = 0
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
OldID = ""
For OldRowCount = 1 To LastRow
NewId = .Range("A" & OldRowCount)
LastCol = .Cells(OldRowCount, Columns.Count).End(xlToLeft).Column
If LastCol 1 Then
If NewId < OldID Then
NewRowCount = NewRowCount + 1
NewSht.Range("A" & NewRowCount) = NewId
OldID = NewId
NewColCount = 2
End If

For ColCount = 2 To LastCol
NewSht.Cells(NewRowCount, NewColCount) = .Cells(OldRowCount,
ColCount)
NewColCount = NewColCount + 1
Next ColCount
End If
Next OldRowCount

End With

End Sub


"Linnaeus" wrote:

I am trying to combine multiple rows into one. The data is in five columns.
The main identifiers are the first few characters of column A (F75 through
F77 with varying number of occurances). I would like to extract each grouping
(first F75 row through F77 row and so on) and place into one row. A space in
between data would be fine as I imagine I could do a text to column delimit
later. Here is an example of what it looks like. I am truncating the data for
ease of viewing.

Thank you in advance.

F75 00141 09022 0
F75
F75 0712 0
F76 08 010 0
F76 09022009 0
F77 0
F75 00141 090 0
F75 24 2
F75 090 01
F76 08 01 0
F76 090 0
F76 08 01 0
F76 09 0
F77 0

Combine multiple rows into one.
 

Thank you very much. I will make the modifications and see if that does the
trick.

"Joel" wrote:

Try this macro

Sub CombineRows()

Set OldSht = Sheets("sheet1")
Set NewSht = Sheets("sheet2")

With OldSht
NewRowCount = 0
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
OldID = ""
For OldRowCount = 1 To LastRow
NewId = .Range("A" & OldRowCount)
LastCol = .Cells(OldRowCount, Columns.Count).End(xlToLeft).Column
If LastCol 1 Then
If NewId < OldID Then
NewRowCount = NewRowCount + 1
NewSht.Range("A" & NewRowCount) = NewId
OldID = NewId
NewColCount = 2
End If

For ColCount = 2 To LastCol
NewSht.Cells(NewRowCount, NewColCount) = .Cells(OldRowCount,
ColCount)
NewColCount = NewColCount + 1
Next ColCount
End If
Next OldRowCount

End With

End Sub


"Linnaeus" wrote:

I am trying to combine multiple rows into one. The data is in five columns.
The main identifiers are the first few characters of column A (F75 through
F77 with varying number of occurances). I would like to extract each grouping
(first F75 row through F77 row and so on) and place into one row. A space in
between data would be fine as I imagine I could do a text to column delimit
later. Here is an example of what it looks like. I am truncating the data for
ease of viewing.

Thank you in advance.

F75 00141 09022 0
F75
F75 0712 0
F76 08 010 0
F76 09022009 0
F77 0
F75 00141 090 0
F75 24 2
F75 090 01
F76 08 01 0
F76 090 0
F76 08 01 0
F76 09 0
F77 0