Sometimes you want to find duplicate rows in a Google spreadsheet, but don’t want to examine every column — maybe you want to exclude a timestamp or other columns.
This Google Apps Script takes a list of columns that you care about. Â Then it temporarily copies those columns to a new sheet, looks for duplicates, highlights the duplicate rows in red on the main sheet, and removes the temporary sheet.
The script is attached to this Google Spreadsheet. Make a copy of this sheet, or just copy the script. Â You can do that by going to Tools – Script Editor and copying the script to the script editor of your own spreadsheet. Then edit the line that defines the important columns. After you re-open your spreadsheet, the “My Scripts” menu will show up. The first time you run the script, it will bring up the permissions dialog. You’ll need to run it again to make it work.
Here’s a video of it in action:
Karl,
Thanks for the script! It was 99% what I was looking for, just need to see if I can make the search not case sensitive. 🙂
Scott
Scott,
Glad to be of assistance. Probably all you need to do is go into Tools – Script editor, find this line: if (data[i].join() == data[j].join()) and change it to something like:
if (data[i].join().toLowerCase() == data[j].join().toLowerCase())
Karl
Karl, that’s what I had in mind, but now I don’t need to lookup the function name 🙂 Thanks again!!
Scott
Great code Karl. Thanks for sharing. I was wondering if you could give advice on how to merge two separate rows in a Google Sheet that have one cell in common (ie. both row 1 and 3 of a hypothetical dataset have 1 cell that has the value 15. How to merge the two rows into one at the shared cell value)?
Thanks in advance!
Vasu
Vasu,
How about if you create a short spreadsheet that has some examples in it for me. Maybe a few rows that should be merged, and a row showing what the data should look like after a merge (are we concatenating text, or summing numbers, etc). Then share the spreadsheet with karl.kranich@gmail.com and I’ll see what I can do.
Karl
Thanks Karl. Will email you the spreadsheet.
All the bests,
Vasu
I clicked on the link but it doesn’t take me to the template. It takes me to a gallery. Is there another way I can access the code?
Hi Marjorie,
I’ll take a look at what happened to the link.
Marjorie,
I edited the post to point to a shared Google Sheet, since I’m not sure how to use the templates gallery any more.
Karl
Hi Karl,
Thanks so much! I’m a beginner using Google Scripting language (and not a programmer) so this helped me out tremendously! Did exactly what I wanted it to do!!
Awesome that you put this out there for people!
Hello!
Is it also possible to search across multiple sheets for duplicates?
That would mean the world to me!!!!