Need a formula to compare two columns and return a value of 1 [Closed]

Report
Posts
2
Registration date
Monday August 8, 2016
Status
Member
Last seen
August 9, 2016
-
Posts
2621
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 28, 2020
-
Good Morning,

I need a formula that will compare two different columns and then return a value of one if certain criteria are met. The criteria: Column A will have unique text (Names) that correspond to column B which may or may not have dates. I need to have the formula check to see if all entries in Column A with Matching Names have a date; if they do, it needs to return a 1, if not, then a 0. Example:

Column A:

Blue
Blue
Blue
Red
Red


Column B:

01/01/16
01/01/16
01/01/16
(No date)
01/01/16

The formula would need to produce a 1 for the Blue entry, since all three match and have dates, while returning a 0 for Red, since only one of the matching names has a date for a total of 1 overall. (The "No Date" cell would be empty in my workbook, fyi.)

Please let me know if I need to provide any further details!

1 reply

Posts
2621
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 28, 2020
420
Hi Alinville,

Extract the unique values of column A to column D (as used in the formula)and then use this formula:
=IF(COUNTIFS($A$2:$A$6,D2,$B$2:$B$6,"")>0,0,1)


Best regards,
Trowa
Posts
2
Registration date
Monday August 8, 2016
Status
Member
Last seen
August 9, 2016

Hey, Trowa!

Thanks for the reply. I don't quite follow what you mean by your reply. What am I extracting to column D? Just the Unique values?
Posts
2621
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 28, 2020
420
Hi Alinville,

That's right. Just the unique values. so column D (or any other location, it's just the column I used in the formula) would have Blue and Red in them.

The formula will count the number of times the value in column D appears in the given range, but only when the adjacent cell is empty. When the returned number is higher then 0, then we know that a "No Date" is found and the result will be 0. Otherwise the result will be 1.

Hopefully this clarifies the provided solution. Let me know if you have further questions.

Best regards,
Trowa