This tutorial demonstrates how to sort without duplicates in Excel and Google Sheets.
Sort Without Duplicates
Say you have the following list of numbers in Column B.
- First, identify the duplicate values in a helper column. In cell C2, enter the formula:
The COUNTIF Function counts how many times a cell from range B3:B12 is equal to B2. This does a search for any other cell in range B3:B12, that has a value from B2 (5). In this case, the result is 1, since cell B7 is the value 5. Therefore, if a value from Column B doesn’t have duplicates, the result of the formula is 0.
- Position the cursor in the bottom right corner of cell C2, until the cross appears.
- Drag the formula down to the end of the range (C12).
- The values with 0 in Column C, are those without duplicates. To include only these unique values, create another helper column; in cell D2, enter the formula:
- Drag the formula down to cell D12. As you can see, Column D contains only unique values from Column B.
- Now create another helper column to sort values from Column D without blanks. In cell E2, enter the formula:
ROW(D2) - ROW(D$2) + 1
This piece of the formula increases by one for every new row. For example, cell E2 returns position 1 from array D2:D12 using the LARGE Function, which is D2 (14); E3 returns position 2 (D3), etc.
- Drag the formula down to cell E12.
Now all values from Column B are sorted in descending order in Column E. If you want to sort from smallest to largest, you can use the same formula, just with the SMALL Function instead:
- Finally, you can copy and paste as values in Column E, delete helper Columns C and D, and delete error values (#NUM!) caused by empty cells.
Sort Without Duplicates in Google Sheets
You can use the exact same steps used in Excel to sort without duplicates in Google Sheets. The final output is the same as in Excel.