How to Sort Duplicates in Excel (Columns and Rows)

Sorting is one of the most common tasks in the daily use of Excel. You need to perform various sorts; sort multiple columns, sort dates, sort data chronologically, and many more. Today we are going to show you how to sort duplicate values. For this session, we are using Excel 2019, you can use your preferred version.

First things first, let’s get to know about the workbook which is the base of today’s examples.

Dataset - How to Sort Duplicates in Excel

Here we have a dataset of several items along with their quantity. There are duplicate items on purpose. Using this dataset we will sort duplicate values.

Note that this is a simple dummy data to keep things straightforward. In real-life scenarios, you may encounter a much complex and bigger dataset.

Practice Workbook

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

Sort Duplicates in Excel

1. Sort in Columns

To sort duplicates in columns we first need to introduce a new column: Counter (we have named it Counter, you can name it as you wish).

Counter column - How to Sort Duplicates in Excel

In this column, we will count the number of times each item occurs in the Items column. We are going to use the COUNTIF function to find the occurrence numbers.

The COUNTIF function counts cells in a range that meets a single condition. To know more about this function, visit this COUNTIF article.

Here our formula will be

=COUNTIF($B$4:$B$21,B4)

COUNTIF formula - How to Sort Duplicates in Excel

Here, $B$4:$B$21 is the range which to count, and B4 is the criteria. This will provide the total number of Apples within the Items column.

COUNTIF result - How to Sort Duplicates in Excel

To find the instances for the rest of the items we are going to exercise the Excel AutoFill feature.

COUNTIF function AutoFill - How to Sort Duplicates in Excel

Now we can sort the data using this column.

I. Sort Ascending Order

To sort in ascending order select the Counter column and click A to Z in the Sort & Filter section from the Data tab.

Select column and A to Z - How to Sort Duplicates in Excel

Since only one column is selected from an entire table Excel will trigger you warning through the Sort Warning dialog box.

Expand the selection - How to Sort Duplicates in Excel

Unless you explicitly need to sort only the selected column, select the Expand the selection and click Sort.

You will find the sorted dataset. Notice the image below, the duplicate items are in ascending order.

Sort column in ascending order - How to Sort Duplicates in Excel

II. Sort Descending Order

This time, to sort in descending order again select the Counter column and this time click Z to A from the Sort & Filter section.

Select column and Z to A - How to Sort Duplicates in Excel

Again, the selection of a single column from an entire table will lead to the Sort Warning dialog box. Select the Expand the selection and click Sort.

Expand the selection - How to Sort Duplicates in Excel

The dataset will be sorted in descending order. See the image below

Sort in descending order - How to Sort Duplicates in Excel

2. Sort in Rows

We can sort duplicate values present within rows. To show you examples we have rearranged the data within rows.

Row data - How to Sort Duplicates in Excel

Our approach will be similar to the column sort, we will introduce a Counter row here.

Counter row - How to Sort Duplicates in Excel

We will fill up the Counter row using the COUNTIF function and the formula will be the following one

=COUNTIF($C$3:$L$3,C3) 

COUNTIF formula - How to Sort Duplicates in Excel

Here $C$3:$L$3 is the range to count and C3 is the criteria. We have used absolute reference for the range to keep this unchanged. This will provide the occurrence for the value in cell C3.

COUNTIF formula result - How to Sort Duplicates in Excel

Exercise the AutoFill feature to complete the row.

COUNTIF AutoFill - How to Sort Duplicates in Excel

Now using this row we will perform our sort operation.

I. Sort Ascending Order

To sort rows perfectly we need to select the rows together and click Sort in the Sort & Filter section from the Data tab.

Select Sort - How to Sort Duplicates in Excel

Now a Sort dialog box will pop up in front of you. From there select Options and you will find another dialog box having two options; Sort top to bottom and Sort left to right.

Sort left to right - How to Sort Duplicates in Excel

Select Sort left to right and click OK. Now you will find the rows listed in the Sort by drop-down box.  

Select row - How to Sort Duplicates in Excel

Here our Counter row is row number 5. Select Row 5 and click OK.

Select Row 5 - How to Sort Duplicates in Excel

You will find the data sorted in ascending order.

sort row in ascending order - How to Sort Duplicates in Excel

II. Sort Descending Order

Now, to sort in descending order we again need to select the rows and click Sort.

Select Sort - How to Sort Duplicates in Excel

Make sure to select Sort left to right from the Sort dialog box (shown in the previous section).

Now select row 5 in the Sort by field and Largest to Smallest in the Order field. Then click OK.

Select largest to smallest - How to Sort Duplicates in Excel

You will find the duplicate data in the row is sorted in descending order.

sort row in descending order - How to Sort Duplicates in Excel

Conclusion

That’s all for today. We have sorted duplicates within columns and rows 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.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo