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
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
- 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?
- Select the cell range you want to check for duplicates.
- Go to the Home tab > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- The Duplicate Values dialog box will open with the Light Red Fill and Dark Red Text format selected by default.
- 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
Get FREE Advanced Excel Exercises with Solutions!
