Solved Excel - Split data from multiple columns into separate rows

February 20, 2018 at 12:18:44
Specs: Windows 10
Hello,

I have a table of orders with various fees, and we're needing the four columns of fees split into separate rows, including description as formatted in my example below. Is all of this able to be done by a macro? Thank you in advance!

	A	B	C	D	E		F	G	H	I
1	ORD#	CUST#	Fee 1	Fee 2	SUBTOTAL1+2	Fee 3	Fee 4	TOTAL	DATE
2	Ord1	Cust1	10		10		20	15	45	Date1
3	Ord2	Cust2		8.99	8.99				8.99	Date2
4	Ord3	Cust3	5	2	7			10	17	Date3
5	Ord4	Cust4	1.5	7	8.5		3.45	8	19.95	Date4


	A	B	C		D	E		F
1	ORD#	CUST#	FEE TYPE	FEE AMT	FEE DESCRIP	DATE
2	Ord1	Cust1	Fee 1		10	Fee 1 - $10.00	Date1
3	Ord1	Cust1	Fee 3		20	Fee 3 - $20.00	Date1
4	Ord1	Cust1	Fee 4		15	Fee 4 - $15.00	Date1
5	Ord2	Cust2	Fee 2		8.99	Fee 2 - $8.99	Date2
6	Ord3	Cust3	Fee 1		5	Fee 1 - $5.00	Date3
7	Ord3	Cust3	Fee 2		2	Fee 2 - $2.00	Date3
8	Ord3	Cust3	Fee 4		10	Fee 4 - $10.00	Date3
9	Ord4	Cust4	Fee 1		1.5	Fee 1 - $1.50	Date4
10	Ord4	Cust4	Fee 2		7	Fee 2 - $7.00	Date4
11	Ord4	Cust4	Fee 3		3.45	Fee 3 - $3.45	Date4
12	Ord4	Cust4	Fee 4		8	Fee 4 - $8.00	Date4


message edited by fl00


See More: Excel - Split data from multiple columns into separate rows


#1
February 20, 2018 at 12:28:42
Please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
February 20, 2018 at 12:30:30
Sorry, just fixed it! Thanks for the quick follow up.

Report •

#3
February 21, 2018 at 13:28:36
✔ Best Answer
Give this a try.

The following code assumes that the original data starts in A1 on Sheet1. The code would need to be modified if the original data is stored anywhere else.

The code adds a new sheet at the end of the workbook, copies the data from Sheet1 and then "reconfigures" the new table based on the format that you posted. The output will be found on the new sheet.

Let me know if the code does what you want.

Option Explicit
Sub FeeTable()

Dim lastRw As Long, nxtRw As Long
Dim rw As Long, col As Long
Dim newWs As Worksheet

Application.ScreenUpdating = False

'Copy Sheet1 to end of worksheets
    Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
    Set newWs = Sheets(Sheets.Count)
    
'Set up table to have 4 rows for each Customer
'Delete unused Columns
    With newWs
      lastRw = .Cells(Rows.Count, 1).End(xlUp).Row
        For rw = lastRw To 2 Step -1
          Rows(rw).Copy
          Rows(rw + 1 & ":" & rw + 3).Insert
        Next
          .Columns("E").Delete
          .Columns("F:G").Delete
    End With
    
''Fill in new table from data on Sheet1

'Initialize Next Row variable
  nxtRw = 1

'Loop through Sheet1 Rows and Columns
 With Sheets(1)
  lastRw = .Cells(Rows.Count, 1).End(xlUp).Row
    For rw = 2 To lastRw
     For col = 3 To 7
      nxtRw = nxtRw + 1
      
'Get Fee Type and Amount
       If .Cells(rw, col) <> "" And .Cells(1, col) Like "*Fee*" Then
        newWs.Cells(nxtRw, 3) = .Cells(1, col)
        newWs.Cells(nxtRw, 4) = .Cells(rw, col)
'Build Fee Description string
        newWs.Cells(nxtRw, 5) = newWs.Cells(nxtRw, 3) & " - " & Format(newWs.Cells(nxtRw, 4), "Currency")
       End If
'Compensate for SUBTOTAL column
        If .Cells(1, col) Like "*SUBTOTAL*" Then
         nxtRw = nxtRw - 1
        End If
     Next
    Next
 End With
 
''Clean up New Worksheet

'Delete Rows with invalid Fee Type
'Delete Rows with invalid Fee Type
 With newWs
   For rw = 17 To 2 Step -1
    If Not .Cells(rw, 3) Like "*Fee*" Then .Cells(rw, 3).EntireRow.Delete
   Next
  
'Set Column Headings
    .Cells(1, 3) = "FEE Type"
    .Cells(1, 4) = "FEE AMT"
    .Cells(1, 5) = "FEE DESCRIP"
    .Columns("A:F").AutoFit
 End With
 
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
February 22, 2018 at 14:26:28
Hi DerbyDad03,

Thank you for the quick reply, we appreciate the help! Yes, we can stick with your assumptions when running the code on our data.

I've tried the code on different sets of data, and it works perfectly for my original example data of four orders, but if I add any additional orders, the macro will output unnecessary additional lines.

	A	B	C	D	E		F	G	H	I
1	ORD#	CUST#	Fee 1	Fee 2	SUBTOTAL 1+2	Fee 3	Fee 4	TOTAL	DATE
2	Ord1	Cust1	10		10		20	15	45	Date1
3	Ord2	Cust2		8.99	8.99				8.99	Date2
4	Ord3	Cust3	5	2	7			10	17	Date3
5	Ord4	Cust4	1.5	7	8.5		3.45	8	19.95	Date4
6	Ord5	Cust5		1	1				1	Date5
7	Ord6	Cust6	2		2		5		7	Date6

	A	B	C		D	E		F
1	ORD#	CUST#	FEE Type	FEE AMT	FEE DESCRIP	DATE
2	Ord1	Cust1	Fee 1		10	Fee 1 - $10.00	Date1
3	Ord1	Cust1	Fee 3		20	Fee 3 - $20.00	Date1
4	Ord1	Cust1	Fee 4		15	Fee 4 - $15.00	Date1
5	Ord2	Cust2	Fee 2		8.99	Fee 2 - $8.99	Date2
6	Ord3	Cust3	Fee 1		5	Fee 1 - $5.00	Date3
7	Ord3	Cust3	Fee 2		2	Fee 2 - $2.00	Date3
8	Ord3	Cust3	Fee 4		10	Fee 4 - $10.00	Date3
9	Ord4	Cust4	Fee 1		1.5	Fee 1 - $1.50	Date4
10	Ord4	Cust4	Fee 2		7	Fee 2 - $7.00	Date4
11	Ord4	Cust4	Fee 3		3.45	Fee 3 - $3.45	Date4
12	Ord4	Cust4	Fee 4		8	Fee 4 - $8.00	Date4
13*	Ord5	Cust5			1			Date5
14	Ord5	Cust5	Fee 2		1	Fee 2 - $1.00	Date5
15*	Ord5	Cust5			1			Date5
16*	Ord5	Cust5			1			Date5
17	Ord6	Cust6	Fee 1		2	Fee 1 - $2.00	Date6
18*	Ord6	Cust6	2			5		Date6
19	Ord6	Cust6	Fee 3		5	Fee 3 - $5.00	Date6
20*	Ord6	Cust6	2			5		Date6

We can easily delete these (*) rows manually, but any idea on what is causing those lines to be generated?


Report •

Ask Question