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


New Member
May 25, 2010
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
111223320141AccurateDesktop Workout4/22/2014 11:08Approved - As IsLaurie
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


Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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)?
Upvote 0
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".
Upvote 0
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.
Upvote 0
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?
Upvote 0
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.
Upvote 0
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!

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back