Forum Discussion

MrMilo's avatar
MrMilo
Copper Contributor
Aug 05, 2022

Matching 2 different data sets with client data

Hi There!

 

I have a problem I'm struggling to figure out, any help would be greatly appreciated!

 

I have 2 excel files with client data, I'm looking for an easy way to merge them into 1 by the client name. Saying that both data sets have different clients and a different amount of clients, I'm expecting only about 40% to match between the files. 

Here's an example:


What's the best way to approach this?

 

Thanks!

excel

3 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Aug 05, 2022

    Hi MrMilo 

     

    In principal https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 could do the job but won't be very efficient if you have a lot of columns. Alternatively Power Query can do that assuming you run Excel > 2013 on Windows (???)

    • MrMilo's avatar
      MrMilo
      Copper Contributor
      Aug 05, 2022
      Hi L z. thanks for the suggestion! I can confirm I'm running Excel >2013 on Windows. How should I approach this with Power Query?
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Aug 06, 2022

        Hi MrMilo 

         

        Not mandatory but easier, format the data in your 2 files https://support.microsoft.com/en-us/office/format-an-excel-table-6789619f-c889-495c-99c2-2f971c0e2370. For below steps it's assumed your tables are named (https://support.microsoft.com/en-us/office/rename-an-excel-table-fbf49a4f-82a3-43eb-8ba2-44d21233b114) respectively Table1 and Table2

         

        From a new workbook (assuming this is what you want):
        - Go to Data (tab) > Get Data > From File > From Excel Workbook > Select 1st file
        - In the Navigator > Select Table1 > At the bottom click on the arrow down next to "Load" > Load To... > Only Create Connection > OK
        - Go to Data (tab) > Get Data > From File > From Excel Workbook > Select 2nd file
        - In the Navigator > Select Table2 > At the bottom click on the arrow down next to "Load" > Load To... > Only Create Connection > OK

         

        On the right side of your screen you see the Queries & Connections pane with 2 queries:

        - Right-click on Table1 > Merge

        - Follow the steps - Starting at #3 - in shttps://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9

Resources