I want to sort (keep) rows by the same column values. For example,

enter image description here

As you can see above, I want to sort the rows by the same (matched) column values of code1 and code2 as follows:

enter image description here

Basically, I have two spreadsheets: one contains code1 and sex, and the other contains code2 and age. Ideally, if they contained the same code list, then I would just need to sort each and copy-paste the data from one to the other spreadsheet. But, the thing is one of the spreadsheet contains missing values, which I want to remove in order to make one perfect matched spreadsheet.

  • Unless I'm misunderstanding, it might be easier for you to use a VLOOKUP rather than sorting them in place. That would allow you to match to the code and pull in the right value. This would be done in a third range of cells, leaving your existing data intact. – Meow_ly Jul 27 '20 at 17:49
  • 1
    Thank you for your suggestion. I used vlookup and it did work for the first row. However, the 'table_array' of vlookup changes as I apply this formula to the remaining rows by scrolling it down... – Piemol Jul 27 '20 at 18:51
  • 1
    @Piemol Use the Absolute Addressing form for table_array – Ron Rosenfeld Jul 27 '20 at 19:01
  • Thanks! It worked! – Piemol Jul 27 '20 at 23:19

To answer your question: Create another column that's true/false based on whether or not code1 = code2, and use that to sort.

However, I strongly recommend you use this as an excuse to learn Power-Query instead! I think you'll love it! In Excel, Power-Query starts with the 'Get & Transform' section of the Data ribbon.

  1. Create a third spreadsheet.
  2. Stumble through using the "Data - Get & Transform" buttons in the ribbon to create a query for one of your two original spreadsheets. For now just close the Power-Query window.
  3. Repeat for your second original spreadsheet.
  4. Back in Excel, if you click "Data - Get & Transform - Show Queries" from the ribbon, you should see a sidebar of your two queries.
  5. Right click on your first query and click "Reference". This will create a new query that builds upon your old one and opens the Power-Query Editor window.
  6. Within the Power-Query Editor window, click "Merge". This will bring up a dialog box. Select your second query and highlight the primary key (code1 and code2) on each of the tables. Then take a look at the "Join-Kind" field and choose whatever works for you before clicking ok.
  7. When you merge, you're just creating a new column that contains the results of your merge. To expand them into data columns, you need to click the little icon on the right of that column's title. This will bring up a dialog box you can fill in.

Here's the awesome thing about Power-Query: You can stumble through it very easily without fear because you are not changing your original data! Just try it, see how it looks, change it... or scrap it and remove the step... or the entire query and start all over! If you change any queries or your source data changes, you can just do "Data - Refresh All" from the ribbon to completely recalculate your query results. Plus, you can do some amazing things like unpivot data! It's definitely worth learning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.