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

Posts
2
Registration date
Monday August 8, 2016
Status
Member
Last seen
August 9, 2016
- - Latest reply: TrowaD
Posts
2493
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 4, 2019
- Aug 11, 2016 at 11:39 AM
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!
See more 

1 reply

Posts
2493
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 4, 2019
354
0
Thank you
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
alinville
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?
TrowaD
Posts
2493
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 4, 2019
354 -
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