Countif for Duplicates returns #NO MATCH
Hi there. I have a column that is concatenate of many columns and I want to find duplicates within that column. I have used the countif to find duplicates many times but have never had an issue. I'm just checking a box if there is more than one. Here is the formula I'm using: =IF(COUNTIF(String:String, String@row) > 1, 1)
The string is 16 columns of data that include Text, checks, and dates. The string looks good to me and pulls it all together just fine, not sure why the Countif isn't working.
Answers
-
Hi Christina,
Are you using a VLOOKUP or INDEX/MATCH formula in the columns?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
It does have one vlookup. I tried to go in there and remove that and it broke my string. Not sure why that would be. This is the string I'm working with:
=Badge@row + Name@row + Quarantine@row + [Self-Quarantine]@row + [Diagnosis or Treatment]@row + [Care for a Quarantined Individual]@row + [Child Care]@row + [Substantially Similar Care]@row + [FMLA School Closure]@row + [Sick Accrued Leave]@row + [Vacation Accrued Leave]@row + [Holidays, Floating Holidays, Birthday]@row + [Personal Time]@row + [COVID-19 Paid Sick Leave]@row + [Other Supportive Services]@row + [High Risk]@row + [Alternate-Vacation Accrued Leave]@row + [Alternate- Holidays, Floating Holidays, Birthday]@row + [Absence Start Date]@row + [Absence End Date]@row
The Name column is a vlookup and I don't really need that if I have the Badge because the purpose of this string is to compare for exact duplicate entries so long as I have one identifier (Badge) I'm fine. The above string works but like i said above, does not work with my duplicate countif. However, when i now try to remove the Name column to test if that vlookup is the culprit, it breaks my string. It's not a join so consecutive rows should not matter? Badge is just a Text/Number, Quarantine through FMLA is a checkbox, Sick Accrued Leave through COVID-19 Paid Sick Leave is a dropdown single select restricted, then the Absence dates are obviously dates.
-
Not sure I follow!
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!