How to Sort Two Columns in Excel to Match (Both Exact and Partial Match)

While working in Excel, we often have to work with columns that are almost the same, and sometimes exactly the same. Today I will be showing how you can sort two columns with exactly the same items or almost the same items to match.

Download Practice Workbook

How to Sort Two Columns in Excel to Match

1. Matching Two Columns with Exactly the Same Items

Look at the set of data below. We have a list of the items produced by a company named APEX Group in the years 2019 and 2020.

If you look carefully, you will find that the two columns contain the same items, but in a different order.

Now we will try to sort the second column to match the first column.

We will use the MATCH function of Excel here.

Take a  new column (must be adjacent to the second column)  and enter this formula in the first cell.

Then double click on the Fill Handle.

• Here, C4 is the first cell of my second column, $B$4:$B$18 is the range of my first column. You use your one accordingly.

Next, select the new column and the second column and go to the Home>Sort & Filter option in Excel Toolbar.

Click on Sort & Filter. From the drop-down menus, select Custom Sort.

You will get the Custom Sort dialogue box. Select Sort by Column D (The new column), Sort On Cell Values and Order Sort Ascending.

Then click OK. You will get the two columns matched exactly like this.

Now if you wish, you can delete the new column.

2. Matching Two Columns with Not Exactly the Same Items

Case 2: First Column Having All the Items of the Second Column

Now, look at the new data set. We have again a list of items produced in two years, 2019 and 2020, which are not exactly the same.

But the first column has all the items of the second column.

Now we will try to sort the second column to match the first column.

And when we will not find a match in the second column, we will keep that cell empty.

Option 1: Sorting in the Original Location

First, we will try to sort the second column in its original location.

We will use the MATCH, FILTER, ROW, and COUNTIF functions of Excel.

Take a new column (must be adjacent to the second column) and enter this formula in the first cell.

Then drag the Fill Handle.

• Here, C4 is the first cell of my second column, and $B$4:$B$18 is my first column. You use your ones accordingly.

Then in the first blank cell of the new column, insert this formula:

• Here I have taken ROW(A1:A15) because my first column consists of a total of 15 items (B4 to B18). You use your one. For example, if your first column consists of 50 items, use ROW(A1:A50).
• D4:D11 is the range in the new column which was filled before applying this formula. You use your one.

Then you have to turn the formulas of the new column into values.

To do that, select the new column and press Ctrl + C.

Then right-click on your mouse and choose Values from Paste Option.

All the formulas in the new column will turn into values.

Now follow the procedure mentioned in section 1. (Select the second column and the new column, Go to Sort & Filter, Choose Custom Sort, in the Custom Sort dialogue box Sort by the new column).

After successfully following the above procedure, you will find the two columns matched like this.

Now if you wish, you can delete the new column.

Option 2: Sorting in a Different Location

Now we will try to sort the second column to match the first column in a different location.

We will use the IF and COUNTIF functions of Excel.

To do that select a new column and insert this formula:

See, the second column is sorted to match the first column.

Now if you wish, you can copy the first column to the right of it and check whether the two columns have completely matched or not.

Case 2: First Column Not Having All the Items of the Second Column

Now finally, look at this data set. We have again a list of items of two different years, 2019 and 2020, and they are not exactly the same.

The first column does not have all the items of the second column.

Now we will try to sort the second column to match the same items in the first column.

We will use the FILTER, COUNTIF, MATCH and ROW functions of Excel here.

Take a new column and insert this formula:

• Here C4:C18 is my second column, B4:B18 is my first column. You use your one accordingly.

Then in the first blank cell of the same column, inserts this formula:

• Here C4:C18 is my second column, B4:B18 is my first column. You use your one accordingly.

Then in the first cell of the next column, insert this formula:

• Here C4:C18 is my second column, B4:B18 is my first column. You use your one accordingly.

Then in the first blank cell of the same column, insert this formula:

• Here, I have used ROW(A1:A15) because each of my columns consists of 15 items, B4:B18 and C4:C18. You use it according to your needs. For example, if you have a total of 30 items, use ROW(A1:A30).
• F4:F12 is the range of the cells of the same column filled by the previous FILTER formula. You use it accordingly.

Next, you have to turn the two new columns from formulas to values. To do that follow the procedure mentioned in section 2.1.1

(Select the two new columns, press Ctrl + C, then right-click on your mouse and choose Values from the Paste Options)

Then you have to follow the procedure mentioned in section 1 (Select the two new columns, go to Sort & Filter, choose Custom Sort and in the Custom Sort dialogue box, Sort by Column F, the second new column)

After successfully following the above steps, you will find your second column arranged like this.

Then copy the first column and paste it right to the new column. You will see the same items have been matched beautifully.

Now, if you wish, you can delete the extra column for beautification.

Note:

You can also use this procedure when the lengths of the two columns are not the same.

For example, see, here I have used the procedure when the first column has items more than the second column.

And here I have used the procedure when the first column has items less than the second column.

Conclusion

Using these methods, you can sort two columns to match that have the same items or almost the same items. Do you know any other method? Or do you have any problems? Feel free to ask us.

Further Readings:

• How to Create Custom Sort in Excel (Both Creating and Using)
• Sort Column by Value in Excel (5 Methods)
• Sorting Columns in Excel While Keeping Rows Together
• How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
• How to Sort Multiple Columns in Excel
• Excel Sort By Date And Time [4 Smart Ways]