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

Closed
alinville Posts 2 Registration date Monday August 8, 2016 Status Member Last seen August 9, 2016 - Aug 8, 2016 at 11:30 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - 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!

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 9, 2016 at 12:01 PM
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
0
alinville Posts 2 Registration date Monday August 8, 2016 Status Member Last seen August 9, 2016
Aug 9, 2016 at 12:10 PM
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?
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 11, 2016 at 11:39 AM
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
0