Reduce a list of dates

Hi,

I have a list of participant ID's and start and stop dates for when they have been on a medication. Some participants have multiple rows but I would like one row per participant.

Participant Start date Stop date
1 24.11.2011 01.01.2016
1 18.02.2010 24.11.2011
1 24.11.2011 01.01.2016
1 18.02.2010 24.11.2011


I would like to just take the earliest date for the start date and the latest date for this stop date.

Participant Start date Stop date
1 18.02.2010 01.01.2016


Any help would be greatly appreciated!

https://www.mrexcel.com/forum/excel-...ist-dates.html

http://www.ozgrid.com/forum/showthread.php?t=203740

Thank you

AliGW it appears OP has complied. I'm taking that as a "green light".

London123, with the understanding that data is in columns A:C and output is in columns E:G ---

Enter this formula in E2 and fill down until you get blanks. It returns the unique 'Participant' #s

Formula:

Please Login or Register  to view this content.

In F2 array enter this formula and fill down same. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. It returns the earliest Start Date for each Participant.

Formula:

Please Login or Register  to view this content.

Then array enter this formula in G2 and fill down. It returns the latest Stop Date for each Participant.

Formula:

Please Login or Register  to view this content.

The only difference between my real spreadsheet and the one I sent you is that the ID numbers are much longer than 1, 2, 3, 4 etc. They are 9 digits with dashes in between, do you think that might make a difference?

Yes. The dashes make the numbers text "numbers". The formula I posted depends upon data in $A$2:$A$14 having numeric value.

Text requires some extra steps in the FREQUENCY formula, and this time it has to be array entered.

Formula:

Please Login or Register  to view this content.

The rest of your formulas will still work.

(I changed $A$14 to $A$3216 because I have a much longer list).

I don't know how much that many rows of data will affect workbook performance. If you are not aware of it array formulas are resource hungry. Too many of them or arrays too large will slow a workbook. My 'instincts' tell me you will OK, but they have lied to me before.

Also the output could be more of a resource issue. How many rows of output do you anticipate ie how many unique IDs do you have?

Try this on the real data. If performance is unacceptable the remedy will be a 'helper' column for the ID formula. If that still doesn't do it I will have to explore further steps for the date returns.

Alternative approach:

J2 (participants): =IFERROR(INDEX($A$2:$A$14,MATCH(0,INDEX(COUNTIF($J$1:$J1,$A$2:$A$14),0),0)),"")

K2 (start date): =IFERROR(AGGREGATE(15,6,$B$2:$B$14/($A$2:$A$14=J2),1),"")

L2 (End date): =IFERROR(AGGREGATE(14,6,$C$2:$C$14/($A$2:$A$14=J2),1),"")

all "normal" formulae...

Hi London,

You can do this problem without using any formulas. Just create a Pivot Table as shown in the attached. (Thanks to Glenn) Use a Min for the Start and Max for the End.

PT Reduce a list of dates.xlsx

You're welcome & thanks for the Rep.