count - Two excel columns, if duplicates in first column, get data from second column

Question

I have two excel columns, where the first column specifies the date a report is published, and the second specifies whether it was on time or late.

In my example, I have 5 dates, where a number of reports were published on each date. The second column specifies whether each report was late or on time.

If there was any report published late on any date, I want to classify that date as late, even though other reports were on time.

So what I have is :

Date        On Time or Late?

02/12/2020  On Time

02/12/2020  On Time

04/12/2020  Late

04/12/2020  Late

11/12/2020  On Time

11/12/2020  On Time

11/12/2020  Late

11/12/2020  On Time

18/12/2020  On Time

18/12/2020  On Time

18/12/2020  On Time

18/12/2020  On Time

30/12/2020  On Time
 

And what I want is:

Date        On Time or Late?

02/12/2020  On Time

04/12/2020  Late

11/12/2020  Late

18/12/2020  On Time

30/12/2020  On Time

Is there a way to do this in Excel?

Thanks

question from:https://stackoverflow.com/questions/65884160/two-excel-columns-if-duplicates-in-first-column-get-data-from-second-column

Answer 1

You can use following formulas:

to get unique dates if you have O365:

=UNIQUE(A2:A14)

or in pre O365 use array formula:

=IFERROR(INDEX($A$1:$A$14,MATCH(1,--(COUNTIF($D$1:D1,$A$1:$A$14)=0),0)),"")

to get OnTime/Late value use

=IFERROR(IF(COUNTIFS($A$2:$A$14,D2,$B$2:$B$14,"Late")/D2,"Late","On Time"),"")