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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 11, 2016 at 11:39 AM
Related:
- How to compare two columns in excel
- How to take screenshot in excel - Guide
- Number to words in excel - Guide
- How to change author in excel - Guide
- Display two columns in data validation list but return only one - Guide
- Dd-mm-yyyy format in excel - Guide
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
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
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
Aug 9, 2016 at 12:10 PM
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?
Aug 11, 2016 at 11:39 AM
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