Sign in to join the conversation:
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.
Hi,
Here is how I worked this request -
See the screenshot - I hope this helps?
Sean
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
2
1
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)
Good job Paul.
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…
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:…
I looking to find unique values, in order, and separate them into columns. The "Client Update Date" column will contain dates, not in order and some dates may be duplicated. In the "1st Date Feedback I'm getting the correct date of 12/12/25 with the formula: =MIN([Client Update Date]:[Client Update Date]) In the "2nd Date…
