Find Number of Duplicates within Multiple Columns
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.
Best Answer
-
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.
Answers
-
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.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
@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.
-
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.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
@Kleerfyre Thank you! I don't think I even knew about that Formula Handbook.
-
Yeah, that thing is very handy!
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
@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.
-
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.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
@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
-
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.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!