Multiple rows and columns in a single row

[Closed]
Report
-
 klempit -
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

5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Assumption
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
4
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
A B C D
1 a b c d
2 a b c d
3 a b c d
4 a b c d
5 a b c d



A B C D
1 a
2 a
3 a
4 a
5 a
6 b
7 b
8 b
9 b
10 b
11 c
12 c
13 c
14 c
15 c

I need to convert like this can you please help me..
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you can do that by copy and paste
Just select the cells in question, and click 'Clear -> Clear Formats' (in the Editing section of MS Office 2007)
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
for me, i had 6 columns of data in my "primary" row and therefore had to use 7 and 6 in the "for" statement. this script will combine two rows until it runs out of rows!
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
formatting wasn't preserved in my post... the transaction ID was in column A and the rest of the data in column B.