Looking for a formula to show last 10 transactions based on multiple criteria

[dilleyo725]

Hi, can anyone advise what type of formula would be best if I wanted to create a dashboard that listed the last 10 transactions based on multiple criteria? In my case it would based on the account status, i.e., Approved, Declined, Pending and the second criteria would be the submitting broker.

My table headers are as follows: Date, Submitting Broker, Submission ID, Status, Loan Amount.

 

[Peter_SSs]

My Status & Broker of interest are in H2:H3. Formula in J2 is copied across and down. Is that what you are after?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Submitting Broker	Submission ID	Status	Loan Amount					Date	Submitting Broker	Submission ID	Status	Loan Amount
2	1/08/2019	Broker 1	ID 2	Approved	1		Status	Approved		=$H$2)*(Table1:]=$H$3)),11-ROWS(J$2:J2))),"")]7/08/2019	Broker 2	ID 1	Approved	7
3	2/08/2019	Broker 2	ID 1	Approved	2		Submitting Broker	Broker 2		10/08/2019	Broker 2	ID 2	Approved	10
4	3/08/2019	Broker 1	ID 1	Pending	3					16/08/2019	Broker 2	ID 1	Approved	16
5	4/08/2019	Broker 2	ID 1	Approved	4					18/08/2019	Broker 2	ID 1	Approved	18
6	5/08/2019	Broker 1	ID 1	Declined	5					19/08/2019	Broker 2	ID 1	Approved	19
7	6/08/2019	Broker 2	ID 1	Pending	6					22/08/2019	Broker 2	ID 1	Approved	22
8	7/08/2019	Broker 2	ID 1	Approved	7					24/08/2019	Broker 2	ID 1	Approved	24
9	8/08/2019	Broker 2	ID 1	Declined	8					25/08/2019	Broker 2	ID 1	Approved	25
10	9/08/2019	Broker 1	ID 1	Pending	9					28/08/2019	Broker 2	ID 2	Approved	28
11	10/08/2019	Broker 2	ID 2	Approved	10					30/08/2019	Broker 2	ID 1	Approved	30
12	11/08/2019	Broker 1	ID 1	Declined	11
13	12/08/2019	Broker 2	ID 1	Pending	12
14	13/08/2019	Broker 1	ID 1	Approved	13
15	14/08/2019	Broker 2	ID 1	Declined	14
16	15/08/2019	Broker 1	ID 1	Pending	15
17	16/08/2019	Broker 2	ID 1	Approved	16
18	17/08/2019	Broker 1	ID 1	Declined	17
19	18/08/2019	Broker 2	ID 1	Approved	18
20	19/08/2019	Broker 2	ID 1	Approved	19
21	20/08/2019	Broker 2	ID 1	Declined	20
22	21/08/2019	Broker 1	ID 1	Pending	21
23	22/08/2019	Broker 2	ID 1	Approved	22
24	23/08/2019	Broker 1	ID 1	Declined	23
25	24/08/2019	Broker 2	ID 1	Approved	24
26	25/08/2019	Broker 2	ID 1	Approved	25
27	26/08/2019	Broker 2	ID 1	Declined	26
28	27/08/2019	Broker 1	ID 1	Pending	27
29	28/08/2019	Broker 2	ID 2	Approved	28
30	29/08/2019	Broker 1	ID 1	Declined	29
31	30/08/2019	Broker 2	ID 1	Approved	30
32
Last 10

 

[Peter_SSs]

Qualifications to my last post.
1. If you will always have at least 10 transactions that fit the criteria then you wouldn't need the IFERROR() wrapped around the rest of the formula.
2. If you might have less than 10 that fit then you may want some further adjustment anyway as my formulas would leave any resultant blank rows immediately under the headings, not at the bottom of the results.

Post back with any issues.

 

[dilleyo725]

Thank you! That seems to be what I'm looking for. Now I'm just trying to understand how the formula works. I was trying to re-enter it and got stuck when trying to enter this "[[Status]:[Status]]". It would not allow me to enter a colon. I selected the status range (without the header) and then got stuck.

Also, is there a video or link that explains this particular formula in depth by chance that you can recommend?

 

[Peter_SSs]

... got stuck when trying to enter this "[[Status]:[Status]]". It would not allow me to enter a colon. I selected the status range (without the header) and then got stuck.

- "Would not allow me ..." did it give you an error? just not respond to the keyboard? would it allow you to type a different character?
- With this sort of syntax you must include the table name
- Did you try copying my formula from the forum and paste into your sheet rather than typing it manually?

Also, is there a video or link that explains this particular formula in depth by chance that you can recommend?

Not my formula in particular, but for the tablename[[]:[]] structure I used, here is one resource:
https://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/

 

[dilleyo725]

I was able to get it to work. I just needed to understand how absolute/relative references worked with tables. I was able to see the work around in the video you provided.

This is a great formula. THANK YOU SO MUCH FOR YOU HELP.

 

[Peter_SSs]

I was able to get it to work. I just needed to understand how absolute/relative references worked with tables. I was able to see the work around in the video you provided.

This is a great formula. THANK YOU SO MUCH FOR YOU HELP.

Good news & "you're welcome".

Just checking: Will you always have at least 10 of the relevant rows to display?

 

[dilleyo725]

Hmm, I would say so since the data source is a rolling 12 month.

But in a scenario where it was just looking at the current month, and if it was just the first week of the month, I can see where it can be less than 10. In that scenario, would this same formula apply?

 

[Peter_SSs]

.. I can see where it can be less than 10. In that scenario, would this same formula apply?

Well, it 'works' but, as I pointed out in post 3, the layout is less than satisfactory in my view. See here

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Submitting Broker	Submission ID	Status	Loan Amount					Date	Submitting Broker	Submission ID	Status	Loan Amount
2	1/08/2019	Broker 1	ID 2	Approved	1		Status	Approved		=$H$2)*(Table1:]=$H$3)),11-ROWS(J$2:J2))),"")]
3	2/08/2019	Broker 2	ID 1	Approved	2		Submitting Broker	Broker 1
4	3/08/2019	Broker 1	ID 1	Pending	3
5	4/08/2019	Broker 2	ID 1	Approved	4
6	5/08/2019	Broker 1	ID 1	Declined	5
7	6/08/2019	Broker 2	ID 1	Pending	6
8	7/08/2019	Broker 2	ID 1	Approved	7
9	8/08/2019	Broker 2	ID 1	Declined	8
10	9/08/2019	Broker 1	ID 1	Pending	9					1/08/2019	Broker 1	ID 2	Approved	1
11	10/08/2019	Broker 2	ID 2	Approved	10					13/08/2019	Broker 1	ID 1	Approved	13
12	11/08/2019	Broker 1	ID 1	Declined	11
13	12/08/2019	Broker 2	ID 1	Pending	12
14	13/08/2019	Broker 1	ID 1	Approved	13
15	14/08/2019	Broker 2	ID 1	Declined	14
16	15/08/2019	Broker 1	ID 1	Pending	15
17	16/08/2019	Broker 2	ID 1	Approved	16
18	17/08/2019	Broker 1	ID 1	Declined	17
19	18/08/2019	Broker 2	ID 1	Approved	18
20	19/08/2019	Broker 2	ID 1	Approved	19
21	20/08/2019	Broker 2	ID 1	Declined	20
22	21/08/2019	Broker 1	ID 1	Pending	21
23	22/08/2019	Broker 2	ID 1	Approved	22
24	23/08/2019	Broker 1	ID 1	Declined	23
25	24/08/2019	Broker 2	ID 1	Approved	24
26	25/08/2019	Broker 2	ID 1	Approved	25
27	26/08/2019	Broker 2	ID 1	Declined	26
28	27/08/2019	Broker 1	ID 1	Pending	27
29	28/08/2019	Broker 2	ID 2	Approved	28
30	29/08/2019	Broker 1	ID 1	Declined	29
31	30/08/2019	Broker 2	ID 1	Approved	30
Last 10

If less than 10 is a possibility, I would add a helper cell, H4 below, and use this modified formula in J2 across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Submitting Broker	Submission ID	Status	Loan Amount					Date	Submitting Broker	Submission ID	Status	Loan Amount
2	1/08/2019	Broker 1	ID 2	Approved	1		Status	Approved		=$H$2)*(Table1:]=$H$3)),$H$4+1-ROWS(J$2:J2))))]1/08/2019	Broker 1	ID 2	Approved	1
3	2/08/2019	Broker 2	ID 1	Approved	2		Submitting Broker	Broker 1		13/08/2019	Broker 1	ID 1	Approved	13
4	3/08/2019	Broker 1	ID 1	Pending	3		Count	2
5	4/08/2019	Broker 2	ID 1	Approved	4
6	5/08/2019	Broker 1	ID 1	Declined	5
7	6/08/2019	Broker 2	ID 1	Pending	6
8	7/08/2019	Broker 2	ID 1	Approved	7
9	8/08/2019	Broker 2	ID 1	Declined	8
10	9/08/2019	Broker 1	ID 1	Pending	9
11	10/08/2019	Broker 2	ID 2	Approved	10
12	11/08/2019	Broker 1	ID 1	Declined	11
13	12/08/2019	Broker 2	ID 1	Pending	12
14	13/08/2019	Broker 1	ID 1	Approved	13
15	14/08/2019	Broker 2	ID 1	Declined	14
16	15/08/2019	Broker 1	ID 1	Pending	15
17	16/08/2019	Broker 2	ID 1	Approved	16
18	17/08/2019	Broker 1	ID 1	Declined	17
19	18/08/2019	Broker 2	ID 1	Approved	18
20	19/08/2019	Broker 2	ID 1	Approved	19
21	20/08/2019	Broker 2	ID 1	Declined	20
22	21/08/2019	Broker 1	ID 1	Pending	21
23	22/08/2019	Broker 2	ID 1	Approved	22
24	23/08/2019	Broker 1	ID 1	Declined	23
25	24/08/2019	Broker 2	ID 1	Approved	24
26	25/08/2019	Broker 2	ID 1	Approved	25
27	26/08/2019	Broker 2	ID 1	Declined	26
28	27/08/2019	Broker 1	ID 1	Pending	27
29	28/08/2019	Broker 2	ID 2	Approved	28
30	29/08/2019	Broker 1	ID 1	Declined	29
31	30/08/2019	Broker 2	ID 1	Approved	30
Last 10