+ Reply to Thread
Results 1 to 13 of 13

Sync data between columns to match corresponding dates?

  1. #1
    Registered User
    Join Date
    11-29-2021
    Location
    Auckland
    MS-Off Ver
    MS Office mac - 16.48
    Posts
    35

    Sync data between columns to match corresponding dates?

    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

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Sync data between columns to match corresponding dates?

    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)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-29-2021
    Location
    Auckland
    MS-Off Ver
    MS Office mac - 16.48
    Posts
    35

    Re: Sync data between columns to match corresponding dates?

    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.9C 14/10 18:59
    14/10 19:05 14.8C 14/10 19:14
    14/10 19:10 14.6C 14/10 19:14
    14/10 19:15 14.5C 14/10 19:15
    14/10 19:20 14.4C 14/10 19:15
    14/10 19:25 14.2C 14/10 19:15
    14/10 19:30 14.1C 14/10 19:15
    14/10 19:35 13.9C 14/10 19:16
    14/10 19:40 13.8C 14/10 19:16
    14/10 19:45 13.6C 14/10 19:17
    14/10 19:50 13.5C 14/10 19:38
    14/10 19:55 13.4C 14/10 19:38
    14/10 20:00 13.3C 14/10 20:52
    14/10 20:05 13.2C 14/10 20:53
    14/10 20:10 13.0C 14/10 20:59
    14/10 20:15 12.9C 14/10 20:59

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Sync data between columns to match corresponding dates?

    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)

  5. #5
    Registered User
    Join Date
    11-29-2021
    Location
    Auckland
    MS-Off Ver
    MS Office mac - 16.48
    Posts
    35

    Re: Sync data between columns to match corresponding dates?

    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!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-29-2021
    Location
    Auckland
    MS-Off Ver
    MS Office mac - 16.48
    Posts
    35

    Re: Sync data between columns to match corresponding dates?

    Oh, there it is... Heck

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Sync data between columns to match corresponding dates?

    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

  8. #8
    Registered User
    Join Date
    11-29-2021
    Location
    Auckland
    MS-Off Ver
    MS Office mac - 16.48
    Posts
    35

    Re: Sync data between columns to match corresponding dates?

    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
    Last edited by Disloe; 11-30-2021 at 02:23 AM.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Sync data between columns to match corresponding dates?

    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.9C
    14/10 18:59
    14.9
    3
    14/10 19:05
    14.8C
    14/10 19:14
    4
    14/10 19:10
    14.6C
    14/10 19:14
    14.6
    5
    14/10 19:15
    14.5C
    14/10 19:15
    14.5
    6
    14/10 19:20
    14.4C
    14/10 19:15
    7
    14/10 19:25
    14.2C
    14/10 19:15
    8
    14/10 19:30
    14.1C
    14/10 19:15
    9
    14/10 19:35
    13.9C
    14/10 19:16
    10
    14/10 19:40
    13.8C
    14/10 19:16
    11
    14/10 19:45
    13.6C
    14/10 19:17
    12
    14/10 19:50
    13.5C
    14/10 19:38
    13
    14/10 19:55
    13.4C
    14/10 19:38
    14
    14/10 20:00
    13.3C
    14/10 20:52
    15
    14/10 20:05
    13.2C
    14/10 20:53
    16
    14/10 20:10
    13.0C
    14/10 20:59
    17
    14/10 20:15
    12.9C
    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

  10. #10
    Registered User
    Join Date
    11-29-2021
    Location
    Auckland
    MS-Off Ver
    MS Office mac - 16.48
    Posts
    35

    Re: Sync data between columns to match corresponding dates?

    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?

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Sync data between columns to match corresponding dates?

    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?

  12. #12
    Registered User
    Join Date
    11-29-2021
    Location
    Auckland
    MS-Off Ver
    MS Office mac - 16.48
    Posts
    35

    Re: Sync data between columns to match corresponding dates?

    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

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,535

    Re: Sync data between columns to match corresponding dates?

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sorting Columns to match dates. Dates need to be on same rows
    By tdugas2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-02-2017, 05:42 PM
  2. Replies: 0
    Last Post: 02-14-2017, 11:55 PM
  3. Replies: 8
    Last Post: 01-24-2016, 07:17 PM
  4. Replies: 1
    Last Post: 12-17-2014, 08:14 PM
  5. Match dates from 2 different columns
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2014, 04:07 PM
  6. Replies: 1
    Last Post: 06-18-2013, 06:32 PM
  7. Replies: 5
    Last Post: 04-10-2012, 07:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1