Need to return the earliest date respective to one criteria

[BYizz]

Hello, I have a list of names and a list of dates respective to each time that employee has participated in a certain event. I need another column to return the earliest event that employee has participated, or better yet, return a "1" in the row if that was the earliest event that employee has participated in. For example, the table below lists the employee name, the date of an event they participated in, and column C, which would be my expected result. For C2, formula would compare all dates respective to "Bill", then return a "1" if the date in that row was the earliest of all the events listed. Please help!

A________B________C
NAME____DATE_____EARLIEST?
Bill..........1/4/12..........0
Bill...........3/2/11.........1
Joe..........5/23/12.......0
Bob.........2/2/12..........1
Joe..........4/15/11........1
Bob..........6/12/12.......0

[Fotis1991]

Try with this ARRAY formula.

=if(b2=min(if($a$2:$a$100=a2;$b$2:$b$100));1;0)

UNTESTED

[BYizz]

Try with this ARRAY formula.

=if(b2=min(if($a$2:$a$100=a2;$b$2:$b$100));1;0)

UNTESTED

Thanks for your input, but this doesn't quite work. The "min" function needs a range to work from, and your second "if" function does not provide the necessary range to compare values. This formula returned a "0" for every instance.

[Fotis1991]

=MIN(IF($A$2:$A$100=A2,$B$2:$B$100))=B2

Ok. This one gives TRUE or FALSE, as results. Does this works for you?

and this one gives the numbers that you want.

=IF(MIN(IF($A$2:$A$100=A2,$B$2:$B$100))=B2=TRUE,1,0)

[daddylonglegs]

For a "non-array" version try COUNTIFS, e.g. in C2 copied down

=(COUNTIFS(A:A,A2,B:B,"<"&B2)=0)+0

[BYizz]

=MIN(IF($A$2:$A$100=A2,$B$2:$B$100))=B2

Ok. This one gives TRUE or FALSE, as results. Does this works for you?

and this one gives the numbers that you want.

Not quite...I'm getting "FALSE" returned for every instance, when some should be "TRUE". I'm also a bit confused by this formula, I think there might be an error because the "IF" statement is checking to see if the name matches. If the name does not match, it returns the entire array for the MIN function to sort through. However, I think it needs a smaller array to sort through for the case that the name DOES match. For example, add a date to a smaller array for only cases when the name matches criteria, then take the MIN of that smaller array...I'm just not sure how to do that.

[daddylonglegs]

The MIN(IF formula works for me.....but you need to enter the formula with CTRL+SHIFT+ENTER so that curly braces like { and ] appear around it.....or try my COUNTIFS suggestion which can be entered normally

[BYizz]

For a "non-array" version try COUNTIFS, e.g. in C2 copied down

=(COUNTIFS(A:A,A2,B:B,"<"&B2)=0)+0

This worked! Thanks for your help. Why does the formula need quotations around the < sign and what the "&" do?

[daddylonglegs]

Why does the formula need quotations around the < sign and what the "&" do?

All the "IFS" functions like COUNTIF, SUMIFS, AVERAGEIFS etc. use text strings as criteria, hence the quotes, so for "greater than 10" you'd use ">10", but if you have 10 in a cell, e.g. G2 then you need to concatenate the ">" to the 10 so & does that, e.g. ">"&G2

[BYizz]

All the "IFS" functions like COUNTIF, SUMIFS, AVERAGEIFS etc. use text strings as criteria, hence the quotes, so for "greater than 10" you'd use ">10", but if you have 10 in a cell, e.g. G2 then you need to concatenate the ">" to the 10 so & does that, e.g. ">"&G2

Good to know. Thanks!

[BYizz]

For a "non-array" version try COUNTIFS, e.g. in C2 copied down

=(COUNTIFS(A:A,A2,B:B,"<"&B2)=0)+0

One more question...does the COUNTIFS function use these criteria as a "filter", per se, based on order of criteria? So, in this case, does the COUNTIFS function first filter the array to meet the first criteria (match the names), then count the number of entries with a date that occur earlier than the filtered entries? I guess I would just assume that the second criteria (count if there are no occurrences where the date is earlier) would only occur once in the entire array: the very earliest date for ALL employees; not once for every employee. Clearly I'm wrong in this assumption, but would someone mind explaining why this method can work??

Thanks!

[daddylonglegs]

COUNTIFS only counts rows where all conditions are met (two in this case) so when you use this formula

=COUNTIFS(A:A,A2,B:B,"<"&B2)

....it is counting the number of rows where both conditions are fulfilled, i.e. column A matches the name on the current row and column B has a date earlier than the date on the current row. If the result of that formula is zero then that means there are no rows where the name is the same and the date is earlier........hence this row must have the earliest (or equal earliest) date for that name.

The formula suggested by Fotis will give the same results if correctly entered