Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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"),"")

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...