Previous Topic Next Topic

Multiple rows and columns in a single row

Closed
nshah - Feb 19, 2010 at 01:47 AM
 klempit - Nov 28, 2011 at 11:43 PM
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
Related:

5 responses

Answer 1 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 19, 2010 at 02:58 AM
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
Answer 2 / 5
sha
Jun 7, 2010 at 02:21 AM
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
1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 7, 2010 at 06:04 AM
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
0
thomas
Oct 8, 2010 at 04:16 AM
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..
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 17, 2010 at 01:00 PM
you can do that by copy and paste
0
Answer 3 / 5
Jon
Aug 5, 2010 at 02:45 PM
Just select the cells in question, and click 'Clear -> Clear Formats' (in the Editing section of MS Office 2007)
0
Answer 4 / 5
klempit
Nov 28, 2011 at 11:11 AM
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
0
klempit
Nov 28, 2011 at 11:12 AM
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!
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
klempit
Nov 28, 2011 at 11:40 PM
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
0
klempit
Nov 28, 2011 at 11:43 PM
formatting wasn't preserved in my post... the transaction ID was in column A and the rest of the data in column B.
0