silentdragonfly
New Member
- Joined
- Dec 19, 2014
- Messages
- 28
I have a database tab with a list of transactions done on loans that includes payments and advances. I created a tab for a user to input an account number and have it review all the transactions in the loan activity tab and return every transaction related to that one loan. My goal is to have it list each transaction but I want to put the payments in one column and the advances in another (columns M & N). Right now both payments and advances are populating in column N. Here is the formula in column N:
=IF(ROWS(N$7:N7)>COUNTIF('Loan Activity'!$AI$2:$AI$2836,$B$1),"",INDEX('Loan Activity'!$P$2:$P$2836,AGGREGATE(15,6,(ROW('Loan Activity'!$AI$2:$AI$2836)-ROW('Loan Activity'!$AI$2)+1)/('Loan Activity'!$AI$2:$AI$2836=$B$1),ROWS(N$7:N7))))
I think what I need is an if statement before "AGGREGATE" (IF Loan Activity transaction column = P put in Payment column, if Loan Activity transaction column = A put in Advance column) but I can't make it work.
I also read a post that seemed to say that I could multiply the Array part of the AGGREGATE argument by Loan Activity transaction column = A or P but again, I can't make it work.
Any help would be appreciated.
Peg
=IF(ROWS(N$7:N7)>COUNTIF('Loan Activity'!$AI$2:$AI$2836,$B$1),"",INDEX('Loan Activity'!$P$2:$P$2836,AGGREGATE(15,6,(ROW('Loan Activity'!$AI$2:$AI$2836)-ROW('Loan Activity'!$AI$2)+1)/('Loan Activity'!$AI$2:$AI$2836=$B$1),ROWS(N$7:N7))))
I think what I need is an if statement before "AGGREGATE" (IF Loan Activity transaction column = P put in Payment column, if Loan Activity transaction column = A put in Advance column) but I can't make it work.
I also read a post that seemed to say that I could multiply the Array part of the AGGREGATE argument by Loan Activity transaction column = A or P but again, I can't make it work.
Any help would be appreciated.
Peg