Append portion of even number rows to odd number rows

Copper Contributor

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.

 

2023-04-03_fusion-loan-snip.PNG

 

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

@jepollard 

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 Sub

You can try this code. In the attached file you can click the button in cell N2 to run the macro.

principal.JPG

@jepollard 

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

image.png

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.

@jepollard 

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.

principal interest.JPG

@OliverScheurich 

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.

@Peter Bartholomew 

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.

principal makearray.JPG

@OliverScheurich 

 

I couldn't figure out how to add a post without replying to a specific existing post.  I wanted to thank you both for your input; it's very much appreciated.

 

There's quite a bit here for me to digest, so it may be a while before I can determine what path is best for me.  But a bit more information, and a few more questions, might be in order.

 

The sample I posted is from actual loan payments made (the lender supplied the original .CSV file), not from a calculated loan payment schedule. So I will need to be able to input the (or "a") .CSV file.

[I hadn't given it enough thought before posting here, but I suppose it may be possible to make modifications to the .CSV file that would cause the principal and interest to appear in one Excel row. But I'd still like to explore "correcting" the data in Excel.]

 

In the case of a simple interest loan (such as my auto loan); there is most likely a difference between the principal/interest splits in the loan payment schedule and the actual principal/interest splits incurred, since each payment split is determined by the lender based on the date when they receive/record each payment. 

 

I have software that can generate loan payment schedules; the project this discussion is about is for me to compare (a visual comparison is ok - at least for now), the actual loan payment principal/interest splits to the principal interest splits in the original payment schedule.

 

My preference is not to purchase anything new, or upgrade beyond Excel 2019 (I have Excel 2013 on my laptop where I do a lot of testing).  I don't expect to be doing this frequently.

I don't know what "Power Query" is, but I might consider trying it; if the price is right and the learning curve isn't too steep.

 

One question that came to mind when I read the original suggestion: will that macro handle all the rows for any loan? That is: my data is for a 60 month loan; 60 payments, two rows per payment. I only posted a few of those rows here.  Can the macro handle any number of payments/rows (for this and any future loans), or would I have to modify the macro to address the actual number of rows?

 

Thanks again.

@jepollard 

The code dynamically recognizes the number of rows and can handle almost any number of rows. The limit is the maximum number of rows in a worksheet which is 1048576. If you want to visually seperate different loans you can enter the data as shown in the screenshot. The data for one loan is in rows 1 -14 and for the other loan in rows 17 - 34 in the example and the result is returned with an empty row in columns G to L.

interest principal.JPG

For the comparison of actual loan payments with the payment schedule can you attach a screenshot which shows the actual and scheduled data and the layout of the data along with the intended result?

Thanks.
Let me play around with what I've gotten so far, to see what I might want to do next.
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?

@jepollard 

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.

vba editor insert module.JPG

 

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. 

insert square.JPG

 

Then you can right-click in the shape and select "assign macro".

select cell assign macro.JPG

 

Then select the macro that you want to assign to the shape and click ok.

assign macro.JPG

 

Then you can run the macro with a click on the shape.