CODEWITHSUNDEEP
excelif-statement
Martijn Aikema
Martijn Aikema

Reputation: 63

Excel - check if one of the rows with same number has a value in an other column

I have a problem with a data request I am trying to analyse automatically. The data consists of a number of values and depening on a formula the answer in a specific column is set to "yes" or "no". Now I want to check if one of the rows containing the same number have an answer "yes" in the second column.

The number of rows with the same number in it can vary.

The solution might be very simple, but I can't seem to get the right formula to determine it. I hope someone can help me out.

Below an example of the dataset and the expected outcome.

1   yes
1   yes
2   no
3   no
3   yes
4   no
4   no
5   yes
5   yes
5   no
5   no
5   no
6   no
6   yes

So based on this dataset the result should be:

1   yes
2   no
3   yes
4   no
5   yes
6   yes

Upvotes: 0

Views: 527

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7884

Assuming data is in columns A:B and C:C is your column with numbers 1-6:

=IF(COUNTIFS($A:$A,$C1,$B:$B,"yes")>0,"yes","no")

Upvotes: 1

Related Questions