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.
Hi all, I'm having some issues with an index-collect formula. I have (3) existing sheets that list a unique project name (text field), as well as a producer assigned to work that project. The producer column in all (3) sheets is set as a contact field. In a separate sheet, I'm trying to auto-populate the producer…
I am currently in business trial version, but i was told it is supposed to allow for full functionality, however when I click Ctr+F my search menu is only a search bar that allows me to only find the string, and it doesn't allow for replacing. Is it me or the F&R functionality rather limited?
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…
