Countif for Duplicates returns #NO MATCH

Christina Riot

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.

Andrée Starå

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!

Christina Riot

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.

Andrée Starå

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)