In the following Excel sheet I want to find a formula in the third column to do the following:
For example, in the first column for all "A" Values if the related values on the second column are not equal, convert all to True and if they are equal keep it as it is (like for "B")
| First | Second | Third |
|---|---|---|
| A | True | |
| A | Fasle | |
| B | False | |
| B | Fasle | |
| B | False | |
| B | Fasle | |
| C | True | |
| C | Fasle | |
| C | True |
CodePudding user response：
If the Fasle are actually typos for FALSE, you could just check for existence of any true in column B with same letter in column A:
=COUNTIFS(A2:A12,A2:A12,B2:B12,TRUE)>0
CodePudding user response：
If you have Excel 365 you can use one of the following formulas:
Remark: I am using a table (insert > table)
self-explaining:
= LET(
filterByFirst,FILTER([Second],[First]=[@First]),
uniqueValues,UNIQUE(filterByFirst),
cntUniqueValues,ROWS(uniqueValues),
IF(cntUniqueValues=1,[@Second],"True")
)
condensed version:
=IF(ROWS(UNIQUE(FILTER([Second],[First]=[@First])))=1,[@Second],"True")
