First, assign each unique name a number in column B with the following formula: If you want to try without the script, here's an example for a short list of names in A2:A8. I'm afraid you won't be able to do that with one simple formula or so. And there's an option to ignore 1st instances as well: There's a separate step for each action so you won't get confused:īesides, it knows how to highlight not only duplicates but also uniques. The add-on offers an intuitive way to select your data and pick columns you'd like to check for duplicates. It takes just a few clicks on 4 steps, and the option to highlight found duplicates is just a radio button with a color palette: Remove Duplicates add-on for Google Sheets will highlight duplicates for you. If you're not ready to devote your time to those, there's an easier solution. Nonetheless, any formula and conditional formatting require a learning curve. Of course, you may have some other use case that requires another formula. If so, every current row will be treated as another instance or, in other words, as an actual duplicate that will be colored.įormula-free way to highlight duplicates - Remove Duplicates add-on for Google Sheets It lets each row to look only above to see if there are the same rows. Instead of mentioning all rows like in the first formula, I use only the first cell of each column. So here's the formula I suggested right above for all duplicate rows:Īnd this is the formula you need to highlight only duplicate instances in Google Sheets: With just one change in the formula, you'll be able to highlight these 'real' duplicate rows - not the first entries, but their 2nd, 3rd, 4th, etc instances. Let's suppose you'd like to keep the 1st entries of duplicate rows intact and see all other occurrences if there are any. Highlight actual duplicates - 2n, 3d, etc instances You may learn more about COUNTIF and the concatenation in Google Sheets in the related articles.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |