How do I pull multiple information from the same MainID (foreign key) in Excel?

[Jangster]

Hi,

I am working for a company which acquires SME/retail Non Performing Loans (Say Borrower Code B_0001 to B_1000)- of which each Borrower Code may contain multiple Loans (ie B_0001 may have 2 LoanIDs (L_0001, L0002... and the running number goes on with the next Borrower. These are the starting balance from the date of acquisition, of which I would then try to accrue and deduct the respective principal and interest of the respective Loan along the way. Each Payment running number, say PMT0001 will have a LoanID next to it as well- indicating which balance it pays

The loan payment sheet would look something like the below (more or less)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PaymentNo[/TD]
[TD]BorrowerID[/TD]
[TD]LoanID[/TD]
[TD]Date[/TD]
[TD]Princ[/TD]
[TD]Int[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PMT_001[/TD]
[TD]B_0001[/TD]
[TD]L_0001[/TD]
[TD]1 Sep 2017_[/TD]
[TD]2000[/TD]
[TD]1000[/TD]
[TD]3000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PMT_002[/TD]
[TD]B_0002[/TD]
[TD]L_0004[/TD]
[TD]1 Sep 2017[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PMT_003[/TD]
[TD]B_0001[/TD]
[TD]L_0002[/TD]
[TD]30 Aug 2017[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PMT_004[/TD]
[TD]B_0001[/TD]
[TD]L_0001[/TD]
[TD]1 Aug 2017[/TD]
[TD]500[/TD]
[TD]300[/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PMT_005[/TD]
[TD]B_0001[/TD]
[TD]L_0001[/TD]
[TD]31 Aug 2017[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now, I am trying to build some templates for other users which would make it easier to do but I don’t know how to make the Formula to capture all the PaymentNo for the specified Borrower ID and Loan ID

I would like to be able to type “B_0001” and “L_0001” to vlookup/sumif and have it return “PMT_0001” , “PMT_0004” and “PMT_0005” (so i canlink PMT0001 to lookup for more information such as the date of payment, amount paid in P and I to calculate the balance as of that day.

Is there a way to type a formula to capture the respective PaymentNo as describe above, and so that when I copy down the formula, it would capture these PMT number in the order it finds? (ie if I put the formula in cell A3, it would pull PMT_0001, and when I copy down, A4 and A5 would show PMT_004 and PMT 0005 accordingly?)


Thanks in advance.

 

[sandy666]

Don't you think that PivotTable will be a good choice?

 

[Jangster]

Don't you think that PivotTable will be a good choice?

I am not sure really.... Usually when i do information like sum of something, i used to do Sumifs to pull information (i must admit, i stink in excel and learned through googling mostly to reach where i am now with my limited database knowledge)

If i do pivot table, it would have to manually create everytime right? I am trying to pull the paymentID to essentially pull other info... So that adjacent to those info, i will have a standing formula to calculate the amount which is paid on that particular date to roll into what today's loan balance would be... (The idea for me is to create an excel spreadsheet for a non-excel savvy user to just type the BorrowerID and show all the relevant information in 1 page on the fly such as name, address, last contact date, ladt payment date, etc but i am just trying to figute out how to pull the payment profile effectively in behind so i can show thr correct loan balance as of today() for the account officers to see)


Ps. Pls excuse any typos or bad grammar as I am non-native and typing on the phone

 

[sandy666]

I said PivotTable based on your example. It's hard to say whole solution with little part of information.
If you have more tables you can try PivotTable with DataModel, PowerQuery (Excel version?) or even formula.

edit:
PivotTable from your example

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]BorrowerID[/td][td=bgcolor:#DDEBF7]B_0001[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]LoanID[/td][td=bgcolor:#DDEBF7]L_0001[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]PaymentNo[/td][td=bgcolor:#DDEBF7]Date[/td][td=bgcolor:#DDEBF7]Sum of Princ[/td][td=bgcolor:#DDEBF7]Sum of Int[/td][td=bgcolor:#DDEBF7]Sum of Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PMT_001[/td][td]

01/09/2017​

[/td][td]

2000​

[/td][td]

1000​

[/td][td]

3000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PMT_004[/td][td]

01/08/2017​

[/td][td]

500​

[/td][td]

300​

[/td][td]

800​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PMT_005[/td][td]

31/08/2017​

[/td][td]

200​

[/td][td]

200​

[/td][td]

400​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

2700​

[/td][td=bgcolor:#DDEBF7]

1500​

[/td][td=bgcolor:#DDEBF7]

4200​

[/td][/tr]
[/table]

 

[Jangster]

That pivot actually would be great (although maybe overkill as i will only need to pull the Payment No and do a bunch of formulas to pull information from column B,c,d onwards..…

Essentially what wouls be great is a formula to put in cell A4 (where it currently reass pmt001), and i can copy down the formula to A5,6 to get the Pmt numbr)

What would be the logical functiin to use so i can try to google for more info?

Thanks

 

[sandy666]

Honestly I don't understand now what are you tryin' to achieve
I can't help with formula (I prefer Excel features like PivotTable and PowerQuery) so maybe someone else will give you formula solution.

will be fine if you post source data (post#1) and expected result (post#?)

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any use. I have used helper cells in A12 down. These could be placed elsewhere and hidden if you want or the other formulas could be adjusted to not need the helpers. However, the helper cell make the other formulas much simpler.

A12 & B12 are copied down as far as you might ever need.
C12 is copied across and down. (Column C may need its formatting set to date after you have entered the formulas)

Excel Workbook
	A	B	C	D	E	F	G
1	PaymentNo	BorrowerID	LoanID	Date	Princ	Int	Total
2	PMT_001	B_0001	L_0001	1-Sep-17	2000	1000	3000
3	PMT_002	B_0002	L_0004	1-Sep-17	20	50	70
4	PMT_003	B_0001	L_0002	30-Aug-17	200	200	400
5	PMT_004	B_0001	L_0001	1-Aug-17	500	300	800
6	PMT_005	B_0001	L_0001	31-Aug-17	200	200	400
7
8
9	BorrowerID	B_0001
10	LoanID	L_0001
11		Payments	Date	Princ	Int
12	1	PMT_001	1-Sep-17	2000	1000
13	4	PMT_004	1-Aug-17	500	300
14	5	PMT_005	31-Aug-17	200	200
15
Payments