When you’re working with data in Apple Numbers on your Mac, you may run into a situation where you have duplicates. This could be names, email addresses, products, colors, or something else. And if you have a lot of data in your spreadsheet, finding and removing those duplicates can be a challenge.
We’re going to 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 actually save you time in the long run, but again, only if you don’t have thousands of rows in your sheet.
Sort by One Column
- Select the table of data by clicking anywhere in the table and then clicking the circle on the top left. This is to the left of column A.
- Move your cursor over the column you want to sort by.
- Click the arrow that displays next to the column letter and choose either Sort Ascending or Sort Descending.
Sort by Multiple Columns
- Follow the same steps as above, but instead of selecting a Sort option in the shortcut menu, click Show Sort Options.
- The right-hand sidebar should open to the Sort
- Make sure that Sort Entire Table is selected in the first dropdown box.
- In the Sort by dropdown, choose the column and below that, pick Ascending or Descending.
- Another dropdown box should appear beneath where you can choose another column and its sort order.
- 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 then 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 that 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 will work, our example data will be product names in column A and our table has column headers in row 1.
- Add another column or move to an empty column in your sheet where you want the duplicate indicator.
- Click the cell in the second row, below the header, and open the Functions Editor by entering the Equal sign (=).
- 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 and enter a space if it’s not.
- Click the checkmark to apply the formula.
- 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.
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 example data as above using column A and our table has column headers.
- Add another column or move to an empty column in your sheet where you want the duplicate indicator.
- Click the cell in the second row, below the header, and open the Functions Editor by entering the Equal sign (=).
- Enter COUNTIF(A,A2) in the editor. A is the column and A2 represents the row.
- Click the checkmark to apply the formula.
- 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
If you use one of the above functions to identify your duplicates because you have a lot of data, then you likely want to remove them without searching manually, right? If so, you can simply jump to the top of our tutorial and use one of the sorting methods.
You can sort by the word Duplicate using the IF function, True, or False, or by numbers, if you use the COUNTIF function. Once you sort, you can simply delete the duplicate rows.
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 and for this task, you’ll use both a formula and a function in Numbers.
Merge the Data
For our example, we’re going to leave the Duplicate indicator column we used with the IF function because we’ll need it later. Then, we’re going to add another column to the right for our Totals.
- Click the cell in the second row, below the header, and open the Functions Editor by entering the Equal sign (=).
- Enter (B2)+IF(A2)=(A3),(H3),0 in the editor. (You can see a breakdown of these formula elements below.)
- Click the checkmark to apply the formula.
- 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.
(H3) 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 they are no longer formulas.
Using our same example, we’ll copy and paste the Duplicate and Total columns.
- Select both columns and then click Edit > Copy from the menu bar.
- Select the new columns where you want to paste them and click Edit > Paste Formula Results from the menu bar.
- 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 Duplicate indicator column that you keep using the sorting instructions at the beginning of this tutorial. You should see all of your duplicates grouped together so you can delete those rows.
You can next 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.
Note: Again, before you delete columns, rows, or other data from your spreadsheet, be sure that everything is correct and that you no longer need it.
Duplicates in Numbers
It’s bound to happen at one time or another, duplicates showing up in your Numbers spreadsheets. But with this tutorial, hopefully you can easily identify those duplicates, merge their data if needed, and then remove the duplicates for a cleaner sheet.
For more help with your spreadsheets, check out how to use conditional formatting in Numbers or add checkboxes, sliders, and popup menus in Numbers on Mac.
Read the full article: How to Find and Remove Duplicates in Numbers on Mac
