Aug 19 2020 11:51 AM
Hi, I am having trouble combining databases in excel. I have two data sets of different sizes. Im going to try to explain this, sorry if it doesn't come out right.
Data set A - Main data set
Data set B - A variable(PEEP) is assigned to a certain ID and time. The time intervals are not fixed.
The larger database has it own time intervals. I need copy the PEEP from dataset B to dataset A but it has to be the right ID and closest to the day in data set A. Thanks for the help!
Data Set A
ID | Day | PEEP |
1 | 1.0 | |
1 | 1.4 | |
1 | 2.1 | |
1 | 2.4 | |
1 | 3.0 | |
1 | 3.8 | |
1 | 3.9 | |
1 | 4.0 | |
1 | 4.1 | |
1 | 4.4 | |
1 | 4.6 | |
1 | 4.8 | |
1 | 5.1 | |
1 | 5.4 | |
1 | 5.6 | |
1 | 6.0 | |
1 | 6.1 |
Data Set B
ID | Day | PEEP |
1 | 1.00 | 5 |
1 | 1.03 | 10 |
1 | 1.07 | 10 |
1 | 1.11 | 10 |
1 | 1.20 | 10 |
1 | 1.24 | 10 |
1 | 1.28 | 10 |
1 | 1.32 | 10 |
1 | 1.45 | 10 |
1 | 1.65 | 10 |
Aug 19 2020 02:16 PM
For such sample
you may try
=INDEX(tblB[PEEP],
MATCH(1,INDEX(
(ABS(tblB[Day]-[@Day])=MIN(ABS(tblB[Day]-[@Day]) ) )*
(tblB[ID]=[@D]),0),
0)
)