Google Sheets Forkus

Google Sheets Beginner Course 4 | Duplicates to be Unique values

How to deal with Duplicates with Adjacent Values in Sheets


　The task of integrating duplicates – are the same entries occurring more than one time in your data set – always happens when you have multiple columns where one has a list of duplicate names, IDs etc., while adjacent cells just have corresponding values. For example, imagine the following arises.

　While Column A contains the duplicate company names Column B has the amounts of their “Quarterly Net Income”. On the right-hand side, however, you find Column D and E that removed the duplicates from Column A with the incomes summed up accordingly. Yes, that’s what you aim to achieve in this course.

If you look at “Alphabet” as an example, that’s it. (↓)

　 This isn’t as easy as it looks if you do it manually, soon appears to be time-consuming process. As follows, let’s see how to do this quickly using ① Pivot Tables, and ② UNIQUE and SUMIF functions.

1. Using Pivot Tables


　 The fastest way to remove duplicates in Sheets is using Pivot Tables. The following 5 steps let you do this so easily.

1-1. How to Open Pivot Tables

  1. 　Click anywhere inside the table containing the records
  2. 　Go to the menu Data, then select Pivot table
  3. 　Select Existing Sheet, then set Location clicking the top left blank corner

1-2. How to Put Pivot Tables into Practice

  1. 　 The Pivot table editor shown, go to Rows and click Add ▼Companies
  2. 　On Values tab, click Add ▼Quarterly Net Income to get SUM

    3. 　 In Step 1 above, keep in mind that it’s not Columns but “Rows” tab though it seemingly returns a column by clicking Add. It’s confusing, but in the Pivot table editor, “Rows” only fetches the “Unique records” from original list (Column A) and set them as “Row headings”. So, at first sight, it just looks like a column but technically, it’s “Row headings (or titles, labels if you’re more familiar with them…)” that are supposed to lead rows later.

1-3. Tips

　Now you have the following spreadsheet shown below that all the duplicates are got rid of with Net Income summed up each other… by Pivot Table!

　Even though you wanted to get the AVERAGE, MIN, or MAX amount of each company’s Net Income, the Pivot table editor makes you add them so easily by clicking Add under Values.

2. Using Formulas


　The Unique function in Sheets allows you to make a list of only unique values from another list that has duplicates. On the other hand, the adjacent list that has corresponding numbers doesn’t have duplicates so that we can’t use the same. Instead, we sum up those numbers using the SUMIF function. The following 5 steps let you do this so easily.

　1. Use the Unique function

  1. 　Click the top cell where you start and type an equals sign (=) and UNIQUE.
  2. 　 Drag your mouse over the range (A2:A13) where you pull unique values.

    3. 　 Importantly, the formula just exists in the top cell. There is no need to copy down the formula like other functions. Just keep in mind that you have sufficient blank cells below the top cell as long as the formula can return all the unique values from the list.

　2. Use SUMIF function

  1. 　Click the top cell where you start and type an equals sign (=) and SUMIF.
  2. 　Set the syntax as “= SUMIF(Range to be tested, Criterion, Range to SUM)”
  3. 　So, it’s going to be “= SUMIF(A2:A13, D2, B2:B13)”
  4. 　Press F4 to make an Absolute Reference ($) in the ranges.

    5. 　 Size of Range to be tested against Criterion and Range to sum must be equal, then they’re recognized related to each other. In the above case, they’re “A2:A13” and “B2:B13”.

RECOMMENDこちらの記事も人気です。