Matching cases and data

Hi Everyone,

I have data collected at 2 time points. Participants have a unique alphanumeric ID that is the same at both times. I need to merge the data into one sheet where each case has all of the information from time 1 and time 2. There are many more cases at Time 1 (nearly 2000) than at time 2 (around 340).

I have prepared an example of what I need (see below), but have lots (around 300 at each time point) more variables than what has been put in the sheet.

Any help would be great - I understand this has something to do with the lookup functions, but am not well versed in how to use them

Example.xlsx

Thanks in advance!

See attached file.

Thanks Turist! This works really well.

Just a couple more quick questions for you (or anyone else who can help)

(1) is there an easy way to do a horizontal fill with this formula - What I mean by that is I have about 100 columns of data in the array and when I try to fill horizontally excel doesn't automatically fill the column index number, rather it keeps the column index from the previous cell. I would love to not have to type in the column index 100 times
(2) the formula pastes in zeros where there was missing data - how do I make this missing instead of zero?


Here is the formula Turist provided =VLOOKUP($A2,'sheet1'!$A$2:$MY$344,4,0)

Thanks!

Originally Posted by stuartj1

Thanks Turist! This works really well.

Just a couple more quick questions for you (or anyone else who can help)

(1) is there an easy way to do a horizontal fill with this formula - What I mean by that is I have about 100 columns of data in the array and when I try to fill horizontally excel doesn't automatically fill the column index number, rather it keeps the column index from the previous cell. I would love to not have to type in the column index 100 times
(2) the formula pastes in zeros where there was missing data - how do I make this missing instead of zero?


Here is the formula Turist provided =VLOOKUP($A2,'sheet1'!$A$2:$MY$344,4,0)

Thanks!

Answer 1: Example formula VLOOKUP($A2,'sheet1'!$A$2:$MY$344,2,0) instedad of 2 use Column(B1) B1 column means 2
Like this second horizontal column's formula will be Column(C1) and so on.
example formula will be on its column : =VLOOKUP($A2,'sheet1'!$A$2:$MY$344,Column(D1),0)
in this logic D1 or D2500 doesn't matter. Only "D" is important. Column D means 4. (Column A is 1)
İn a worksheet in any cell if you write =Column() you will see its column value , if you write = row() , you will see its row value.

Answer 2:Please try the formula below

Please Login or Register  to view this content.

Thanks very much, the column command worked perfectly.

But I am getting an error with the second formula - I would also like it to include the column reference you provided so what I am trying is below. The error I am getting is with the 0, which I have coloured red=

Formula:

Please Login or Register  to view this content.

I really appreciate your help! I am on a timeline and this problem has been driving me crazy

Originally Posted by stuartj1

Thanks very much, the column command worked perfectly.

But I am getting an error with the second formula - I would also like it to include the column reference you provided so what I am trying is below. The error I am getting is with the 0, which I have coloured red=

Formula:

Please Login or Register  to view this content.

I really appreciate your help! I am on a timeline and this problem has been driving me crazy

There is liltle mistake in formula. Use the below:

Please Login or Register  to view this content.

Fantastic - big thanks it works perfectly