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.
Had a follow up question to my previous question located here. — I was using the original sheets, which I fixed with the solution from that thread, as a template. Today, I copied them over to essentially create the same system for a different LOB. I checked the box that updated all formulas to the new references. Now, in…
Hello there, looking for some feedback. I currently have this formula to identify FYQ. My FY goes from October-Sept. =IF(ISDATE([Referral Date]@row ) = false, "", IF(AND(YEAR([Referral Date]@row ) >= 2024, YEAR([Referral Date]@row ) <= 2025), IF(OR(MONTH([Referral Date]@row ) = 11, MONTH([Referral Date]@row ) = 12,…
This discussion was created from comments split from: Ready Status Solved - I hope this helps everyone.
