Duplicate data formula which references two values
Hello Community,
I am trying to find duplicate invoice numbers for the same client within two of my sheets.
I need to show if there have been any duplicate invoices for clients using two criteria but am unsure how to create the formula. I want this information to show in a checkbox form.
=IF(COUNTIF([Invoice #]:[Invoice #], [Invoice #]@row, Name:Name, Name@row>1, 1,0))
Appreciate any support on this.
Best wishes,
Chloe
Answers
-
You need to change from COUNTIF to COUNTIFS with the "S" on the end) to accommodate multiple range/criteria sets, and you will need to move one of the closing parenthesis from the very end to close out the COUNTIFS immediately after Name@row.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you for your help with this. I appreciate it. I am hoping you can assist further?
The formula still isn't working. I have used the below formula but as you can see from the screenshot, it doesn't show the duplicate value. It shows 0.
=COUNTIFS([Invoice #]:[Invoice #], [Invoice #]@row, Name:Name, Name@row > 1)
Thank you in advance!
-
You needed to keep the IF portion, and you still need to move the closing parenthesis to immediately after Name@row.
=IF(COUNTIFS([Invoice #]:[Invoice #], [Invoice #]@row, Name:Name, Name@row)>1, 1,0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi,
I have a similar situation but I cannot get the formulas to work. We have two separate sheets tracking property by using numerical property numbers. I would like "Property Sheet 1" to indicate when a duplicate property number has been entered by comparing the numbers from Column 1 on "Property Sheet 1" and Column 1 on"Property Sheet 2".
I've used this type of formula: =IF((COUNT([Column A]1:[Column A]12)) = (COUNT(DISTINCT([Column A]1:[Column A]12))), "No Dups", "Duplicates exist") but it only gives me the "Duplicates Exist" no matter if the data is duplicated or not.
I've tried this type of formula: =IF(COUNTIF(Store:Store, Store@row) > 1, 1, 0), but again everything is marked as "1" or checkmark (because I added a "Flag" Column to change to a "1" or "checkmark" if a duplicate number is found.
What am I doing wrong here?
Jackie
-
Hi @Jackie9
It sounds like you're wanting to compare data across sheets, but your formulas only have reference to in-sheet cells...there are no {cross sheet references}.
See: Create cross sheet references to work with data in another sheet
Try replacing the first "store" reference with the column in sheet 2:
=IF(COUNTIF({Store Column Reference}, Store@row) > 1, 1, 0)
If this hasn't helped, it would be useful if we could see screen captures of both sheet set-ups (but block out sensitive data or use demo data), with the formula open in the cell.
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!