# New columns - true or false if duplicate value

#### jk-vtti

##### New Member
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

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
=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".

Hello,

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

Joel

No, but maybe someone else does...

=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.

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

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])

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

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.

Replies
6
Views
446
Replies
6
Views
78
Replies
5
Views
139
Replies
1
Views
128
Replies
1
Views
90

1,203,329
Messages
6,054,756
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back