Rifat Hassan
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 nearly the same items to match. In this article, we will show 2 methods. Using these methods, you can easily sort two columns to match in Excel.
Download Practice Workbook
You can download the practice workbook from here.
2 Simple Methods to Sort Two Columns to Match in Excel
In this section, I will show 2 simple methods to sort two columns to match in Excel. In the first method, I will show sorting columns having exactly the same items, and in the second method, columns have different items. For demonstration, I have used a dataset that includes Items Produced in 2021 and Items Produced in 2022 of a company.
1. Sort Two Columns to Match with Exactly Same Items
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.
- First, take a new column Serial and type the following formula in Cell D5.
=MATCH(C5,$B$5:$B$14,0)- Then, press Enter.
- Further, use the Fill handle to copy the formula in the cells below.
- Now, select the range of cells C5:D14.
- Further, select Sort & Filter > Custom Sort in the Home tab.
- Then, in the Sort window choose 1 (value in Cell D5) in Sort by section, Cell Values in Sort on section and Small to Largest in Order section.
- Next, press OK.
- Finally, we will see the second column sorted according to the first column.
2. Sort Two Columns to Match with Partially Matched Items
Now it’s time to show you the method to sort two columns when the columns don’t contain exactly the same items.
2.1 If First Column Has All Items of Second Column
Suppose the second column has fewer items and the first column has all items of second column. I will show how to sort them in the second column or in a new column. I will use the MATCH, FILTER, COUNTIF, ROW functions in the procedures.
2.1.1 Sorting on Same Location
Let’s discuss the stepwise procedures to sort the second column in the same location.
- First, take a new column Serial and write the following formula in Cell D5.
=MATCH(C5,$B$5:$B$14,0)- Then, press Enter and use the Fill Handle to copy the formula up to Cell D10.
- Now, write the following formula in Cell D11.
=FILTER(ROW(A1:A10),COUNTIF(D5:D10,ROW(A1:A10))=0)
In the formula,
- I have taken ROW(A1:A10) because my first column consists of a total of 10 items (B5 to B16). You use your one. For example, if your first column consists of 50 items, use ROW(A1:A50).
- D5:D10 is the range in the new column which was filled before applying this formula.
- Now, copy the cell values from D5:D14 that contain the serial number.
- Consecutively, paste it as values.
- Finally, sort Column D and Column C following the procedures in Method 1.
- In this way, we will see the second column sorted according to the first column.
2.1.2 Sorting on Different Location
It’s time to show the procedures for sorting the data in a different location. I will use the IF and COUNTIF functions in this method. Follow the steps given below.
- First, take a New Column and write the following formula in Cell D5.
=IF(COUNTIF(C5:C110,B5:B14)>0,B5:B14,"")- Next, press Enter.
- Finally, we can see the second column’s data sorted according to the first column’s data in the New Column.
2.2 If First Column Doesn’t Have All Items of Second Column
The first column may not have all the items from the second column. I have used COUNTIF, ROW, FILTER and MATCH functions for this method. Follow the stepwise procedures given below to sort the columns.
- First, take two additional columns New Column and Serial beside the two columns.
- Secondly, write the following formula in Cell D5.
=FILTER(C5:C16,COUNTIF(B5:B16,C5:C16)>0)- Then, press Enter for Excel 365 or press Ctrl + Shift + Enter for previous versions as it is an array formula.
- Now, the first empty cell below the array formula result is Cell D11.rite the following formula in Cell D11.
=FILTER(C5:C16,COUNTIF(B5:B16,C5:C16)=0)- Further, press Enter for Excel 365 or press Ctrl + Shift + Enter for previous versions as it is an array formula.
- Afterward, type the following formula in Cell E5.
=FILTER(MATCH(C5:C16,B5:B16,0),COUNTIF(B5:B16,C5:C16)>0)- Later on, press Enter for Excel 365 or press Ctrl + Shift + Enter for previous versions as it is an array formula.
- Then, type the formula in Cell E11.
=FILTER(ROW(A1:A12),COUNTIF(E5:E10,ROW(A1:A12))=0)- After that, press Enter for Excel 365 or press Ctrl + Shift + Enter for previous versions as it is an array formula.
- Now, copy the data from Column D and Column E and paste only values in the same place.
- Finally, sort Column D and Column E just like in Method 1 and we will see the sorted data in New Column.
How to Find If Data of Two Columns Match in Excel
If we have two columns of data and want to check whether the data match or not, we can easily do that in Excel. For this purpose, we will use the IFERROR and VLOOKUP functions. Follow the stepwise procedures given below for determining the data match or not. For demonstration, I have included a dataset having Item 1 and Item 2 as well as Status.
- First, write the following formula in Cell D5.
=IFERROR(VLOOKUP(C5,$B$5:$B$15,1,0),"Data Doesn't Match")- Then, press Enter and use the Fill Handle to copy the formula in the cells following.
- As a consequence, we will see matched data in the cells, if the data don’t match then Data Doesn’t Match text will appear in the cells.
Conclusion
Sorting the data of one column with reference to another column is quite a useful tool in Excel. Here, I have shown 2 simple methods to sort two columns to match. If you have any queries, please leave a comment.
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]
It help me so much, I was strugle at 2. Matching Two Columns with Not Exactly the Same Items. Now, I know how to done it, thanks to you, cheers.
Hey,
Thank you for the helpful article!
I have one question to ask to “2. Matching Two columns with Not Exactly the Same Items”:
If i want to sort the data in the second coloumn (items produced in 2020) to match not only data added to the coloumn: items produced in 2019 (as you do here), but also data added to a coloumn more, called: items produced in 2018. and 1) the 2018 coloumn have the same data as the 2019 coloumn have, 2) their data are placed at the same rows and 3) the 2018 coloumn have some empty cells (for instance while 2019 coloumn have a cell with the text “monkey”, the cell beside (which belongs to 2018 coloumn) is empty. So How do i sort the data from the coloumn: items produced in 2020) not only to match one coloumn (items produced in 2019) but to match two coloums?(items produced in 2018 and items produced in 2019)
Thank you in advance.
Anton
Hello Anton,
First of all, you have to create three columns including items produced in 2018. Here, we need to paste the same value as items produced in 2019. Just one or two blanks in there.
Then, take a new cell and write down the following formula.
=IF(COUNTIF(D5:D12,B5:C19)>0,B5:C19,””)
Here, the range becomes (D5:D12 as the column of items produced in 2020 changes. Then, the criterion becomes B5:C19. Here, it follows if the condition match for both cells then it will return the value. Otherwise, it returns blank. After that, press Ctrl+Shift+Enter as it is an array formula.
Here, you can see, that the product Trousers is available in 2019 and 2020. but, we give criteria for products in 2018 and 2019. As they don’t find trousers in 2018, that’s why you won’t get them in the result. So, you will get the products that are available in both 2018 and 2019.
Hi,
Using your case 2 example, how would you align the items that do not match with a blank cell on both sides (instead of having them on the same row). E.g. blank – skirt & jacket – blank
Many thanks
Hello,
First of all, download the Practice Workbook for better understanding.
I think you’ve talked about the following phenomena. Let’s see the following image.
Now, you want to match the items in Column 2020 with the items in Column 2019.
For this, copy the cells in the B5:B12 range.
Then, paste them in the E5:E12 range.
After that, go to cell F5 and enter the following formula.
=IF(COUNTIF(C5:C12,B5:B12)>0,B5:B12,"")As usual, press ENTER.
Note: It’s an array formula. If you are using Microsoft Excel 365 then you can easily run the formula by pressing the ENTER key. Otherwise, you have to tap the CTRL+SHIFT+ENTER keys simultaneously to make the formula work.
Hope that will work for you. Follow our blog Exceldemy to learn more about Excel.