Find Number of Duplicates within Multiple Columns

Sara H

I need to create a COUNTIF formula (I assume) that will count the number of duplicates of a number in at least 10 different columns. I will eventually create a notification for the end result but I can't figure out how to do it over more than one column without only being able to compare the other columns to just one column.

I have combed through many different questions about duplicates and can't seem to find a formula that will work for my situation.

Paul Newcome

You would have to have it set up on a row by row basis so that "@row" is the criteria on every row. You can then create a report or filter to show any rows that are flagged if you wanted to just look at all duplicates in general.

Kleerfyre

You would have to use a COUNTIFS formula to do that. A COUNTIF will only count one criteria in one range. You would have to have multiple COUNTIFS formulas if you are looking for duplicates of different numbers. A single COUNTIFS is going to only do one number well.

=COUNTIFS([Employee ID 1]:[Employee ID 1], 1234, [Employee ID 2]:[Employee ID 2], 1234, etc...

Replace 1234 with your number you are looking for. You will have to have another formula for each number you are wanting to count duplicates for.

Sara H

@Kleerfyre Thank you. There might be hundreds of numbers and I don't know which would be duplicates. I was hoping to find a way to identify duplicates that showed up no matter which column they were in. For example, if there was a number in column 3 (example: an employee ID) and then the same number was in column 7, I was hoping for a flag saying "2" in a helper column to identify that the number showed up twice in any row in all 10 columns.

Kleerfyre

Have you looked at the Formula Handbook Template for all the different functions? This has been my go to in creating formulas that are unique to a sheet. I would look there and try to put together something. If you get stuck with that, I would be more than happy to help more if you needed it.

To find the Formula Handbook, go to the templates section in Smartsheet and just type Formula Handbook in the search there.

Sara H

@Kleerfyre Thank you! I don't think I even knew about that Formula Handbook.

Kleerfyre

Yeah, that thing is very handy!

Sara H

@Kleerfyre I don't see a way to do this without having a specific criterion to reference. I may need to try something else. I am a bit confused now.

Kleerfyre

Yeah, if you don't have a specific criteria to be able to reference, it will be hard to just pull any numbers that are duplicates from all columns. I will look in my collection of formulas that I have created to see if I have anything that might do what you are looking for.

Sara H

@Paul Newcome Would you take a look at the question above? I swear I've seen your name when helping with duplicates formulas. I think the challenge I am running into is 2 fold - I want the formula to look across multiple columns (I think that one can be managed) and and I don't have a specific criterion (I want it to look for any duplicates vs a specific duplicate). Any thoughts would be greatly appreciated!

EDITED: I think this will work - https://community.smartsheet.com/discussion/77471/highlight-duplicates-across-multiple-columns

Kleerfyre

So the only thing I have close to helping is the INDEX/MATCH formula set. Not sure if its dynamic enough to search for a value in one place and see if there are matches in another place. Maybe trying to figure out if a helper column/s could be used to help with this? I don't think I have much more than that.

Paul Newcome

You would have to have it set up on a row by row basis so that "@row" is the criteria on every row. You can then create a report or filter to show any rows that are flagged if you wanted to just look at all duplicates in general.