I am attempting to create some graphs with dynamic cell references as indices. These graphs are based upon time stamps generated from a testing machine. I have a column of Boolean values (0 or 1) that mark "force logs", and a spreadsheet of various fields of data for machine. This machine also produces a spreadsheet on a separate workbook (this spreadsheet contains actual time stamps that mark start and end times) that I am manually copying and pasting into another sheet of the workbook with the data (it has approximately 5000 rows). I want to locate my beginning and end indices for my graph based on time stamps. Unfortunately, the separately produced Excel workbook with the correct time stamps to choose from lags around 1 second from the time stamp on the main data spreadsheet (this lagging is variable but between 0 and 2 seconds). Furthermore, my "force log" Boolean values are supposed to be 1 for time stamps, but often times I have extra force logs that do not correspond to the correct times for starting the graph. Below is a representative sample as per requested.
Worksheet containing data (Call this Sheet 2)
A B C D
Time Stamp Force Log Field 1 Field 2
1 2/6/2015 3:21:22 PM 1 100 500 <- Extraneous boolean value
2 2/6/2015 3:21:23 PM 0 101.4 499.2
3 2/6/2015 3:21:24 PM 1 99.3 501.4 <- I want this index for graph start
4 2/6/2015 3:21:25 PM 0 100.3 498.9
5 2/6/2015 3:21:26 PM 0 102.4 500.7
6 2/6/2015 3:21:27 PM 1 101.8 499.3 <- I want this index for graph end
Worksheet containing machine generated data summary (call this Sheet 1)
Start Time End Time
3:21:23 PM 3:21:26 <- The times here lead the other worksheet by less than 2 seconds
They are also off by 2/6/2015, but I can generate a helper column
After obtaining the indices 3 and 6, I would graph the values of Field 1 and Field 2.
Here is what, from my problem, I have gathered I need to do: Perform fuzzy searching for time stamps by either truncated or conditionally testing for equivalence via a range (plus minus 2 seconds), and make sure that the "force log" Boolean value column is also set to 1.
I am new to Excel reference and search functions, so I am not sure if I am missing something. So far I have tried LOOKUP, VLOOKUP (did not work because my data columns aren't formatted correctly for table_array), INDEX MATCH (did not work because of the fuzzy requirement of one of my searches), and can't seem to find any easy to understand solutions allowing a search with multiple criteria that returns multiple results (I have multiple times that each need a single result, not a single time that needs multiple results; I was struggling to grasp array formulas in Excel).
I greatly thank in advance anyone with a solution or advice.
EDIT: I understand that my question might have a very strange background. Please let me know if you need me to clarify or otherwise elaborate on what exactly I need to do. Thanks!
Force Log
is1
. Is that correct?