Possible MIN function to be added to FILTER formula

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
514
Office Version
  1. 365
Platform
  1. MacOS
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.

Screenshot 2021-11-01 at 01.56.38.jpg


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.


Thanks in advance!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,975
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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.
 
Solution

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
514
Office Version
  1. 365
Platform
  1. MacOS
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.
Excel Formula:
=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!
 

Forum statistics

Threads
1,175,530
Messages
5,897,952
Members
434,688
Latest member
vi28

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 MrExcel.com.
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 "mrexcel.com".
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
Top