Welcome!

Duplicates

How do i create a formula that shows there are duplicates.

I would like it to have a check box in a column showing that "Sarah Jensen" has duplicate expense submission based off Consultant Name, Date of Receipt,Total Expense.

Find more posts tagged with

Human Resources

Sheets

Healthcare

Finance

Formulas

Solution Center

Account and User Management

Comments

sean59916

Hi,

Here is how I worked this request -

added a helper column to concatenate the fields that you are looking to compare, i.e. =[Consultant Name]@row + [Date of Receipt]@row + [Total Expense]@row
added a checkbox with the following formula =IF([Consultant Name]@row = "", "", IF(COUNTIF(Helper:Helper, Helper@row) > 1, 1, 0))
Hide the helper column

See the screenshot - I hope this helps?

Sean

Paul Newcome

You could skip the helper column and just include the additional column criteria in a COUNTIFS instead of a COUNTIF.

=COUNTIFS([Consultant Name]:[Consultant Name], [Consultant Name]@row, [Date of Receipt]:[Date of Receipt], [Date of Receipt]@row, [Total Expense]:[Total Expense], [Total Expense]@row)

This will count how many rows have the same data in those 3 columns as the row the formula is currently sitting on. In your screenshot, going down the rows it would populate

We can then leverage that result in an IF statement to check your box if there are any rows that have a number higher than 1 meaning there is more than one row with matching data.

=IF(COUNTIFS(..........) > 1, 1, 0)

So the finalized formula in the checkbox column would look something like this...

=IF(COUNTIFS([Consultant Name]:[Consultant Name], [Consultant Name]@row, [Date of Receipt]:[Date of Receipt], [Date of Receipt]@row, [Total Expense]:[Total Expense], [Total Expense]@row) > 1, 1, 0)

sean59916

Good job Paul.

Sign in to join the conversation!

Quick Links

Trending discussions

Create Sequential, Unique ID for certain rows as column formula
I need a column formula that can add sequential, unique IDs to data like this coming in from a form: Task Type Task ID Manual M001 Automated Manual M002 Manual M003 Manual M004 Automated Manual M005 Task IDs that aren’t the manual type need sequential numbering within their type (i.e. A001, A002, etc). Here’s the kicker:…
SUMIFS keeps returning 0
I'm trying to display a sum of the column Total Funds Available when Status = Apps Open, and Value Score = 5. Here is the formula I have: =SUMIFS([Total Funds Available]:[Total Funds Available], Status:Status, ="Apps Open", [Value Score]:[Value Score], ="5") There are rows that meet these conditions and should display a…
Track duration in hrs?
Hi everyone. We have an upcoming project is very short, but very critical, and requires that we track not only the start and end dates, but also the start and end times. Currently our team has added two columns, start time & end time, and they would like to add a duration column that can track the planned duration in…