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.	AMC	Service	Appraised $	Loan Purpose	Date	Status	Processor
1112233	20141	Accurate	Desktop		Workout	4/22/2014 11:08	Approved - As Is	Laurie
2200791	20145	Valligent	eVolv	57000	Workout	2/27/2014 15:30	Approved - As Is	JoAnn
4733371	20146	Accurate	1004D	0	Purchase	2/17/2014 07:27	Approved - As Is	Kristie
4733371	20146-1	Accurate	1004D	175000	Purchase	2/19/2014 10:08	Declined	Kristie
4733371	20146-2	Accurate	1004D	178000	Purchase	2/20/2014 09:58	Approved - As Is	Kristie
5646304	20147	Nations	1073	125000	Purchase	4/04/2014 11:51	Declined	Kristin
5646304	20147-1	Nations	1073	125000	Purchase	4/17/2014 06:59	Approved - As Is	Kristin

<tbody>
</tbody>

 

[C Moore]

So it will not be a search for a declined loan, the list will only show loans that have a declined status (i.e. loan no 1112233 would not be on the list because it doesn't have a declined status)?

 

[JacobRH]

Correct! So Loans 1112233 and 2200791 will not be on the new list because the don't have a status of "Decline" however, all records for Loan No's 4733371 and 5646304 need to be returned becase one of the records has a status of "Declined".

 

[JacobRH]

Any one have any thoughts?

 

[C Moore]

I would format as table to make the following equations easier to read. Click a cell in the data and click format as table, name it LoanData. Pick a cell to start your unique declined list (mine was K2) and input the following with Ctrl+Shift+Enter:
=INDEX(LoanData, MATCH(0, COUNTIF($K$1:$K1,LoanData[Loan No.])+(LoanData[Status]<>"Declined"), 0), COLUMN(A1))
Fill down as needed. Name the range of unique values (in this case K2:K8) as DeclinedList. Pick a cell for your list to start (mine was M2) and enter the flowing with Ctrl+Shift+Enter:
=INDEX(LoanData,SMALL(--ISNA(LoanData[Loan No.]=DeclinedList)*ROW(LoanData[Loan No.]),ROW(1:1)-1+MATCH(1,--ISNA(LoanData[Loan No.]=DeclinedList),0))-1,COLUMN(A:A))
Fill right, then down as needed.

 

[JacobRH]

Thanks C Moore!

So Building the "DeclinedList" worked and I understand that formula. But using the second formula to fill in the table it is returning loans that are not in the DeclinedList. What I'm seeing is that the Small and Match portions are returning #N/A for the first 32 rows they evaluate, but after that it returns a True for the remaining 9375 rows; almost like it isn't matching the entire list, just cycling through the list of 30 loans with a status of decline.

I tried your formulas with the sample data I included and it work just fine...for some reason it doesn't like the larger dataset though. Any thoughts?

 

[C Moore]

It's tough to say without seeing it. You'll have to step through pieces of it to figure out what's wrong with Evaluate Formula.

 

[JacobRH]

I figured it out. I modified the DeclinedList column to the following 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]),"")

Which returned the row number where there is a match for the Loan Number when the Loan Status = Declined. From there I just used an Index to pull in the column values.


Thanks for all your help!

Jacob