Combining Databases

%3CLINGO-SUB%20id%3D%22lingo-sub-1599605%22%20slang%3D%22en-US%22%3ECombining%20Databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599605%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20having%20trouble%20combining%20databases%20in%20excel.%20%26nbsp%3BI%20have%20two%20data%20sets%20of%20different%20sizes.%20Im%20going%20to%20try%20to%20explain%20this%2C%20sorry%20if%20it%20doesn't%20come%20out%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20set%20A%20-%20Main%20data%20set%3C%2FP%3E%3CP%3EData%20set%20B%20-%20A%20variable(PEEP)%20is%20assigned%20to%20a%20certain%20ID%20and%20time.%20The%20time%20intervals%20are%20not%20fixed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20larger%20database%20has%20it%20own%20time%20intervals.%20I%20need%20copy%20the%20PEEP%20from%20dataset%20B%20to%20dataset%20A%20but%20it%20has%20to%20be%20the%20right%20ID%20and%20closest%20to%20the%20day%20in%20data%20set%20A.%20Thanks%20for%20the%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20Set%20A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22230px%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3EID%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3EDay%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3EPEEP%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E1.0%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E1.4%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E2.1%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E2.4%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E3.0%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E3.8%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E3.9%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E4.0%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E4.1%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E4.4%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E4.6%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E4.8%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E5.1%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E5.4%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E5.6%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E6.0%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2268.109375px%22%20height%3D%2220%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2282.609375px%22%3E6.1%3C%2FTD%3E%3CTD%20width%3D%2278.296875px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20Set%20B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22331px%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3EID%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3EDay%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3EPEEP%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.00%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.03%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.07%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.11%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.20%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.24%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.28%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.32%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.45%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2297.5625px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2297.578125px%22%20height%3D%2230px%22%3E1.65%3C%2FTD%3E%3CTD%20width%3D%22134.890625px%22%20height%3D%2230px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1599605%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1599931%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20Databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F764874%22%20target%3D%22_blank%22%3E%40shuelska%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20807px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F213375iEDDB8B1E1E901F21%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20try%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(tblB%5BPEEP%5D%2C%0A%20%20%20%20%20MATCH(1%2CINDEX(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20(ABS(tblB%5BDay%5D-%5B%40Day%5D)%3DMIN(ABS(tblB%5BDay%5D-%5B%40Day%5D)%20)%20)*%0A%20%20%20%20%20%20%20%20%20%20%20%20%20(tblB%5BID%5D%3D%5B%40D%5D)%2C0)%2C%0A%20%20%20%20%200)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

IDDayPEEP
11.0 
11.4 
12.1 
12.4 
13.0 
13.8 
13.9 
14.0 
14.1 
14.4 
14.6 
14.8 
15.1 
15.4 
15.6 
16.0 
16.1 

 

Data Set B

IDDayPEEP
11.005
11.0310
11.0710
11.1110
11.2010
11.2410
11.2810
11.3210
11.4510
11.6510
1 Reply

@shuelska 

For such sample

image.png

you may try

=INDEX(tblB[PEEP],
     MATCH(1,INDEX(
             (ABS(tblB[Day]-[@Day])=MIN(ABS(tblB[Day]-[@Day]) ) )*
             (tblB[ID]=[@D]),0),
     0)
)