Merging Data from two separate sheet based on unique ID

[pcsojka]

Hello everyone,


I have a question that I think this is the correct place for. I have two sets of data on two separate sheets. The data is connected by a unique study ID. There is at least one of each study ID in each set of data, and there are instances where there are multiple of each study ID in each data sets (e.x. 10 1003s on sheet 1, 1 1003 on sheet2). There should always be at least as many pieces of data on sheet 1 as there are on sheet 2 (e.x. there will never be 2 1005s on sheet 1 and 10 1005s on sheet 2). I want to combine the data from each sheet into a single row (the data with 1001 from sheet 1 and sheet 2 together on a single line on sheet 3). In instances where there are unequal number of IDs on sheet 1 and 2, I want to consolidate data based on having the closest date and time on ADMIT_DTTM on sheet 1 and teen_demographics_timestamp on sheet 2.

Does this make sense? If it makes sense, is it even possible?

If anyone has insight I would greatly appreciate it, as it will save me about many hours of manually consolidating

Example Workbook.xlsx

Example Workbook 2.xlsx

[sandy666]

is that what you want?

with Power Query

[pcsojka]

I think so! That is beautiful.

[sandy666]

I am glad I could help
so hit Add Reputation then mark this thread as Solved

have a nice day

[pcsojka]

Sorry I am a little confused. How can I take that power query and apply it to my actual data set?

[pcsojka]

Also because I am new to this, how to do I mark a thread as solved?

[sandy666]

Data tab
Show Queries
on the right side double click on Merge query
in new Power Query window choose Home tab then Advanced Editor

or just add more data to your source tables then right click on green table and select Refresh

from the top of your thread Thread Tools and select Solved (maybe because I have never done it before )

[pcsojka]

I am still struggling with this. I think an error was that in my actual data set the unique piece of data that connects the two worksheets is a medical record number (confidential). When I try to add more data It does not update appropriately and the information from the second sheet does not merge. I am worried this may be above my technical prowess.

[sandy666]

probably because your "actual data" structure is different from this what showed in example
I don't care about your data but structure must be the same as original

[pcsojka]

Okay I think I made sure the actual data structure is the same as the example and added it to the original post. Thank you so much for your help.

[sandy666]

My pleasure

[pcsojka]

Do you think you could reevaluate with the new data set to make sure the data structure is appropriate?

[sandy666]

just post new example and we will see

[pcsojka]

I added a new example worksheet to the initial message. I cannot figure out how to add another link to the reply messages.

[sandy666]

don't change example in the first post because all post after doesn't make sense
new example - new post

[sandy666]

btw. don't use blank rows in source tables , it also doesn't make sense

---

Click the Add Reputation at the foot of any of the posts of members who helped you

[sandy666]

or maybe you like this one