Filter a list (in a separate worksheet) according to a condition

[ollyhughes1982]

Hi, I wonder if anyone might be able to help, please.

I have a list of the unique parkrun events that I have completed, in the ‘Completed Unique E(V)’ worksheet. Out of these events, some no longer take place (signified by a white box [empty cell] in column Z. Whilst still live events have a ‘1’ and are filled pink).

In the second worksheet (‘All Completed Runs - BCD’), I want to list events that I have completed which are now no longer taking place - in column A. In column B, I want to display the relevant date entry from column D in the ‘Completed Unique E(V)’ worksheet.

I only want to list the first 4 closed events that I have completed, as that is what this challenge requires to be completed. At the moment I have only completed 2 events that are now closed (parkrun de Mandavit and Churchfields Farm), so there should only be two entries in the second worksheet at present.

I can’t think of the right way to do this. Any solution is much appreciated!

My file can be found here:

MR Excel - Example.xlsx

Thanks.

 

[Alex Blakenburg]

Assuming the filter function works the same on a Mac, try this in A3.

=FILTER(
FILTER('Completed Unique E(V)'!$B$3:$D$2002,
('Completed Unique E(V)'!$D$3:$D$2002<>"")*('Completed Unique E(V)'!$Z$3:$Z$2002="")
,""),
{1,0,1})

 

[ollyhughes1982]

Assuming the filter function works the same on a Mac, try this in A3.

=FILTER(
FILTER('Completed Unique E(V)'!$B$3:$D$2002,
('Completed Unique E(V)'!$D$3:$D$2002<>"")*('Completed Unique E(V)'!$Z$3:$Z$2002="")
,""),
{1,0,1})

Thanks. That looks to be working perfectly! Would I need to add in a SEQUENCE function somewhere, to limit it to the first four entries?

 

[Fluff]

Another option to limit the results to 4

=LET(Fltr,FILTER(FILTER('Completed Unique E(V)'!B3:D2002,('Completed Unique E(V)'!Z3:Z2002="")*('Completed Unique E(V)'!B3:B2002<>"")),COLUMN('Completed Unique E(V)'!B3:D3)<>3),INDEX(Fltr,SEQUENCE(MIN(ROWS(Fltr),4)),{1,2}))

 

[ollyhughes1982]

Another option to limit the results to 4

=LET(Fltr,FILTER(FILTER('Completed Unique E(V)'!B3:D2002,('Completed Unique E(V)'!Z3:Z2002="")*('Completed Unique E(V)'!B3:B2002<>"")),COLUMN('Completed Unique E(V)'!B3:D3)<>3),INDEX(Fltr,SEQUENCE(MIN(ROWS(Fltr),4)),{1,2}))

Thank you, also works perfectly and limits to the first four results

 

[ollyhughes1982]

Thank you, both. That's cracked it. Perfect!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ollyhughes1982]

One more thing, if you don't mind. I have another separate summary sheet, where I list how I have been doing in the various challenges. I am using the formula (below) to try and get to '2 out 4' for this event, but I keep getting '2 out of 2'. Any idea where I have gone wrong for the second figure? i.e. How I can get it to count the 4 rows, even though they aren't all populated.

=SUMPRODUCT(('All Completed Runs - BCD'!A3:A6<>"")/COUNTIF('All Completed Runs - BCD'!A3:A6,'All Completed Runs - BCD'!A3:A6&""))&" out of"&" "&COUNTIF('All Completed Runs - BCD'!A3:A6,"<>")

Thanks again.

 

[Fluff]

As this is a totally different question, it needs a new thread.
It would also help if you posted some sample data.

 

[ollyhughes1982]

As this is a totally different question, it needs a new thread.
It would also help if you posted some sample data.

OK. Thanks.