multiple rows and columns in a single row Closed

Question

Hello,

I have some data in excel sheet. Here is the example.
AAA         10     0      0     0    0    0     0    0    0    0    0    0
               0      100   0     0    0    0     0    0    0    0    0    0
bbb          20     0      0     0    0    0     0    0    0    0    0    0
               0      200   0     0    0    0     0    0    0    0    0    0
               0      0      2000     0    0     0     0    0    0    0    0    
ccc          30     0     0     0    0    0     0    0    0    0    0      0
               0      0      0      300    0    0     0    0    0    0    0    0
               0      0      0     0    0   3000  0     0    0    0    0    0  
               0      0   0     0    0    0     0    0    0    3000    0    0
               0      0      0     0    0   0  0     0    0    0    0   35  


Now I want All the data in Following Format in Excel

AAA     10    100    0               0          0               0      0      0        0       0          0       0
BBB     20    200    2000           0          0               0     0     0         0       0           0       0
CCC    30     0       0             300         3000          0     0     3000    0       3000      0       35


Thankyou

rizvisa1 · Accepted Answer

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

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

sha · Answer

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

Jon · Answer

Just select the cells in question, and click 'Clear -> Clear Formats' (in the Editing section of MS Office 2007)

klempit · Answer

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

klempit · Answer

Just a little more follow-up. 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 oddly-formatted 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

Multiple rows and columns in a single row

5 responses

Similar discussions

Subscribe To Our Newsletter!