Mar 05 2024 07:24 AM
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 Sites
Those datasets work together in pairs:
Is there a way to use Excel to identify Contacts, Trials, and Sites in every possible combination?
Though I'm not certain this was the right approach, I started down the path to create a many to many relationship model in Power Pivot, but I eventually hit a roadblock - not knowing what to do once the connections between the tables were established.
A teammate has suggested that nested VLOOKUPs might offer a solution, but I have yet to head down that path.
If you have any recommendations for how to accomplish this, I'd really appreciate it. Thank you!
Mar 08 2024 02:51 AM
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
Mar 08 2024 08:23 AM
@L z. - 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.
Mar 08 2024 09:33 AM
Solution
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
Mar 11 2024 07:19 AM
Mar 11 2024 08:09 AM
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
To follow:
Mar 08 2024 09:33 AM
Solution
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