How to Find and Remove Duplicates in Numbers on Mac

When you’re working with lots of data in Apple Numbers on your Mac, you may run into a situation where you have duplicates. These could be names, email addresses, products, colors, or something else.

Finding and removing duplicates can be challenging if you have a lot of data in your spreadsheet. Numbers doesn’t have a “remove duplicates” button, after all.

Here, we’ll show you a few different methods you can use to find duplicates and then either mark them or eliminate them if you like.

Find Duplicates in Numbers With Sorting

If you don’t have a lot of data in your spreadsheet, you can sort it and check for duplicates manually. This method might save you time in the long run, but only if you don’t have thousands of rows in your sheet.

Sort by One Column

If you only need to sort a single column in your spreadsheet to find duplicates, follow these steps:

  1. Select the table of data by clicking anywhere in the table.
  2. Move your cursor over the column you want to sort by.
  3. Click the arrow next to the column letter and choose either Sort Ascending or Sort Descending from the action menu that appears.

Sort by Multiple Columns

If you need to sort by multiple columns to find duplicates in your spreadsheet, use these instructions instead:

  1. Follow the same steps as above, but instead of selecting a Sort option in the action menu, click Show Sort Options.
  2. The right-hand sidebar should open to the Sort menu.
  3. Make sure that Sort Entire Table is selected in the first dropdown box.
  4. In the Add a Rule dropdown box below the Sort rows by multiple columns header, choose the first column you want to sort by. Below that, pick Ascending or Descending.
  5. The Sort rows header should now be Sort by, and another Add a Rule dropdown box should appear beneath the first rule you made. Choose another column from the Add a Rule dropdown and select its sort order.
  6. The data should sort automatically, but if not, click the Sort Now button at the top of the sidebar.

Once you sort your data, you should more easily be able to spot the duplicates and mark or remove them as you need to.

Find Duplicates in Numbers With Functions

There are two built-in functions in Numbers that you can use to find duplicates. These are the IF and COUNTIF functions. IF can display duplicates as True or False or a word you assign. COUNTIF will display how many times an item appears to indicate duplicates.

Find Duplicates With the IF Function

To illustrate how the function works, our example data will show product names in column A, and our table has column headers in row 1. You need to have sorted your data per the above methods for this to work. Just follow these steps for your own spreadsheet:

  1. Add another column or move to an empty column in your sheet where you want to add the duplicate indicator.
  2. Click the cell in the second row of the new or empty column below the header, and open the Functions Editor by entering the Equal sign (=).
  3. Enter IF(A2)=(A1), “Duplicate”,” “in the editor. This will compare the cell with the one above it and enter the word Duplicate if it’s a duplicate. If it’s not a duplicate, it will enter a space. You can change A2 and A1 to B2 and B1 depending on the column you want to search for duplicates within.
  4. Click the checkmark to apply the formula.
  5. Copy the formula to the subsequent cells by clicking the cell it’s in and dragging it down the column when you see the yellow circle on the border.

Want the “Duplicate” cells to stand out even more? Try using conditional formatting in Numbers to make them a different color so you know which rows to delete.

If you prefer not to use a word of your own and just display True for duplicates and False for non-duplicates, you can simply enter (A2)=(A1) in the editor. This works without adding IF before it.

Find Duplicates With the COUNTIF Function

We’ll use the same pre-sorted example data as above using column A and our column headers. Here’s how to use the COUNTIF function to find duplicates:

  1. Add another column or move to an empty column in your sheet where you want the duplicate indicator.
  2. Click the cell in the second row below the header of the new or empty column, and open the Functions Editor by entering the Equal sign (=).
  3. Enter COUNTIF(A,A2) in the editor. A is the column, and A2 represents the row.
  4. Click the checkmark to apply the formula.
  5. Copy the formula to the subsequent cells the same way as Step 5 above.

You should now see numbers in that new column showing how many times the item in your duplicate column appears. For our example in the screenshot above, you can see that Cap appears three times, Coat once, and Gloves twice.

Remove Duplicates From Numbers

You can manually remove duplicates using the IF or COUNTIF function, finding each cell that says Duplicate, True, or any number over 1 and deleting them one by one. Sorting the formula column lets you remove the duplicates much faster though, but you need to be careful you don’t delete the originals as well as the duplicates.

Merge and Delete Duplicates From Numbers

Maybe you do want to remove the duplicates, but you also don’t want to lose any data. For instance, you might have inventory data for products, like in our example. So you want to total those amounts before you delete the duplicates. To do this, you’ll need to merge the data first. For this task, you’ll use both a formula and a function in Numbers.

Merge the Data

For our example, we’ll leave the Duplicate indicator column we used with the IF function because we’ll need it later. Then, we’ll add another column to the right for our Totals.

  1. Click the cell in the second row of the new column below the header, and open the Functions Editor by entering the Equal sign (=).
  2. Enter (B2)+IF(A2)=(A3),(D3),0 in the editor. (You can see a breakdown of these formula elements below.)
  3. Click the checkmark to apply the formula.
  4. Copy the formula to the subsequent cells.

Formula breakdown:

  • (B2) is the cell containing our first quantity.
  • + will add that quantity to what follows.
  • IF(A2)=(A3) checks for a duplicate between the two cells.
  • (D3) is where the result of the quantity total will display.
  • 0 will be added if there’s no duplicate.

Once you finish merging the data, it’s important that you double-check to be sure everything adds up correctly.

Delete the Duplicates

To remove the duplicates after you merge data, you’ll use the sort action again. But first, you need to create new columns to copy and paste the data results as values so that they are no longer formulas.

Using the same example, we’ll copy and paste the Duplicate and Total columns:

  1. Select both columns and then click Edit > Copy from the macOS menu bar.
  2. Select the new columns where you want to paste them and click Edit > Paste Formula Results from the menu bar.
  3. Delete the columns with the formulas by selecting them again and right-clicking or clicking the column header arrow and choosing Delete Selected Columns.

Now, you can sort by the remaining Duplicate indicator column using the sorting instructions at the beginning of this tutorial. You should see all your duplicates grouped together so you can delete those rows.

You can also remove the original Quantity and Duplicate columns you used for the functions and formulas. This will leave you with no duplicates and merged data.

You can also filter your Duplicate column so that you only see your non-duplicated data without deleting anything:

  1. Go to Organize > Show Sort Options, and click on the Filter tab.
  2. Select your Duplicates column under the Add a Filter dropdown.
  3. In the Choose a Rule window that appears, select Text in the left menu, then Is not from the right one.
  4. A Text is not dropdown will appear. Type “Duplicate” in the text box below it, and hit Enter.
  5. Your table will now only show the non-duplicate entries in your spreadsheet!

Remember, before you delete columns, rows, or other data from your spreadsheet, be sure that everything is correct and that you no longer need it.

Dealing With Duplicates in Numbers

It’s easy to have duplicates in your Numbers spreadsheets, whatever you’re calculating. Hopefully, the steps we’ve outlined above help you quickly identify any duplicates, merge relevant data about them, and remove them for a clean sheet.

We also hope having this knowledge encourages you to use Numbers more often. It’s a great built-in Mac program that can do a lot if you take the time to learn about it.

[quads id=2]
Read the full article here

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button