How to Sort Duplicates in Excel (3 Methods)

Sorting duplicates in Excel is crucial for data accuracy and analysis. By identifying and arranging duplicate values in a systematic order, users can easily detect patterns, anomalies, or inconsistencies within a dataset. Moreover, sorting duplicates enables users to make informed decisions based on accurate and well-organized information.

In this Excel tutorial, you will learn how to sort duplicates.

The following dataset contains some duplicate items and quantities in kg. Here, we have sorted the duplicates in alphabetical order from A to Z. Additionally, the corresponding quantities of the respective items are sorted in ascending order.

3 Methods to sort duplicates in Excel:

Using Sort Feature

The Sort feature allows users to arrange data in a specific order based on selected criteria. With the Sort feature, users can organize data alphabetically, numerically, or chronologically, either in ascending or descending order.

Here are 2 cases to explain how to sort duplicates in Excel using the Sort feature:

Case 1: Sorting Duplicates in Column

To sort a column using the Sort feature:

1. Select the cell range.
2. Go to the Data tab > Sort & Filter group > Sort A to Z.
3. Sort Warning box appears. In the Sort Warning box:
   • Check Expand the selection option
   • Press Sort

   

The sorted output will appear.

But if you’ve noticed, only a single column, Items, is being sorted. The corresponding quantities of the items are not ordered properly. So to sort all the columns, follow the steps:

1. Select the entire cell range.
2. Go to the Data tab > Sort & Filter group > Sort.
3. In the Sort dialog box, select:
   • Sort by: Items in the Column field > Sort On: Cell Values > Order: A to Z.
   • Then, click Add Level to insert a new level.

   

4. Next, select Quantity (kg) in the Then by field > Cell Values in Sort On > Smallest to Largest in Order.
5. Press OK.

Finally, you’ll see both the columns have been sorted.

Case 2: Sorting Duplicates in Row

To sort rows using the Sort feature:

1. Select the cell range.
2. Go to the Data tab > Sort & Filter group > Sort.
3. In the Sort dialog box, select Options.
4. In the Sort Options dialog box, check for Sort left to right and press OK.
5. Again, in the Sort dialog box, select:
   • Sort by: Row 6 in the Row field > Sort On: Cell Values > Order: A to Z.
   • Then, click Add Level.
   • Next, select Row 7 in the Then by field > Cell Values in Sort On > Smallest to Largest in Order.
   • Press OK.

   

The rows will get sorted accordingly.

Read More: How to Arrange Numbers in Ascending Order in Excel Using Formula

Using SORTBY Function

The SORTBY function sorts a range or array of data based on the values in another range or array. You can also specify the sorting order.

Here are the 2 cases of using the SORTBY function to sort duplicates:

Case 1: Sorting Duplicates in Column

To sort duplicates in columns using the SORTBY function:

1. Select a blank cell.
2. Apply the formula: =SORTBY(B7:C23,B7:B23,1,C7:C23,1)
   The sorted output will appear. Here, B7:B23 (Items) and C7:C23 (Quantity) are the cell ranges based on which we’ll sort the cell range B7:C23. 1 implies ascending order, so we’ll sort the range in ascending order. The formula sorts based on items at first, as B7:B23 is put before C7:C23. After that, the quantities get sorted for the respective items.
   Replace the cell ranges accordingly with your cell range and insert 1 for ascending order or -1 for descending order.

Case 2: Sorting Duplicates in Row

To sort duplicates in rows using the SORTBY function:

1. Select a blank cell.
2. Insert the formula: =SORTBY(C6:N7, C6:N6, 1, C7:N7, 1)
   The sorted output will appear. Here, C6:N6 (Items) and C7:N7 (Quantity) are the cell ranges based on which we’ll sort the cell range C6:N7. 1 implies ascending order, so we’ll sort the range in ascending order. The formula sorts based on items at first as C6:N6 is put before C7:N7. After that, the quantities get sorted for the respective items.
   Replace the cell ranges accordingly with your cell range and insert 1 for ascending order or -1 for descending order.

Read More: How to Sort Unique List in Excel 

Using SORT Function

The SORT function sorts the contents of a range or array based on the order you specify. This function can only sort based on a single column.

To sort duplicates using the SORT function:

1. Select a blank cell.
2. Apply the formula: =SORT(B7:C23,1,1)
   The sorted output will appear. Here, B7:C23 is the cell range to be sorted in ascending order as the second argument is 1. The range is sorted based on the first column.
   Replace B7:C23 with your cell range and specify the order and the column based on which you need to sort.Here, only a single column, Items, is being sorted but the quantities of the items aren’t in the right order.

Read More: How to Sort Numbers with Letter Suffix in Excel

Download Practice Workbook

You are welcome to download the practice workbook from the link below.

Conclusion

That’s all for today. I have elucidated 2 ways to sort duplicates in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other ways to sort duplicates that we might have missed here.

Frequently Asked Questions

How do I sort duplicates in reverse order in Excel?

To sort duplicates in reverse order, follow the same steps for sorting duplicates. Select cell range> Data tab > Sort & Filter group > Sort. In the Sort dialog box, select the column with duplicates, choose the order (descending), and add levels if necessary. Click OK to apply the reverse order sorting.

How do I count duplicates in Excel?

You can use the COUNTIF function in Excel to count duplicate values in a specific range. The formula would look something like this: =COUNTIF(Range, Criteria). The criteria would be the value that you want to count as a duplicate.

How do I find duplicates in Excel without deleting them?

1. Select the cell range you want to check for duplicates.
2. Go to the Home tab > Styles group > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. The Duplicate Values dialog box will open with the Light Red Fill and Dark Red Text format selected by default.
4. Click OK.

Related Articles

• How to Sort by Name in Excel
• How to Sort by Last Name in Excel
• How to Sort in Excel by Number of Characters
• How to Put Numbers in Numerical Order in Excel
• How to Sort Numbers in Excel
• How to Sort Merged Cells in Excel
• How to Sort Merged Cells of Different Sizes in Excel

<< Go Back to Sort in Excel | Learn Excel