Sync data between columns to match corresponding dates?

[Disloe]

Hi everyone,

New to the forum, not to Excel but always managed to find the answers through forums like this.
I don't even know how to describe what I'm doing because I'm not familiar with the excel language, so it's hard to Google...

So, to my struggle: I have 2 datasheets, one with the the temperatures taken every 5 minutes (so column A is date and time (dd/mm h:mm) and column B the temp), the other datasheet is just one column (lets call it column C) with the date and time (same format as column A) of something occurring at random times. I need to organise these so that I can turn it into a chart so that I can plot the temperatures against the activity pattern in column C to try and see a pattern.

I hope any of this makes sense and some genius here can help me...

Thank you in advance

[FDibbins]

It kinda makes sense, but it would help if you provided a sample WB, showing what you have, and what you want (manually mocked up if necessary)

[Disloe]

Absolutely! So A and B are linked, C is the other (unrelated) data which I want to plot against the temperature in B. I want to see if there's a link between the occurrences in C and the temperatures in B, spread over time (because I'll add more columns with more variables, like rainfall and lunar %). Does this context help?


A B C
14/10 19:00 14.9°C 14/10 18:59
14/10 19:05 14.8°C 14/10 19:14
14/10 19:10 14.6°C 14/10 19:14
14/10 19:15 14.5°C 14/10 19:15
14/10 19:20 14.4°C 14/10 19:15
14/10 19:25 14.2°C 14/10 19:15
14/10 19:30 14.1°C 14/10 19:15
14/10 19:35 13.9°C 14/10 19:16
14/10 19:40 13.8°C 14/10 19:16
14/10 19:45 13.6°C 14/10 19:17
14/10 19:50 13.5°C 14/10 19:38
14/10 19:55 13.4°C 14/10 19:38
14/10 20:00 13.3°C 14/10 20:52
14/10 20:05 13.2°C 14/10 20:53
14/10 20:10 13.0°C 14/10 20:59
14/10 20:15 12.9°C 14/10 20:59

[FDibbins]

I did ask for a sample workbook lol, not a copy. I will see what I can do with this, but please upload the sample file (see yellow banner at the top)

[Disloe]

Sorry! Not familiar yet with forums, and I didn't do my reading...
I keep going in circles with the attachments though, uploading it in the other window but nothing seems to become available in this window. Close to throwing out my laptop!

[Disloe]

Oh, there it is... Heck

[FDibbins]

OK your file came through nicely, thank you. But you dont show what you want?

Sorry to be a pain, but I need to know what you want so I can put something together to help you

[Disloe]

So sorry to be vague, and I really appreciate that you keep trying to help.

What I'm trying to accomplish is to be able to see what the effect of temperature is on the activity in C, so if I can filter out all the date/times/temperatures from A/B that don't occur at the same approximate time as a value in C, I would get a long way. Ideally I would want to put this in a line chart, but lets take it one step at a time eh

[FDibbins]

Not sure if it was just your test file, but the temp and the 2nd date/time were text entries not real values.

OK see if this is heading us in the right direction?

Because you said "approximate" time, I added a "constant" that you can adjust as needed to see what affect different "approximations" will have for you. I started with 5 mins either way - adjust as needed.

	A	B	C	D
1	Date_Time	Temp	Occurance	0:05
2	14/10 19:00	14.9°C	14/10 18:59	14.9
3	14/10 19:05	14.8°C	14/10 19:14
4	14/10 19:10	14.6°C	14/10 19:14	14.6
5	14/10 19:15	14.5°C	14/10 19:15	14.5
6	14/10 19:20	14.4°C	14/10 19:15
7	14/10 19:25	14.2°C	14/10 19:15
8	14/10 19:30	14.1°C	14/10 19:15
9	14/10 19:35	13.9°C	14/10 19:16
10	14/10 19:40	13.8°C	14/10 19:16
11	14/10 19:45	13.6°C	14/10 19:17
12	14/10 19:50	13.5°C	14/10 19:38
13	14/10 19:55	13.4°C	14/10 19:38
14	14/10 20:00	13.3°C	14/10 20:52
15	14/10 20:05	13.2°C	14/10 20:53
16	14/10 20:10	13.0°C	14/10 20:59
17	14/10 20:15	12.9°C	14/10 20:59

D1 is the time variance that you can adjust (I used 5 mins...enter a time value in the format 0:05 etc)
D2=IF(AND(C2-$D$1<A2,C2+$D$1>A2),B2,"")
copied down

[Disloe]

Awesome, seems like a good start! But doesn't this assume the columns are in sync over time? Which unfortunately they're not. Often there are double entries in C (which can't be disregarded because it emphasises popular temperatures) or nothing at all for hours. So I guess I firstly need to sync column C with column A to make them align, is that even possible in excel?

[FDibbins]

OK so if they are not sync'd...and col C is actually on another sheet, do you just want to do a search for a date/time in C, and find an approximate match in A, and return B?

[Disloe]

Thanks again for responding and thinking with me, apologies for the late reply, I got side-tracked by other projects.

Your suggestion to do a search for a date/time in C, and find an approximate match in A, and return B seems like a fabulous idea! How would I go about that?


Thank you for bearing with me FDibbins

[JeteMc]

I feel that we might be in a better position to help if you could tell us the temperatures you expect to see in D2:D17, so that we can see if the results of our proposed formulas/code match the expected temperatures.