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

Two Columns of the Same Length but Not the Same have been matched.

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.

Two Columns of Exactly the Same in Excel

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.

=MATCH(C4,$B$4:$B$18,0)

Then double click on the Fill Handle.

MATCH Formula for the Same Columns

  • 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.

SORT and FILTER in Excel

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

Selecting Custom Sort from Sort and Filter

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

Custom Sort Dialogue Box

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

Two Columns Matched Exactly

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.

Two Columns of Not the Same Length

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.

=MATCH(C4,$B$4:$B$18,0)

Then drag the Fill Handle.

MATCH Formula for Two Columns Not of the Same Length

  • 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:

=FILTER(ROW(A1:A15),COUNTIF(D4:D11,ROW(A1:A15))=0)

FILTER Funtion to Sort Column to Match

  • 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.

Copying the New Column

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

Paste Value 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.

Two Columns of Not the Same Length Matched

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:

=IF(COUNTIF(C4:C11,B4:B18)>0,B4:B18,"")
[Array Formula. So do not forget to enter Ctrl + Press + Enter unless you are in Office 365.]

Sorting Column of Not the Same Length in a Different Position

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.

Matching Two Columns of Not the Same Length in Different Location


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.

Two Columns of the Same Length but Not 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:

=FILTER(C4:C18,COUNTIF(B4:B18,C4:C18)>0)

First FILTER Formula for Two Columns of Same Length but Not the Same

  • 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:

=FILTER(C4:C18,COUNTIF(B4:B18,C4:C18)=0)

Second FILTER Formula to Sort Two Columns to Match

  • 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:

=FILTER(MATCH(C4:C18,B4:B18,0),COUNTIF(B4:B18,C4:C18)>0)

Third FILTER Formula to Sort Two Columns to Match

  • 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:

=FILTER(ROW(A1:A15),COUNTIF(F4:F12,ROW(A1:A15))=0)

Fourth FILTER Formula to Sort Two Columns to Match

  • 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.

Two Columns of the Same Length but Not the Same have been matched.

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.

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.

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:

 

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo