Possible MIN function to be added to FILTER formula

[ollyhughes1982]

Hi. I am currently using the formula below to show all parkruns that I have completed on New Year's Days - along with he date:

=IFERROR(FILTER('All Completed Runs'!C4:C2003,'All Completed Runs'!DP4:DP2003<>""),"")

=IFERROR(FILTER('All Completed Runs'!E4:E2003,'All Completed Runs'!DP4:DP2003<>""),"")

The 'All Completed Runs' worksheet is the source and holds all of my record-level data for runs:

1. Column C is the event (venue) that was completed column, in the 'All Completed Runs' worksheet.
2. Column E is the date completed column, in the 'All Completed Runs' worksheet.
3. Column DP is a helper column which indicates whether the date was the 01/01 in any year ("" for non New Year's Eve days and "1" for New Year's Days).

This formula works fine, except for one issue - on New Year's Day you can complete two runs on the same day. When this is the case, I would like to display the information on just one line. e.g. For 01/01/2018 I would to display 'Riverfront parkrun and Newport parkrun' and then the date as it is now. So I would end up with 4 rows, instead of 7.

I'm thinking I may need to some sort of MIN function into the formulas? I have already added a column in the 'All Completed Runs' worksheet, which concatenates events with an 'and' in between them in column BA.

I have included a link to the spreadsheet, which is 40mb in size and can therefore take a minute or so to open.

parkrun NYD Example.xlsx

Microsoft Excel Workbook

1drv.ms

Thanks in advance!

 

[jasonb75]

I'm not attempting to open the file, it failed last time I tried.
See if this works in place of the first formula, if it does then you can easily change the references to column C to E for the second one.

=IFERROR(FILTER('All Completed Runs'!C4:C2003,('All Completed Runs'!DP4:DP2003<>"")*('All Completed Runs'!B4:B2003=MINIFS('All Completed Runs'!B4:B2003,'All Completed Runs'!DP4:DP2003,"*?",'All Completed Runs'!C4:C2003,'All Completed Runs'!C4:C2003))),"")

If you need more help, please provide a link to a smaller sample file that only contains data relevant to the question rather than the entire workbook.

 

[ollyhughes1982]

I'm not attempting to open the file, it failed last time I tried.
See if this works in place of the first formula, if it does then you can easily change the references to column C to E for the second one.

=IFERROR(FILTER('All Completed Runs'!C4:C2003,('All Completed Runs'!DP4:DP2003<>"")*('All Completed Runs'!B4:B2003=MINIFS('All Completed Runs'!B4:B2003,'All Completed Runs'!DP4:DP2003,"*?",'All Completed Runs'!C4:C2003,'All Completed Runs'!C4:C2003))),"")

If you need more help, please provide a link to a smaller sample file that only contains data relevant to the question rather than the entire workbook.

Thanks, that worked great!