Forum Discussion
Append portion of even number rows to odd number rows
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.
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.
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.
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.
