Forum Discussion

Adam_Kaye's avatar
Adam_Kaye
Copper Contributor
Mar 05, 2024
Solved

How to combine 3 pairs of data

I have a puzzle that feels like it should be solvable with a fairly straightforward solution, but so far I'm stumped.   We have 3 datasets for a series of clinical trials: Contacts, Trials, and Sit...
excel
Formulas and Functions
office 365
  • Lorenzo's avatar
    Lorenzo
    Mar 08, 2024

    Adam_Kaye 

     

    Thanks for your sharing. Don't get me wrong but I also asked + a few examples of what you expect. You know exactly what you expect, on my side I'm note sure. This potentially leads to a waste of time on both sides 😞 Attached is a 1st attempt - see sheet TEMP - based on my understanding that could be wrong...

     

    The duplicates are the complicating factor, but I'm sure there's a way to accommodate them

    Easy to say 🙂 Again you know why they exist and what you expect so you'll have to explain/give examples as I can't guess

     

    Hope this all makes sense

Lorenzo's avatar
Lorenzo
Silver Contributor
Mar 08, 2024

Adam_Kaye 

 

Attached is a sample with 3 input Tables and a query (Power Query) that lists all possible combinations. Probably not what you want but with what you shared it's hard to figure out what's ultimately expected 🙂

 

If you need more help with this, please share (i.e. with OneDrive) a workbook with representative data sets + a few examples of what you expect

Adam_Kaye's avatar
Adam_Kaye
Copper Contributor
Mar 08, 2024

Lorenzo - Thank you very much for your response.

 

I'm attaching the file now so you can see how the data is collected in pairs.

 

I'm just looking to populate the 3rd column ("Trial") on the 1st sheet ("Contact-Site") based on the relationships in the 2nd ("Contact-Trial") and 3rd ("Trial-Site") sheets.

 

The duplicates are the complicating factor, but I'm sure there's a way to accommodate them.

 

Thank you again for your help.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Mar 08, 2024

    Adam_Kaye 

     

    Thanks for your sharing. Don't get me wrong but I also asked + a few examples of what you expect. You know exactly what you expect, on my side I'm note sure. This potentially leads to a waste of time on both sides 😞 Attached is a 1st attempt - see sheet TEMP - based on my understanding that could be wrong...

     

    The duplicates are the complicating factor, but I'm sure there's a way to accommodate them

    Easy to say 🙂 Again you know why they exist and what you expect so you'll have to explain/give examples as I can't guess

     

    Hope this all makes sense

    • Adam_Kaye's avatar
      Adam_Kaye
      Copper Contributor
      Mar 11, 2024

      Lorenzo - Thank you again for your response.

       

      What you produced looks great. How did you do it?

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Mar 11, 2024

        Hi Adam_Kaye 

        I cleaned a bit the file for easier reading, please download the attached version

         

        How did you do it? With Power Query

        1. Formated your 3 data sheets as Excel Tables, respectively named them ContactSite, ContactTrial & TrialSite
        2. Loaded them to Power Query as Connections only
        3. First merge (= query MergedOnTrial) between ContactTrial & TrialSite on their respective field [Trial ID], then expanded field [Site ID]
        4. Second merge (= query MergedOnContractSite) between ContactSite & MergedOnTrial on their respective field [Contact ID]+[Site ID], then expanded field [Trial ID]

        To follow:

        • In Excel, go to Data (tab) > Queries & Connections. The corresponding pane opens on the right

        • Double-click on a query name (Power Query Editor opens)
        • Click on each step in the APPLIED STEP box:

Resources