I have what I *think* is just a complicated IF/INDEX/MATCH problem that I have not quite been able to figure out. Hoping someone here can help.
I have two data sets which I am trying to merge. Dataset 1 contains a series of cases defined by a 9-digit serial number, a 4-digit ID number (this is needed as a unique identifier because the same serial # may appear multiple times in the set), a date, and a few columns of additional data. Dataset 2 contains 9-digit serial numbers, dates, and again, a few columns of (different) data. In this set, there are again multiple entries per serial number, but there is no unique identifying 4-digit ID number. The sets overlap completely in that they contain the same 9-digit serial numbers, but there are far more rows/entries in dataset 2.
To properly merge these sets, what I need to do is assign the right 4-digit ID number (along with corresponding date and data) from set 1 to the 9-digit serial numbers in set 2. Usually, for this type of problem, INDEX/MATCH is the solution I employ. However, what makes this a bit more complicated is that there are multiple unique 4-digit ID numbers for many of the 9-digit serial numbers (with the differentiating variable being a date), but simple INDEX/MATCH will only result in assigning one ID number to a given serial number. The right choice of ID number is a function of serial number AND date, and I cannot figure out how to create a formula to incorporate both of these properly.
Below I have provided some example data, including desired output. Basically, I want to match on serial number and merge "pet" data from dataset 1 with dataset 2 provided that the corresponding date for that entry in dataset 1 occurred before the date in for the ID number in dataset 2.
Example Set 1:
[TABLE="class: grid, width: 383"]
<tbody>[TR]
[TD]Serial #[/TD]
[TD]Pet[/TD]
[TD]Date of Pet purchase[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]cat[/TD]
[TD="align: right"]3/23/2012[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]dinosaur[/TD]
[TD="align: right"]3/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]cow[/TD]
[TD="align: right"]8/24/2010[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]bird[/TD]
[TD="align: right"]9/24/2013[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]frog[/TD]
[TD="align: right"]10/13/2010[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]snake[/TD]
[TD="align: right"]4/6/2012[/TD]
[/TR]
</tbody>[/TABLE]
Example Set 2:
[TABLE="class: grid, width: 356"]
<tbody>[TR]
[TD]ID #[/TD]
[TD]Serial #[/TD]
[TD]Date of score[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD="align: right"]1098[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/21/2012[/TD]
[TD]2R[/TD]
[/TR]
[TR]
[TD="align: right"]2056[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]3/6/2013[/TD]
[TD]1R[/TD]
[/TR]
[TR]
[TD="align: right"]1115[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/18/2013[/TD]
[TD]2R[/TD]
[/TR]
[TR]
[TD="align: right"]2251[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]7/25/2014[/TD]
[TD]2R[/TD]
[/TR]
</tbody>[/TABLE]
Ideal merged table appearance (though other forms of merge would be ok if this is too difficult - I can make data more 'wide' later):
[TABLE="class: grid, width: 727"]
<tbody>[TR]
[TD]ID #[/TD]
[TD]Serial #[/TD]
[TD]Date of score[/TD]
[TD]Score[/TD]
[TD]Pet 1[/TD]
[TD]Pet 2[/TD]
[TD]Pet 3[/TD]
[TD]Pet 4[/TD]
[TD]Pet 5[/TD]
[TD]Pet 6[/TD]
[/TR]
[TR]
[TD="align: right"]1098[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/21/2012[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2056[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]3/6/2013[/TD]
[TD]1R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1115[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/18/2013[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD]dinosaur[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2251[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]7/25/2014[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD]dinosaur[/TD]
[TD]bird[/TD]
[/TR]
</tbody>[/TABLE]
Any help from experts or ambitious amateurs would be appreciated. Thanks!
I have two data sets which I am trying to merge. Dataset 1 contains a series of cases defined by a 9-digit serial number, a 4-digit ID number (this is needed as a unique identifier because the same serial # may appear multiple times in the set), a date, and a few columns of additional data. Dataset 2 contains 9-digit serial numbers, dates, and again, a few columns of (different) data. In this set, there are again multiple entries per serial number, but there is no unique identifying 4-digit ID number. The sets overlap completely in that they contain the same 9-digit serial numbers, but there are far more rows/entries in dataset 2.
To properly merge these sets, what I need to do is assign the right 4-digit ID number (along with corresponding date and data) from set 1 to the 9-digit serial numbers in set 2. Usually, for this type of problem, INDEX/MATCH is the solution I employ. However, what makes this a bit more complicated is that there are multiple unique 4-digit ID numbers for many of the 9-digit serial numbers (with the differentiating variable being a date), but simple INDEX/MATCH will only result in assigning one ID number to a given serial number. The right choice of ID number is a function of serial number AND date, and I cannot figure out how to create a formula to incorporate both of these properly.
Below I have provided some example data, including desired output. Basically, I want to match on serial number and merge "pet" data from dataset 1 with dataset 2 provided that the corresponding date for that entry in dataset 1 occurred before the date in for the ID number in dataset 2.
Example Set 1:
[TABLE="class: grid, width: 383"]
<tbody>[TR]
[TD]Serial #[/TD]
[TD]Pet[/TD]
[TD]Date of Pet purchase[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]cat[/TD]
[TD="align: right"]3/23/2012[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]dinosaur[/TD]
[TD="align: right"]3/13/2013[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]cow[/TD]
[TD="align: right"]8/24/2010[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]bird[/TD]
[TD="align: right"]9/24/2013[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]frog[/TD]
[TD="align: right"]10/13/2010[/TD]
[/TR]
[TR]
[TD="align: right"]002241081[/TD]
[TD]snake[/TD]
[TD="align: right"]4/6/2012[/TD]
[/TR]
</tbody>[/TABLE]
Example Set 2:
[TABLE="class: grid, width: 356"]
<tbody>[TR]
[TD]ID #[/TD]
[TD]Serial #[/TD]
[TD]Date of score[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD="align: right"]1098[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/21/2012[/TD]
[TD]2R[/TD]
[/TR]
[TR]
[TD="align: right"]2056[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]3/6/2013[/TD]
[TD]1R[/TD]
[/TR]
[TR]
[TD="align: right"]1115[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/18/2013[/TD]
[TD]2R[/TD]
[/TR]
[TR]
[TD="align: right"]2251[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]7/25/2014[/TD]
[TD]2R[/TD]
[/TR]
</tbody>[/TABLE]
Ideal merged table appearance (though other forms of merge would be ok if this is too difficult - I can make data more 'wide' later):
[TABLE="class: grid, width: 727"]
<tbody>[TR]
[TD]ID #[/TD]
[TD]Serial #[/TD]
[TD]Date of score[/TD]
[TD]Score[/TD]
[TD]Pet 1[/TD]
[TD]Pet 2[/TD]
[TD]Pet 3[/TD]
[TD]Pet 4[/TD]
[TD]Pet 5[/TD]
[TD]Pet 6[/TD]
[/TR]
[TR]
[TD="align: right"]1098[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/21/2012[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2056[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]3/6/2013[/TD]
[TD]1R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1115[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]6/18/2013[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD]dinosaur[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2251[/TD]
[TD="align: right"]002241081[/TD]
[TD="align: right"]7/25/2014[/TD]
[TD]2R[/TD]
[TD]cow[/TD]
[TD]frog[/TD]
[TD]cat[/TD]
[TD]snake[/TD]
[TD]dinosaur[/TD]
[TD]bird[/TD]
[/TR]
</tbody>[/TABLE]
Any help from experts or ambitious amateurs would be appreciated. Thanks!