Combine info from two excel sheet

Hi,

I have two separate excel sheets and I would like to combine information from both of them.

In sheet 1 I have participant ID, medications using (can be multiple per individual), medication dose, medication start and stop time.

In sheet 2 I have participant ID and visit dates whereby participants had an assessment.

For each participant I would like to record which medication they were on (and at what dose) during each assessment. The assessment date differs slightly between each individual.

Visit dates and start/stop times of medication are written in days. All participants had their first assessment on day 0. Therefore medication dates can be before that (i.e. -30).

It gets complicated because someone may have been on a drug at a certain dose for their first visit and then the same drug at another dose on subsequent visits.

Please let me know whether I can provide anymore info!

Thanks!

Hi Alan,

Thanks for your help. I have changed the format like you suggested (attached). Please may you give me more guidance regarding the next steps? (Vlookup or index/Match or Power Query or VBA). I am basically a complete beginner at excel.

Thanks,

Kate

Hi Alan,

The assessment day and the medication start/ stop date need to be compared.

So for participant 1, they had their first assessment on day 0, their second assessment on day 371, third on day 734 and fourth on 1098. Looking at
sheet 1, they were on 37.5mg drug 1 from day 0- 283. Therefore, they were on this drug/dose during their first assessment (day 0). For the rest of
their visits they were on 100mg drug 1. They were also on 2.5mg of drug 2 for the final assessment.

I am unable to do this manually as I have thousands of participants. Is there a way to do this in excel?

Thanks,

Kate

apologies I just realised you're on 2013 rather than 2016 so the use of TEXTJOIN isn't feasible; you can remove this and repeat the INDEX call for B & C - just less efficient (and verbose!)

Formula:

Please Login or Register  to view this content.

Hi JeteMc,

Thanks for taking the time to work on this. The layout of this solution works well but I was wondering whether I can get all the values to show up all at once rather than only seeing the answers when I select each participant via B2? Especially because the B2 cell only allows me to choose the first 6 participants.

Thanks!

@London123, I've reattached my earlier suggestion but with the 2013 compatible function outlined in prior post -- perhaps that will help?

Thanks JeteMC!

Now it gives the correct answer for the first participant but it just repeats this answer for each subsequent participant. Does it need updating again?

If you can, post an update sample which reflects your real life (devoid of sensitive info) and we can review.

Originally Posted by London123

I am obviously working with a larger spreadsheet than in the example (but all columns are the same) and so I have updated the number of rows (i.e. changing (Sheet1!$B$2:$B$29) to (Sheet1!$B$2:$B$3414)

just reading back the above, did you replace all references to $29 with $3414?

taking the formula JeteMc provided, which caters for the ID# being various data types, you would get:

Formula:

Please Login or Register  to view this content.

if the above doesn't work, could you please try

Formula:

Please Login or Register  to view this content.

where "x" represents a seemingly "blank" row, and advise the answer.

It's attached but now the cells are blank again. When I changed the drug names its was still 'working' as before (it worked for the first participant but then just repeated the answers for the other participants). However, when I changed the participant IDs it went blank again. I have tried using a letter and a number (the real IDs are 10 digits starting with a letter followed by 9 numbers. The first 5 digits of each ID are the same for all participants).

Thanks so much for all your help.

see the attached; your prior sample is linking to another workbook so I've updated this, the row references, and fixed a typo in the LOOKUP function.

Hi JeteMc,

Sorry for being such a pain. Regarding your last message at 1.58 its working now but only to row 24 (which is the size of my example file). Is there something in the formula which stops at this point?

Thanks,

Hi XLent,

In reply to your message sent today at 11.48 I have used the first formula and it is working well!

Thank you so much for all your help, you have save me many hours/ days of doing this manually.