Need a formula to compare two columns and return a value of 1
Closed
alinville
Posts
2
Registration date
Monday 8 August 2016
Status
Member
Last seen
9 August 2016
-
8 Aug 2016 à 11:30
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 11 Aug 2016 à 11:39
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 11 Aug 2016 à 11:39
Related:
- How to compare two columns in excel
- Beyond compare - Download - File management
- How to merge and compare two excel files - Guide
- How to change date format in excel - Guide
- Excel mod apk for pc - Download - Spreadsheets
- How to clear formatting in excel - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
9 Aug 2016 à 12:01
9 Aug 2016 à 12:01
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
9 Aug 2016 à 12:10
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?
11 Aug 2016 à 11:39
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