+ Reply to Thread
Results 1 to 2 of 2

Matching two spreadsheets by time of day

  1. #1
    Forum Contributor
    Join Date
    09-02-2005
    Posts
    146

    Matching two spreadsheets by time of day

    Ok, I have two sets of data that I need to match up to one another.

    The first set of data (STI) has to be transferred back into Access when all is said and done, and cannot be modified outside of these two columns:
    Column 2: Time of day in number of seconds past the year 1900
    Column 13: Recieved Signal 2

    The data (Box) that I'm importing can be cut an paste anywhere on the sheet and will come in in this format:
    Col 1: Time of day in number of days past the year 1900
    Col 2: Time of day in HH:MM:SS 'not used
    Col 3: Voltage recieved
    Col 4: Signal Strength

    So what I need to do is match up Col 4 from the box to column 13 of STI by using the time from each set of data. I think what I need is a loop in a loop, but so far Excel isn't liking it like that.

    I will eventually have to add some rounding into the time because they are off by decimal points, but for now we can assume it is a perfect match.

    This is what I was thinking so far. Could I get an expert on loops let me know if I'm way off base. Also, is there a way to show (such as by highlighting) the data that was unable to be matched?

    Sub matchup()
    datalength = Cells(Rows.Count, 1).End(xlUp).Row 'STI data from access
    impdatalength = Cells(Rows.Count, 16).End(xlUp).Row 'TX data from box
    For i = 2 To datalength
    Cells(i, 14).Value = Cells(i, 2).Value / 86400 'convert time from seconds to days
    If Cells(i, 13).Value = "-130" Then Cells(i, 13).Value = "" 'remove default Rec2 values
    Next
    For o = 1 To impdatalength
    For p = 2 To datalength
    If Cells(o, 16).Value = Cells(p, 14) Then
    Cells(o, 19).Cut Cells(p, 13)
    Next
    Next
    End Sub
    Thanks guys,
    Wilro85

  2. #2
    Forum Contributor
    Join Date
    09-02-2005
    Posts
    146
    I figured out how to do this using vlookup. This thread can be closed/deleted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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