New columns - true or false if duplicate value

[jk-vtti]

Hello,

I'm looking for a simple calculated column formula to give me true/false (or -1/1) if a row's value is duplicated within a column. Basically, I have a column with many values that are repeated in it, and I want a new column telling me if each row has a duplicate value in the original column.

Thanks!

Joel

 

[Ruddles]

=COUNTIF(A:A,A1) will tell you how many times A1 appears in column A, so =IF(COUNTIF(A:A,A1)=1,"No","Yes") will flag duplicates with "Yes", non-duplicates with "No".

 

[jk-vtti]

Hello,

Thank you for your answer. Do you know of a way of doing this within the PowerPivot environment?

Joel

 

[Ruddles]

No, but maybe someone else does...

 

[MD610]

=CALCULATE(COUNTROWS(YourTableName), FILTER(YourTableName, [YourColumnName]=EARLIER([YourColumnName])))

If you add this as a caculated column with the appriopriate Table and Column names, you will get a count of how many times the value for that particular column appears in your entire table.

If all you really want is a True/False, then you could wrap this formula with an IF statement that says something like if it equals 1 then False, otherwise True.

 

[jk-vtti]

Great - thank you both. The formula in PowerPivot worked (and I'll know how to do it in an Excel spreadsheet as well).

Could I also use this or a similar formula to count the values in one column that appear in another column (and even wrap this in an IF stagement)?

Thanks,

Joel

 

[MD610]

Yes. Say you have 2 columns called Column1 and Column2. You want to know how many times the value in Column 2 occurs in Column 1, just modify the filter in the formula above to this:

FILTER(YourTableName, [Column1]=EARLIER([Column2])

 

[jk-vtti]

Thanks again. A quick follow up question: How can I return related values between two tables? I have two tables joined through a key (a certain shared file ID). I want to return the values from a calculated column from table 2 to table 1 (or simply do the calculation in table 1). I can't seem to make it work.

Joel

 

[MD610]

You need to use the RELATED() function to pull in values from one table to another based on their relationship.

For this to work in the scenerio you have described above, Table 2 must be your Lookup Table. That means that the key in Table 2 is unique and the arrow in the relationship diagram is pointing away from Table 1 towards Table 2. RELATED() will only work in one direction across a relationship because it can only pull values from a table that has a unique ID in the relationship.

In a calculated column in Table 1 you should be able to enter =RELATED(Table 2[YourColumnName]) to get the value if Table 2 is setup as the Lookup Table.