I need to create a calculated field that would check if the value in Column 2 exists in Column 1, if it does then return �yes�. (Without blending or duplicating the data)

Hi,

This problem is similar in concept to this thread: http://community.tableau.com/thread/170476, it's a variation on the "compare each to each other" problem where in this case "each" and "each other" are across both rows and columns, whereas in that thread it was comparing across only rows, and the desired comparison is different.

Depending on your answer to @kettan's questions in this thread The specified item was not found.  there's a v9 solution:

Here's how it works:

1) I used v9 to pivot the data so instead of 2 columns there's 1:

Pivot isn't supported for all data sources, so this might be a limitation.

2. Then I created a view that filters Pivot field names for only Column 1 and shows the Pivot field values.

3. The Does 2 exist in 1 calc has the following formula:

IF {FIXED [Pivot field values] : COUNTD([Pivot field names])} > 1 THEN "Yes" END

What this does is count the number of Pivot field names (i.e. # of columns) for each pivot field value. If there's more than 1, we know that the value is appearing in both columns and therefore that returns a Yes.

4. Added that to the view. This is set up in the "v9 pivot and LOD" in the attached.

Here are three other solutions that don't require v9:

a) If the data source is a data table, you can do a self-left join on Column 1 from Column 2. This is the "self-left join" worksheet in the attached. You've ruled out data blending as an option, that could work as well.

b) Use an R script. R scripts are fantastic for this sort of thing because they give us a programming environment where we can iterate over the marks in almost any conceivable fashion. Mary Solbrig put together an example in a similar-in-concept-but-not-details script here Re: How? Compare cell value to cell value in all other rows, count matches.

c) Use Tableau's densification behaviors to effectively create a cross product inside Tableau, see my response here for an example: Re: How? Compare cell value to cell value in all other rows, count matches. I wouldn't recommend doing something like this for this kind of problem since there are more efficient ways to get the results.

Jonathan