Pull recent 3 most recent entries

[DJFANDANGO]

Hi Guys,

I'm going to change my name to brain fart....

I'm trying to figure out how to pull the top 3 or 3 most recent entries from a data set based on the date...

I'm guessing INDEX/MATCH... but can't figure out the 'date' bit... any help appreciated. Thanks.

Book1
	A	B	C	D	E
1	Data			Last 3 results
2	Date	Audited		Output	Audited
3	23/12/2020			15/10/2020	Yes
4	07/12/2020			27/05/2020	Yes
5	15/10/2020	Yes		14/05/2020	Yes
6	17/09/2020
7	02/08/2020
8	08/07/2020
9	27/05/2020	Yes
10	25/05/2020
11	21/05/2020
12	18/05/2020
13	14/05/2020	Yes
14	15/04/2020
15	23/02/2020
16	10/02/2020
17	01/02/2020
18	07/12/2019
19	26/11/2019
20	24/10/2019	Yes
21	06/10/2019
22	10/09/2019
23	27/08/2019
24	08/08/2019
25	13/07/2019	Yes
26	27/06/2019
27	23/05/2019
28	29/04/2019
29	23/04/2019	Yes
30	04/03/2019
31	24/02/2019
32	22/02/2019
33	16/02/2019
34	17/01/2019
Sheet1

 

[arthurbr]

Perhaps in E3 following array formula

 =large((($a$3:$a$34)*--($b$3:$b$34="Yes")),row()-2)

committed with Ctrl+Shift +Enter and pulled down

 

[DJFANDANGO]

Hi Arthur,

Thanks, this works pulling the last 3 dates, but it's not pulling the 'Yes" part...? any ideas?

 

[galbert]

Hi,

Are you able to add additional columns to the file? If yes, you could add 2 additional ones: one (let's assume it's Column C) pulls the date for those rows which contain "Yes" in column B: =IF(B3="Yes",A3,""). Then you could determine the rank of each row where you have a date (i.e. the ones which are audited), using IFERROR and RANK.EQ formulas (let's assume it's in Column D): =IFERROR(RANK.EQ(C3,$C$3:$C$35,0),""). This results in dates being populated to the rows with a "Yes"in the 2nd column. Now you just need to write the INDEX/MATCH formulas to get the 3 most recent dates (since I've inserted columns, it would be in Column F): =INDEX($A$3:$A$35,MATCH(1,$D$3:$D$35,0)). You need to replace 1 with 2 and 3 to return 2nd and 3rd ranked records. If there is a possibility that at any point in time there are less than 3 audited records, you could also combine it with IFERROR.

Hope this helps.

 

[arthurbr]

In E3 also committed with CSE

=INDEX($B$3:$B$34,match(1,--($b$3:$b$34="Yes")*--($a$3:$a$34=$d3),0))

and pull down

 

[FormR]

Hi, here's another option you can consider.

Book1
	A	B	C	D	E
1	Data			Last 3 results
2	Date	Audited		Output	Audited
3	23/12/2020			15/10/2020	Yes
4	07/12/2020			15/10/2020	Yes
5	15/10/2020	Yes		15/10/2020	Yes
6	17/09/2020
7	02/08/2020
8	08/07/2020
9	27/05/2020	Yes
10	25/05/2020
11	21/05/2020
12	18/05/2020
13	14/05/2020	Yes
14	15/04/2020
15	23/02/2020
16	10/02/2020
17	01/02/2020
18	07/12/2019
19	26/11/2019
20	24/10/2019	Yes
21	06/10/2019
22	10/09/2019
23	27/08/2019
24	08/08/2019
25	13/07/2019	Yes
26	27/06/2019
27	23/05/2019
28	29/04/2019
29	23/04/2019	Yes
30	04/03/2019
31	24/02/2019
32	22/02/2019
33	16/02/2019
34	17/01/2019
Sheet1

Cell Formulas
Range	Formula
D3:D5	D3	=AGGREGATE(14,6,A3:A34/(B3:B34="Yes"),ROWS($D3:D3))
E3:E5	E3	="Yes"

 

[DJFANDANGO]

Hi, here's another option you can consider.

Book1
	A	B	C	D	E
1	Data			Last 3 results
2	Date	Audited		Output	Audited
3	23/12/2020			15/10/2020	Yes
4	07/12/2020			15/10/2020	Yes
5	15/10/2020	Yes		15/10/2020	Yes
6	17/09/2020
7	02/08/2020
8	08/07/2020
9	27/05/2020	Yes
10	25/05/2020
11	21/05/2020
12	18/05/2020
13	14/05/2020	Yes
14	15/04/2020
15	23/02/2020
16	10/02/2020
17	01/02/2020
18	07/12/2019
19	26/11/2019
20	24/10/2019	Yes
21	06/10/2019
22	10/09/2019
23	27/08/2019
24	08/08/2019
25	13/07/2019	Yes
26	27/06/2019
27	23/05/2019
28	29/04/2019
29	23/04/2019	Yes
30	04/03/2019
31	24/02/2019
32	22/02/2019
33	16/02/2019
34	17/01/2019
Sheet1

Cell Formulas
Range	Formula
D3:D5	D3	=AGGREGATE(14,6,A3:A34/(B3:B34="Yes"),ROWS($D3:D3))
E3:E5	E3	="Yes"

Hi FormR,

Thanks for the formula, It's pulling only the most recent date 3 times? any ideas?

 

[Peter_SSs]

It's pulling only the most recent date 3 times? any ideas?

There was a mis-placed $ sign. Try

=AGGREGATE(14,6,A3:A34/(B3:B34="Yes"),ROWS(D$3:D3))

 

[DJFANDANGO]

As always, it's the simple things!

Thanks again! This gives me something to work on now!

There was a mis-placed $ sign. Try

=AGGREGATE(14,6,A3:A34/(B3:B34="Yes"),ROWS(D$3:D3))

 

[Dossfm0q]

Just a little bit change for Top 3 Limitation
IFERROR(AGGREGATE(14,6,$A$2:$A$33/($B$2:$B$33=E2)/(ROWS($D$2:D2)<=3),ROWS(D$2:D2)),"")

Book1
	A	B	C	D	E
1	Date	Audited		Output	Audited
2	23/12/2020			15/10/2020	Yes
3	07/12/2020			27/05/2020	Yes
4	15/10/2020	Yes		14/05/2020	Yes
5	17/09/2020
6	02/08/2020
7	08/07/2020
8	27/05/2020	Yes
9	25/05/2020
10	21/05/2020
11	18/05/2020
12	14/05/2020	Yes
13	15/04/2020
14	23/02/2020
15	10/02/2020
16	01/02/2020
17	07/12/2019
18	26/11/2019
19	24/10/2019	Yes
20	06/10/2019
21	10/09/2019
22	27/08/2019
23	08/08/2019
24	13/07/2019	Yes
25	27/06/2019
26	23/05/2019
27	29/04/2019
28	23/04/2019	Yes
29	04/03/2019
30	24/02/2019
31	22/02/2019
32	16/02/2019
33	17/01/2019
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IFERROR(AGGREGATE(14,6,$A$2:$A$33/($B$2:$B$33=E2)/(ROWS($D$2:D2)<=3),ROWS(D$2:D2)),"")