How to Sort Duplicates in Excel – 3 Methods

The following dataset contains  duplicate items and quantities in kg. Duplicates were sorted in alphabetical order from A to Z. Quantities were sorted in ascending order.

Method 1 – Using the Sort Feature

Case 1: Sorting Duplicates in a Column

• Select the cell range.
• Go to the Data tab > Sort & Filter > Sort A to Z.
• In the Sort Warning box:
  • Check Expand the selection.
  • Click Sort.

  

This is the output.

To sort all the columns:

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

  

• Select Quantity (kg) in Then by > Cell Values in Sort On > Smallest to Largest in Order.
• Click OK.

This is the output.

Case 2: Sorting Duplicates in a Row

• Select the range.
• Go to the Data tab > Sort & Filter  > Sort.
• In the Sort dialog box, select Options.
• In the Sort Options dialog box, choose Sort left to right and click OK.
• In the Sort dialog box, select:
  • Sort by: Row 6 in the Row field > Sort On: Cell Values > Order: A to Z.
  • Click Add Level.
  • Select Row 7 in Then by > Cell Values in Sort On > Smallest to Largest in Order.
  • Click OK.

  

This is the output.

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

Using the 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.

Case 1: Sorting Duplicates in a Column

• Select a blank cell.
• Enter the formula: =SORTBY(B7:C23,B7:B23,1,C7:C23,1)

B7:B23 (Items) and C7:C23 (Quantity) are the cell ranges based on which the cell range B7:C23 is sorted.

1 implies ascending order. The formula sorts based on items, as B7:B23 is  before C7:C23.

• Replace the cell ranges and insert 1 for ascending order or -1 for descending order.

Case 2: Sorting Duplicates in a Row

• Select a blank cell.
• Enter the formula: =SORTBY(C6:N7, C6:N6, 1, C7:N7, 1)
  C6:N6 (Items) and C7:N7 (Quantity) are the cell ranges based on which range C6:N7 is sorted. 1 implies ascending order. The formula sorts based on items as C6:N6 is before C7:N7. Quantities are sorted for the given items.

• Replace the cell ranges and insert 1 for ascending order or -1 for descending order.

Read More: How to Sort Unique List in Excel 

Using the 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.

• Select a blank cell.
• Use the formula: =SORT(B7:C23,1,1)
  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.This is the output.

Download Practice Workbook

Download the practice workbook here.

Frequently Asked Questions

How do I sort duplicates in reverse order in Excel?

Follow the same steps. 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.

How do I count duplicates in Excel?

Use the COUNTIF function to count duplicate values in a specific range. The formula is  =COUNTIF(Range, Criteria). Criteria refers to the value 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 > 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