Multiple rows and columns in a single row
[Closed]
Report
nshah

klempit 
klempit 
Related:
 Multiple rows and columns in a single row
 Convert multiple rows and columns to one colu ✓  Forum  Excel
 Repeat multiple rows in excel  HowTo  Excel
 Excel: Sample Macro to Insert Multiple Rows  HowTo  Excel
 Excel  One macro for multiple row tasks  HowTo  Excel
 Multiple rows in one cell excel  HowTo  Excel
5 replies
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Assumption
1. Data starts at row 2
2. Col 2 will always have data
1. Data starts at row 2
2. Col 2 will always have data
Sub combine() Dim lThisRow As Long Dim lSectionStart As Long Dim lSectionEnd As Long Dim iMaxCol As Integer Dim iCount As Integer iMaxCol = 13 lThisRow = 2 Do While (Cells(lThisRow, 2) <> "") If (Cells(lThisRow + 1, 1) = "") Then For iCount = 2 To iMaxCol Cells(lThisRow, iCount) = Cells(lThisRow, iCount) + Cells(lThisRow + 1, iCount) Next Rows(lThisRow + 1).Delete If (Cells(lThisRow + 1, 1) <> "") Then lThisRow = lThisRow + 1 ElseIf (Cells(lThisRow + 1, 2) = "") Then lThisRow = lThisRow + 1 End If Else lThisRow = lThisRow + 1 End If Loop End Sub
sha
100050 abcd:
100050 efghi;
100050 jklmnop:
100051 abcde:
100051 fghijkl;
100051 mnopqr
i need above format in excel sheet like below this
100050 abcd:efghi;jklmnop:
100051 abcde:fghijkl;mnopqr
100050 efghi;
100050 jklmnop:
100051 abcde:
100051 fghijkl;
100051 mnopqr
i need above format in excel sheet like below this
100050 abcd:efghi;jklmnop:
100051 abcde:fghijkl;mnopqr
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Assumption
1. Data starts at row 2
2. Col 1 will always have data
3. Col2 will always have data
4. data is sorted based on column B
1. Data starts at row 2
2. Col 1 will always have data
3. Col2 will always have data
4. data is sorted based on column B
Sub combine2() Dim lThisRow As Long Dim iMaxCol As Integer Dim iCount As Integer iMaxCol = 2 lThisRow = 2 Do While (Cells(lThisRow, 1) <> "") If (Cells(lThisRow, 1) = Cells(lThisRow + 1, 1) ) Then Cells(lThisRow, iMaxCol ) = Cells(lThisRow, iMaxCol ) & " " & Cells(lThisRow + 1, iMaxCol ) Rows(lThisRow + 1).Delete Else lThisRow = lThisRow + 1 End If Loop End Sub
Jon
Just select the cells in question, and click 'Clear > Clear Formats' (in the Editing section of MS Office 2007)
klempit
i modified the script above to combine two rows without thinking too hard about the content... you need to set the number of columns to be moved in the declarations and where to start in the "for" statement.
Sub combine()
Dim lThisRow As Long
Dim lSectionStart As Long
Dim lSectionEnd As Long
Dim iMaxCol As Integer
Dim iCount As Integer
iMaxCol = 15
lThisRow = 2
Do While (Cells(lThisRow, 2) <> "")
For iCount = 7 To iMaxCol
Cells(lThisRow, iCount) = Cells(lThisRow + 1, (iCount  6))
Next
Rows(lThisRow + 1).Delete
If (Cells(lThisRow + 1, 1) <> "") Then
lThisRow = lThisRow + 1
ElseIf (Cells(lThisRow + 1, 2) = "") Then
lThisRow = lThisRow + 1
End If
Loop
End Sub
Sub combine()
Dim lThisRow As Long
Dim lSectionStart As Long
Dim lSectionEnd As Long
Dim iMaxCol As Integer
Dim iCount As Integer
iMaxCol = 15
lThisRow = 2
Do While (Cells(lThisRow, 2) <> "")
For iCount = 7 To iMaxCol
Cells(lThisRow, iCount) = Cells(lThisRow + 1, (iCount  6))
Next
Rows(lThisRow + 1).Delete
If (Cells(lThisRow + 1, 1) <> "") Then
lThisRow = lThisRow + 1
ElseIf (Cells(lThisRow + 1, 2) = "") Then
lThisRow = lThisRow + 1
End If
Loop
End Sub
klempit
Just a little more followup. I was also trying to get Authorize.net transaction detail into my excel spreadsheet. When you copy and paste into Excel, you get a multi line entry for each transaction record, with the transaction ID in one column and the rest of the detail in the next. This variation does a nice job of putting these oddlyformatted transactions into one row. Hope it helps someone  this thread sure helped me.
Here's the data format I had to start (in two columns, hundreds of rows):
9991476482
Settled Successfully
3/9/2011 5:03
Jones, James
A
XXXX3456
USD 14.95
3/9/2011 19:40
USD 14.95
the following macro puts all data for each record on one row, and assumes you have a blank row 1.
Sub combine()
Dim lThisRow As Long
Dim lSectionStart As Long
Dim lSectionEnd As Long
Dim iCount As Integer
lThisRow = 2
iCount = 1
Do While ((Cells(lThisRow, 2) <> "") Or (Cells(lThisRow, 1) <> ""))
Do While (Cells(lThisRow + 1, 2) <> "")
Cells(lThisRow, iCount + 1) = Cells(lThisRow + 1, 2)
iCount = iCount + 1
Rows(lThisRow + 1).Delete
Loop
iCount = 1
lThisRow = lThisRow + 1
Loop
End Sub
Here's the data format I had to start (in two columns, hundreds of rows):
9991476482
Settled Successfully
3/9/2011 5:03
Jones, James
A
XXXX3456
USD 14.95
3/9/2011 19:40
USD 14.95
the following macro puts all data for each record on one row, and assumes you have a blank row 1.
Sub combine()
Dim lThisRow As Long
Dim lSectionStart As Long
Dim lSectionEnd As Long
Dim iCount As Integer
lThisRow = 2
iCount = 1
Do While ((Cells(lThisRow, 2) <> "") Or (Cells(lThisRow, 1) <> ""))
Do While (Cells(lThisRow + 1, 2) <> "")
Cells(lThisRow, iCount + 1) = Cells(lThisRow + 1, 2)
iCount = iCount + 1
Rows(lThisRow + 1).Delete
Loop
iCount = 1
lThisRow = lThisRow + 1
Loop
End Sub