Forum Discussion
Append portion of even number rows to odd number rows
Below are a few sample rows from an Excel 2019
file, created from a .CSV file.
The Excel file contains records of the loan
payment transactions for an auto loan.
Each payment transaction takes up two Excel rows:
one row for the principal amount, followed
by a second row for the interest amount.
I have two questions:
1.) How can I easily modify the file
so that each payment transaction occupies only one row?
Then instead of
03/15/18 PRINCIPAL 607.89
03/15/18 INTEREST 82.24
I would see
03/15/18 Principal 607.89 Interest 82.24
in Row 1 - and Row 2 would be gone.
2.) Why does the vertical line between column C and column D not
run all the way from the top row to the bottom row? And can I
"fix" that?
10 Replies
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
I would argue that 'Principal' and 'Interest' should be part of the header row, not the body as requested. I think Power Query would be my preferred solution for the problem as so far described. My preference, though, would be to open the CSV file using PQ so the blue table would never appear in Excel.
I agree that Power Query is the best suggestion so far because with Power Query there's no need to load the unprocessed data into the worksheet and Power Query doesn't require Office365. It's available in Excel 2013 and maybe earlier versions. In case that the questioner wants to return 'principal' and 'interest' only as headers it should be possible to adapt the query.
=MAKEARRAY(COUNTA(A1:INDEX(A:A,COUNTA(A:A)))/2,COUNTA(A1:C1),LAMBDA(r,c,INDEX(A:C,r*2-1,c)))=MAKEARRAY(COUNTA(A1:INDEX(A:A,COUNTA(A:A)))/2,COUNTA(A1:C1),LAMBDA(r,c,INDEX(A:C,r*2,c)))Without Power Query an alternative with Office 365 or Excel 2021 or Excel for the web could be these formulas which are in cells E2 and H2 in the example.
Excel 365 has functionality that enables solutions which are not remotely similar to those of the traditional spreadsheet. Due to the regulatity of your input data, the formula
= LET( hdr, {"Date","Principal","Interest"}, amt, WRAPROWS(amount, 2), dt, TAKE(WRAPROWS(date, 2),,1), VSTACK(hdr, HSTACK(dt, amt)) )will generate the output table
A couple of extra statements and it would generate the running balance and stack that as part of the output table.
Whilst I think of it, removing the background white fill will cause the grid to show. Then again View and uncheck 'Grid' would also provide consistency.
Sub principal() Dim i, j, k, l As Long Range("G:L").Clear k = 1 l = 1 j = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To j Step 2 Cells(k, 7).Value = Cells(l, 1).Value Cells(k, 8).Value = Cells(l, 3).Value Cells(k, 9).Value = Cells(l, 5).Value l = l + 2 Cells(k, 10).Value = Cells(i, 1).Value Cells(k, 11).Value = Cells(i, 3).Value Cells(k, 12).Value = Cells(i, 5).Value k = k + 1 Next i End SubYou can try this code. In the attached file you can click the button in cell N2 to run the macro.
- Ok, my inexperience has already caught up with me.
How do I get the contents of your cell N2 into a specific cell in my Excel file?The easiest possibility would be if you copied your data and pasted it into the file that i sent you in an earlier reply.
If you want the code in your file you have to enter the code into a module. In your worksheet you can press alt+F11 in order to open the VBA editor. Then you can select insert (Einfügen in german Excel)-> module to add a module. Then copy the code into the module and close the editor.
Then you can add a button in your worksheet in any cell you want. Therefore under "insert" (Einfügen in german Excel) select "shapes" and square (or any shape you prefer). Then enter the shape in any cell you want. To do this press the left-click with the mouse and drag the square across a cell.
Then you can right-click in the shape and select "assign macro".
Then select the macro that you want to assign to the shape and click ok.
Then you can run the macro with a click on the shape.
