7 Replies Latest reply on Sep 1, 2016 8:06 AM by David Li

# Identifying duplicate values in a column

Hello,

I am working on a business problem and seeking a solution in this forum.  I have a view as shown below:

Expense Report Number 12756293 for employee id - 21908194 has 6 expense item split no. with the category description and amount in USD. I want to have a coloumn which will say "yes" for every expense item split no. which has the same expense amount and "No" if the amount is not the same. I want the calculation to restart for every Expense report no.

Any help with this will be highly appreciated. Thanks a lot in advance.

• ###### 1. Re: Identifying duplicate values in a column

Hi Prasanjeet,

Can you please share the data for the above screenshot to work upon?

-Ashish

• ###### 2. Re: Identifying duplicate values in a column

PFA the workbook. Please let me know if you need any other info.

• ###### 3. Re: Identifying duplicate values in a column

Hi  Ashish,

Any luck with the solution

- Prasenjeet

• ###### 4. Re: Identifying duplicate values in a column

Hi there! I used this table calculation to solve this:

IIF(SUM([Expense Amount US - DTL]) = ZN(LOOKUP(SUM([Expense Amount US - DTL]), -1)) OR

SUM([Expense Amount US - DTL]) = ZN(LOOKUP(SUM([Expense Amount US - DTL]), 1)),

"YES", "NO")

Note that the table calc has to compute along [Expense Item Split Number] and it has to sort the indices by SUM([Expense Amount US - DTL]).

If this is the correct solution, please mark it as correct! Thanks.

2 of 2 people found this helpful
• ###### 5. Re: Identifying duplicate values in a column

Thanks a lot David.

Can you explain the solution as well so that I can learn from it. Thanks.

• ###### 6. Re: Identifying duplicate values in a column

Hi David,

I have 1 more variation of the problem that I raised. If I have to check for all values in  Expense Amount US - DTL which are within the range (+/-)1 of the current Expense Amount US - DTL value and mark them all as "Yes". How can i tweak the script to cater to this requirement?

• ###### 7. Re: Identifying duplicate values in a column

You're welcome! The table calculation compares each cell's value to the cell directly before and after it. That's why you need to sort by the sum of expenses. Table calculations can process in an order that's different from the one shown on the sheet itself, and we're using that here to pick up duplicate cells.

To tweak this to handle a tolerance range, you basically need to do a calculation where you subtract the adjacent cells' values from the cell value, like this:

IIF(ABS(SUM([Expense Amount US - DTL]) - ZN(LOOKUP(SUM([Expense Amount US - DTL]), -1))) < [Tolerance] OR

ABS(SUM([Expense Amount US - DTL]) = ZN(LOOKUP(SUM([Expense Amount US - DTL]), 1))) < [Tolerance],

"YES", "NO")