3 Easy Ways to Find Duplicates in Google Sheets
The Find Duplicates tool in Google Sheets can be an invaluable time-saver when you’re trying to do data entry or eliminate redundant information from your spreadsheet. However, if you don’t know how to use it correctly, this tool can also return inaccurate results and create even more work for you than it saves. This guide will help you understand the ins and outs of finding duplicates in Google Sheets so that you can save yourself time and headache down the road!
Advanced Filter
One way to find duplicates is to use the ADVANCED FILTER tool. To do this, select the range of cells you want to search, then click Data > Advanced Filter. In the dialog box that appears, choose Copy to another location and then select the range of cells where you want the unique values to appear. Finally, click OK and your unique values will be copied over.
1) You can also create a spreadsheet with only the duplicates by doing the following:
2) Create a new sheet within your document by going to Insert > Spreadsheet or if it already exists, go to View > Select Window > Active Sheet. Make sure that you are on Sheet1 and not Sheet2. Click on A1, B1, C1…etc. until you have selected all of the rows which contain duplicates. On the menu bar, navigate to Data > Remove Duplicates. If there are any columns (excluding column A) which have duplicate data they will automatically be included in the Remove Duplicate Columns list. As you move through each column and decide what columns should be excluded from the removal process, check off those items from the list at the bottom of the screen. When finished with all three steps, remove any empty rows or columns left in your spreadsheet.
Text to Columns
One of the most common data clean-up tasks is finding and removing duplicate records. If you have a list of data in a Google Sheet, there are a few different ways you can go about finding duplicates. First, you could copy your data from the spreadsheet into another spreadsheet or text editor and use Text to Columns. In Text to Columns, simply click text on the top menu bar.
Select convert text to columns on the right side of your screen; this will create a column for each word in your text document. Once all of your words are organized into separate columns, you can search for duplicates by looking at each column individually.
The third way to find duplicates is by using advanced filters in Google Sheets. These advanced filters allow you to filter out rows that contain certain criteria. For example, if I want to filter out rows that only contain the letters a and b, I would type a b as my filter criteria in the box next to Show only rows that match these values. And lastly, when dealing with numbers it’s easy enough just to use a conditional formatting rule!
Match & Merge
One of the most useful features of Google Sheets is its ability to find and remove duplicates. This can be a huge time-saver when you’re working with large data sets. Match & Merge: To find duplicates, type =Match(A1,B1) into cell A2 (don’t forget to include the parentheses).
The first cell that matches will have (Match) appended to it. Click on this cell and drag your cursor down until all matched cells are highlighted. Select Remove Duplicate Rows from the dropdown menu that appears at the top of your screen. You’ll end up with just one row for each set of matching cells. For instance, if we had entered =Match(A1,B1) into cell A2 and dragged our cursor down the spreadsheet until all duplicate rows were highlighted, we would see two rows for row 2:
One where Moby Dick was found in column B and another where Diet was found in column B. Once we select Remove Duplicate Rows, these two rows would disappear, leaving us with only one. You may need to use the Undo feature in case you mistakenly delete some important information.
