Return all records from a transaction where one record meets criteria - no VBA

[JacobRH]

Good Morning All,

I have a worksheet listing appraisal records for loan transactions. A single loan number may have multiple appraisal transaction records. What I've been asked to do is, if an appraisal transaction was declined for a loan (Status Column), list all of the records for that loan, whether declined or approved. The thing is, they have requested no VBA becasue the team who will maintain the workbook doesn't have anyone with VBA experience. I've tried making an iterative count helper column and then creating a unique distinct list based on the criteria, but I'm still not getting what I need. Any help would be greatly appreciated! Sample Data is below. The results should be returned in a new worksheet with the same column headings.

Thank you!
Loan No.Doc ID.AMCServiceAppraised $Loan PurposeDateStatusProcessor

220079120145ValligenteVolv57000Workout2/27/2014 15:30Approved - As IsJoAnn
473337120146Accurate1004D0Purchase2/17/2014 07:27Approved - As IsKristie
473337120146-1Accurate1004D175000Purchase2/19/2014 10:08DeclinedKristie
473337120146-2Accurate1004D178000Purchase2/20/2014 09:58Approved - As IsKristie
564630420147Nations1073125000Purchase4/04/2014 11:51DeclinedKristin
564630420147-1Nations1073125000Purchase4/17/2014 06:59Approved - As IsKristin

 

[Hui]

Have a read of: http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[JacobRH]

Hi Hui, Thanks for the reply! I feel like this is getting me the same results as this:

{=IFERROR(INDEX(LoanNum_List,MATCH(0,COUNTIF('Sheet2'!$B$2:B2,LoanNum_List)+IF(TrxStatus<>"DECLINED",1,0),0)),"")}

which I have already tried, it creates the list for me of just the records with a status of decline. What I need is to create a listing of all records for each loan number where there is a status of declined. So, In my sample data, the result for loan number 5646304 would have two listings since there are two records and one of them has a status of declined.

Does that make sense?

I'm stuck on creating making the output list include all transactions for a loan number when one of the transactions is Declined.

Thanks Again,

Jacob

 

[JacobRH]

I've create a unique list of loan numbers that have a status of declined and named the range "DeclinedList". I then converted my data to a table. Next, I entered the following formula into cell M2, as an array:

{=INDEX(LoanData,SMALL(--ISNA(LoanData[Loan Number]=DeclinedList)*ROW(LoanData[Loan Number]),ROW(58:58)-1+MATCH(1,--ISNA(LoanData[Loan Number]=DeclinedList),0))-1,COLUMN(A:A))}

My problem is this only evaluates the first 30 rows of loan numbers ad my DeclinedList only has 30 unique loan numbers in it. Any thoughts on how to get around this?

Thank you!

 

[Haseeb A]

Hello Jacob,

Could you please upload a sample file desired outcome?

 

[JacobRH]

Sample file attached. I shortened ti to 250 rows of data and included a tab for Desired Output and an Actual Output tab that shows the results of my formula.

Thank you,

Jacob

 

[Haseeb A]

Hello Jacob,

Since you are looking to get multiple column outputs, better to have one helper column. Use one column to get matchable Row#, then reference that row in INDEX formula. So you can avoid same array formula repeating for each column.

You can hide this column, if you wish. Or you can directly apply this formula in INDEX row_number, if you don't like to have helper column.

Here is work around:

In your output tab insert a new column before 'A',

A1: Row#
A2: Array Formula

=IFERROR(SMALL(IF(ISNUMBER(MATCH(LoanData[Loan Number],IF(LoanData[Status]="Declined",LoanData[Loan Number]),0)),ROW(LoanData[Status])),ROWS(A$2:A2))-ROW(LoanData[#Headers]),"")

B2: Normal formula, then copy across

=IF(N($A2),INDEX(LoanData[Loan Number],$A2),"")

Copy A2:J2, then paste down as needed.

See attached file.

 

[JacobRH]

That is exactly what I was looking for! Thank you, Haseeb!