How to have a column that will utilize the following condition?

[Hoss2434]

Hello,

I have a column that I need to show if it contains more than 1 value from one column, while referencing another column. Below is a example of a before and after of what I want:

Before:

ID	Color
1	Red
1	Red
2	Blue
2	Red
3	Red
3	Blue
3	Red
4	Blue

After:

ID	Color	Contains Both Colors?
1	Red	No
1	Red	No
2	Blue	Yes
2	Red	Yes
3	Red	Yes
3	Blue	Yes
3	Red	Yes
4	Blue	No

So in this example the third column is checking if the "ID" column is marked for both Colors in the second column. If that ID is marked for both Red and Blue, then in the third column it will state "Yes", otherwise "No".

If someone can show me how to create a formula for this that would be much appreciated!

Thanks!

[BS_THE_ANALYST]

@Hoss2434 here's one way:

Summarize tool is checking how many distinct colours there is for each ID. We then use the join tool to join back using ID. This will give us an extra column to build logic on:

Then we can write an if statement, i.e. if number of unique colours is greater than 1, then Yes, else No.

Thanks,
BS

[BS_THE_ANALYST]

@Hoss2434 another way using a slightly different logic:

Essentially, this way gives us a column that tells us all the colours that the ID holds. We can then create another column to check if it Contains the colours we're looking for:

Hope the solutions help 😎.

Thanks,

BS

[BS_THE_ANALYST]

@Hoss2434 admittedly, solution 2 is better than solution 1 😂

[gaoa]

Hi @Hoss2434 my solution (similar to solution 1):