Excel: find and order matches by column

user_13882056 0
Viewed 116

I´m currently working with a huge epidemiological dataset with several Excel-files. The files contain pathology and clinical report for almost 30k patients. Each patient can have several pathology and clinical reports. The patients are assigned an unique ID.

I want combine all files into one so that ID for patient X001 would contain all the information form all the files. I cannot just copy/paste because the number of rows (IDs) in the files vary.

Here is an example of what I want to accomplish.

I want to combine two lists as follows.

As you can see that List1 and List 2 vary in row numbers. Also there are IDs in list1 that are not found in list2 and vice versa.

I want to merge them so that they align and match, see image below. Can someone provide a code for this? I cannot do this manually since I have 100k rows in list1 and 30k rows in list2...that would take several weeks to do with a risk of errors.

enter image description here

powerquery excel
1 Answers

You can merge tables combined utilizing Excels built in Power Query, which can be found under the Data tab.

Note: Photos are taken from Excel 2016

The first step is to create the queries:

  • Within the Get & Transform section under the Data click on New Query -> From File -> From Workbook and select the appropriate workbook that has the table you want to merge enter image description here
  • Select the appropriate sheets in which your tables are found, and confirm that they are displaying properly
    • If you notice that the table is not correct, you can make changes to it via the Edit button below.
      • For example, if you notice that your Column headers are being treated as a normal value, you can click Use First Row as Headers under the Power Query Editor Home -> Transform
      • I would also recommend changing the name of the query so it makes more sense down the line
  • Once you are happy with the way the query is looking, click on the Close and Load Dropdown menu under the Power Query Editor Home and select Close and Load To...
    • Select Only Create Connection to add it into your Workbook Queries without duplicating the table. enter image description here
  • Repeat the above steps for each table in which you are looking to merge.

Once you have all of your tables linked via Queries, you can now move on to merging them:

  • Under the same section of New Query select Combine Queries -> Merge enter image description here
  • Select the two queries you are looking to merge in each of the respective boxes
    • Confirm that they are correct via the preview window (don't worry if not all rows show)
    • Rule of thumb would also be to select your largest query first, and the smaller second
  • Next, highlight the columns in which you are looking to merge based on. For your example it would be the ID. This is done simply by clicking on the column within the preview
  • Finally change the Join Kind to Full Outer and click OK enter image description here

From here you should be back in the Power Query Editor

  • The final steps are modifying this merged query to your desired output
  • You should notice that there is a new column added next to your first original table with the name of the query at the top, next to the name is a button that allows you to expand out this query.enter image description here
  • Select the appropriate columns you would like to merge into the other table and click OK
  • If at any point you make a mistake, you can retrace your changes under Applied Steps within the Query Settings Pane
  • Once you are happy with the way your newly merged query looks, go ahead and click on Close and Load
  • Your should now have access to your new merged query that will update based on changes made to the original connected files
  • If you want to make any additional changes going forward from this point just click anywhere inside of the table and you should see both the Table Tools and Query Tools tabs appear at the top
user_119658280
Related