Compare and combine Date/Times between 2 sheets of data

S

Sparktracer

New Member
Joined
May 31, 2007
Messages
38
Hello experts,
This is what I want the finished work to look like (S3):

|-- A -- | -- B -- | -- C -- | --D --|-- E --|-- F --|--G --|-- -- H -- -- | -->
8/20/2010 21:18:03 -40 85.6 85 34
8/20/2010 21:19:03 -40 85.4 85 35
8/20/2010 21:20:03 -40 85 85 36 8/20/2010 21:20:43 Calculated Data 0.079 0.048 0.080 0.019 0.078 0.081
8/20/2010 21:21:03 -48.3 84.5 85 37
8/20/2010 21:22:03 -54.7 82.8 -40 xfer 0
8/20/2010 21:23:03 -39 85.5 -40 1
8/20/2010 21:24:03 -38.4 85.9 -40 2
8/20/2010 21:25:03 -39.2 85.5 -40 3
8/20/2010 21:26:03 -40.1 86.2 -40 4
8/20/2010 21:27:03 -40 85.3 -40 5
8/20/2010 21:28:03 -40.2 85.2 -40 6
8/20/2010 21:29:03 -40.2 84.8 -40 7
8/20/2010 21:30:03 -40.2 84.7 -40 8 8/20/2010 21:30:46 Calculated Data 0.051 0.064 0.056 (0.052) 0.023 0.082
8/20/2010 21:31:03 -40.1 84.9 -40 9
8/20/2010 21:32:03 -40.1 85.1 -40 10
8/20/2010 21:33:03 -40.1 85.2 -40 11
8/20/2010 21:34:03 -40.1 85.3 -40 12
8/20/2010 21:35:03 -40 85.5 -40 13
8/20/2010 21:36:03 -40.1 85.1 -40 14
8/20/2010 21:37:03 -40.1 84.9 -40 15
8/20/2010 21:38:03 -40.1 84.9 -40 16
8/20/2010 21:39:03 -40 85.3 -40 17
8/20/2010 21:40:03 -40.1 85.2 -40 18 8/20/2010 21:40:48 Calculated Data 0.055 0.038 0.051 0.028 0.044 0.047
8/20/2010 21:41:03 -40 84.9 -40 19
8/20/2010 21:42:03 -40.4 89 85 xfer 0

Here is the first set of data, (S1):

8/20/2010 21:18:03 -40 85.6 85
8/20/2010 21:19:03 -40 85.4 85
8/20/2010 21:20:03 -40 85 85
8/20/2010 21:21:03 -48.3 84.5 85
8/20/2010 21:22:03 -54.7 82.8 -40
8/20/2010 21:23:03 -39 85.5 -40
8/20/2010 21:24:03 -38.4 85.9 -40
8/20/2010 21:25:03 -39.2 85.5 -40
8/20/2010 21:26:03 -40.1 86.2 -40
8/20/2010 21:27:03 -40 85.3 -40
8/20/2010 21:28:03 -40.2 85.2 -40
8/20/2010 21:29:03 -40.2 84.8 -40
8/20/2010 21:30:03 -40.2 84.7 -40
8/20/2010 21:31:03 -40.1 84.9 -40
8/20/2010 21:32:03 -40.1 85.1 -40
8/20/2010 21:33:03 -40.1 85.2 -40
8/20/2010 21:34:03 -40.1 85.3 -40
8/20/2010 21:35:03 -40 85.5 -40
8/20/2010 21:36:03 -40.1 85.1 -40
8/20/2010 21:37:03 -40.1 84.9 -40
8/20/2010 21:38:03 -40.1 84.9 -40
8/20/2010 21:39:03 -40 85.3 -40
8/20/2010 21:40:03 -40.1 85.2 -40
8/20/2010 21:41:03 -40 84.9 -40
8/20/2010 21:42:03 -40.4 89 85

Here is the second set of data, (S2):

8/20/2010 21:20:43 Calculated Data 0.079 0.048 0.080 0.019 0.078 0.081
8/20/2010 21:30:46 Calculated Data 0.051 0.064 0.056 (0.052) 0.023 0.082
8/20/2010 21:40:48 Calculated Data 0.055 0.038 0.051 0.028 0.044 0.047
8/20/2010 21:50:48 Calculated Data 0.083 0.076 0.076 (0.012) 0.032 0.045
8/20/2010 22:00:49 Calculated Data 0.075 0.068 0.066 0.004 0.077 0.068
8/20/2010 22:10:50 Calculated Data (0.028) 0.014 0.068 (0.025) 0.042 0.049
8/20/2010 22:20:51 Calculated Data 0.056 0.041 0.053 0.020 0.040 0.049
8/20/2010 22:30:53 Calculated Data 0.110 0.065 0.044 0.068 0.129 0.086
8/20/2010 22:40:53 Calculated Data 0.047 0.005 0.088 0.026 0.089 0.063
8/20/2010 22:50:54 Calculated Data 0.122 0.013 0.043 0.090 (0.010) 0.038
8/20/2010 23:00:55 Calculated Data 0.034 0.043 0.045 0.021 0.033 0.049
8/20/2010 23:10:56 Calculated Data 0.062 (0.014) (0.001) (0.002) 0.073 0.035
8/20/2010 23:20:58 Calculated Data 0.048 0.135 0.090 0.011 0.027 0.040
8/20/2010 23:30:58 Calculated Data (0.064) 0.033 0.072 (0.045) (0.014) 0.010
8/20/2010 23:41:00 Calculated Data 0.055 0.044 0.055 0.031 0.051 0.050
8/20/2010 23:51:01 Calculated Data 0.037 0.115 0.447 (0.001) 0.004 0.075

Currently I am comparing the times in S1 column B with the times in S2 column A and then manually moving the data so the times matchup.

I have 3 months of data that I need to combine/compare.
A typical day of data:
S1 has 5 columns and up to 1445 rows.
S2 has 101 columns and up to 145 rows.

I have tried different formulas to move/copy the data from S2 into S1 but have had no luck.
Any assistance with this excel data compare and combine is greatly appreciated.

I am working with Windows XP and Office 2003.
We are allowed to use Macros and VB code. I have done very little with either.

Sparktracer

ps. I do not have HTMLMaker to create the formatted data. Sorry
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
(S2)
Excel Workbook
ABCDEFGH
38/20/2010 21:20:43Calculated Data0.0790.0480.0800.0190.0780.081
48/20/2010 21:30:46Calculated Data0.0510.0640.056(0.052)0.0230.082
58/20/2010 21:40:48Calculated Data0.0550.0380.0510.0280.0440.047
68/20/2010 21:50:48Calculated Data0.0830.0760.076(0.012)0.0320.045
78/20/2010 22:00:49Calculated Data0.0750.0680.0660.0040.0770.068
88/20/2010 22:10:50Calculated Data(0.028)0.0140.068(0.025)0.0420.049
98/20/2010 22:20:51Calculated Data0.0560.0410.0530.0200.0400.049
108/20/2010 22:30:53Calculated Data0.1100.0650.0440.0680.1290.086
118/20/2010 22:40:53Calculated Data0.0470.0050.0880.0260.0890.063
128/20/2010 22:50:54Calculated Data0.1220.0130.0430.090(0.010)0.038
138/20/2010 23:00:55Calculated Data0.0340.0430.0450.0210.0330.049
148/20/2010 23:10:56Calculated Data0.062(0.014)(0.001)(0.002)0.0730.035
158/20/2010 23:20:58Calculated Data0.0480.1350.0900.0110.0270.040
168/20/2010 23:30:58Calculated Data(0.064)0.0330.072(0.045)(0.014)0.010
178/20/2010 23:41:00Calculated Data0.0550.0440.0550.0310.0510.050
188/20/2010 23:51:01Calculated Data0.0370.1150.447(0.001)0.0040.075
read data
 
Upvote 0
(S1)
Excel Workbook
ABCDE
378/20/201021:18:03-4085.685
388/20/201021:19:03-4085.485
398/20/201021:20:03-408585
408/20/201021:21:03-48.384.585
418/20/201021:22:03-54.782.8-40
428/20/201021:23:03-3985.5-40
438/20/201021:24:03-38.485.9-40
448/20/201021:25:03-39.285.5-40
458/20/201021:26:03-40.186.2-40
468/20/201021:27:03-4085.3-40
478/20/201021:28:03-40.285.2-40
488/20/201021:29:03-40.284.8-40
498/20/201021:30:03-40.284.7-40
508/20/201021:31:03-40.184.9-40
518/20/201021:32:03-40.185.1-40
528/20/201021:33:03-40.185.2-40
538/20/201021:34:03-40.185.3-40
548/20/201021:35:03-4085.5-40
558/20/201021:36:03-40.185.1-40
568/20/201021:37:03-40.184.9-40
578/20/201021:38:03-40.184.9-40
588/20/201021:39:03-4085.3-40
598/20/201021:40:03-40.185.2-40
608/20/201021:41:03-4084.9-40
618/20/201021:42:03-40.48985
Equipment log
 
Upvote 0
(S3)
Excel Workbook
ABCDEFGHIJKLMNO
378/20/201021:18:03-4085.685 34
388/20/201021:19:03-4085.485 35
398/20/201021:20:03-408585 368/20/2010 21:20:43Calculated Data0.0790.0480.0800.0190.0780.081
408/20/201021:21:03-48.384.585 37
418/20/201021:22:03-54.782.8-40xfer0
428/20/201021:23:03-3985.5-40 1
438/20/201021:24:03-38.485.9-40 2
448/20/201021:25:03-39.285.5-40 3
458/20/201021:26:03-40.186.2-40 4
468/20/201021:27:03-4085.3-40 5
478/20/201021:28:03-40.285.2-40 6
488/20/201021:29:03-40.284.8-40 7
498/20/201021:30:03-40.284.7-40 88/20/2010 21:30:46Calculated Data0.0510.0640.056(0.052)0.0230.082
508/20/201021:31:03-40.184.9-40 9
518/20/201021:32:03-40.185.1-40 10
528/20/201021:33:03-40.185.2-40 11
538/20/201021:34:03-40.185.3-40 12
548/20/201021:35:03-4085.5-40 13
558/20/201021:36:03-40.185.1-40 14
568/20/201021:37:03-40.184.9-40 15
578/20/201021:38:03-40.184.9-40 16
588/20/201021:39:03-4085.3-40 17
598/20/201021:40:03-40.185.2-40 188/20/2010 21:40:48Calculated Data0.0550.0380.0510.0280.0440.047
608/20/201021:41:03-4084.9-40 19
618/20/201021:42:03-40.48985xfer0
Combined
 
Upvote 0
Hi there,

This shouldn't be a problem, but I have a few questions. I don't think a formula solution would be best suited here, I would personally use VBA. Not saying you couldn't though. I'm assuming this will be reoccuring data? If not, a formula solution may be more applicable. The questions below are under the assumption the data will be transient.

Where the dates and times match, you want to copy data from S2 and put it on the same line of data copied from S1? And this will go into a new worksheet in the same workbook? Explain how this works for me. Is the data in a single workbook? Downloaded/copied into a new workbook? Where do you want to run this from (a VBA-based solution)? What version of Excel will this be running on (at work or home)?
 
Upvote 0
Zack Barresse said:
Hi there,

This shouldn't be a problem, but I have a few questions. I don't think a formula solution would be best suited here, I would personally use VBA. Not saying you couldn't though. I'm assuming this will be reoccuring data? If not, a formula solution may be more applicable. The questions below are under the assumption the data will be transient.

Where the dates and times match, you want to copy data from S2 and put it on the same line of data copied from S1? And this will go into a new worksheet in the same workbook? Explain how this works for me. Is the data in a single workbook? Downloaded/copied into a new workbook? Where do you want to run this from (a VBA-based solution)? What version of Excel will this be running on (at work or home)?
Click to expand...

Zack,
I agree that a VBA solution is prefered. To answer your questions:
I have a program that creates a new file every day (S2). This addes data to a txt file every 10 minutes. The scan time does incriment in seconds almost every scan so the times do change some as time progresses. I also have a machine that I export data from daily. This data is taken every second. I create a new xls every day and import both the S1 csv file and the S1 txt file into two seperate worksheets. I then want to run the VBA to generate a third worksheet S3 with the date/time stamps lined up... I do this at work daily with Windows XP Pro and MS Office 2003. I do a lot more manual operations with the data, but this is what takes me the longest.
I end up copying the data that is the highest value from S3-column G in a new worksheet and then do more math with the data fields.
 
Upvote 0
Can't we just do it all from VBA and cut out all the menial tasks? You could create a userform for this and have a couple of buttons which lets you pick the files, then run your processes on them.

And which version should this be for? If, as I see in your signature, you run this at work, we'll have to code it for 2003. If you use this at home, but will ever use it at work, we have to code it for 2003. Make sense?

Also, are there ever any headers? You didn't post any, but your data did not start from row 1 (in your post). Things like that would be very good information to know. Sample files would be best.
 
Upvote 0
Zack Barresse said:
Can't we just do it all from VBA and cut out all the menial tasks? You could create a userform for this and have a couple of buttons which lets you pick the files, then run your processes on them.

And which version should this be for? If, as I see in your signature, you run this at work, we'll have to code it for 2003. If you use this at home, but will ever use it at work, we have to code it for 2003. Make sense?

Also, are there ever any headers? You didn't post any, but your data did not start from row 1 (in your post). Things like that would be very good information to know. Sample files would be best.
Click to expand...


Zack,
If we can do it all from VBA, that would be wonderful.
This is for work and 2003.
I do have some headers in S1 and S3, none in S2.
 
Upvote 0
Excel Workbook
ABCDE
1Comment:****
2*****
3DateTimeCold BoxHot BoxLoad 1 SP
49/7/201023:55:19-40.284.6-40
59/7/201023:56:19-40.284.7-40
69/7/201023:57:19-40.184.4-40
79/7/201023:58:19-40.184.7-40
89/7/201023:59:19-40.184.7-40
99/8/20100:00:01-40.185.3-40
109/8/20100:01:01-40.185.1-40
119/8/20100:02:01-4085.4-40
129/8/20100:03:01-40.185.4-40
139/8/20100:04:01-40.185.4-40
149/8/20100:05:01-40.185.3-40
159/8/20100:06:01-4084.6-40
169/8/20100:07:01-40.184.4-40
179/8/20100:08:01-4084.9-40
189/8/20100:09:01-40.68985
199/8/20100:10:01-40.187.785
209/8/20100:11:01-39.987.585
219/8/20100:12:01-39.986.285
229/8/20100:13:01-408685
239/8/20100:14:01-4085.885
249/8/20100:15:01-4085.685
259/8/20100:16:01-4084.385
269/8/20100:17:01-40.184.385
279/8/20100:18:01-408585
289/8/20100:19:01-4085.485
299/8/20100:20:01-4085.985
309/8/20100:21:01-4085.485
319/8/20100:22:01-4085.385
329/8/20100:23:01-4085.285
339/8/20100:24:01-4084.985
349/8/20100:25:01-4084.585
359/8/20100:26:01-4084.585
369/8/20100:27:01-4084.685
379/8/20100:28:01-4084.785
389/8/20100:29:01-43.485.8-40
399/8/20100:30:01-37.185.9-40
409/8/20100:31:01-38.984.3-40
419/8/20100:32:01-39.784.8-40
429/8/20100:33:01-40.184.8-40
439/8/20100:34:01-40.284.4-40
449/8/20100:35:01-40.284.7-40
459/8/20100:36:01-40.284.7-40
469/8/20100:37:01-40.284.8-40
479/8/20100:38:01-40.184.9-40
489/8/20100:39:01-40.185.1-40
499/8/20100:40:01-40.184.7-40
509/8/20100:41:01-40.185.4-40
519/8/20100:42:01-40.185.1-40
529/8/20100:43:01-40.185.8-40
539/8/20100:44:01-40.184.6-40
549/8/20100:45:01-40.184.8-40
559/8/20100:46:01-4085.1-40
569/8/20100:47:01-40.185.5-40
579/8/20100:48:01-4085.4-40
589/8/20100:49:01-39.583.785
599/8/20100:50:01-40.586.785
609/8/20100:51:01-4082.485
619/8/20100:52:01-39.982.785
629/8/20100:53:01-39.983.885
639/8/20100:54:01-4085.585
649/8/20100:55:01-4086.485
659/8/20100:56:01-408585
669/8/20100:57:01-4086.485
679/8/20100:58:01-4085.985
689/8/20100:59:01-4085.885
699/8/20101:00:01-4085.385
709/8/20101:01:01-4084.785
719/8/20101:02:01-4084.485
729/8/20101:03:01-4084.385
739/8/20101:04:01-408585
749/8/20101:05:01-4085.585
759/8/20101:06:01-4085.585
769/8/20101:07:01-4084.985
779/8/20101:08:01-4084.785
789/8/20101:09:01-46.484.585
799/8/20101:10:01-40.384.3-40
809/8/20101:11:01-38.384.3-40
819/8/20101:12:01-39.385.8-40
829/8/20101:13:01-4084.9-40
839/8/20101:14:01-40.284.5-40
849/8/20101:15:01-40.285.6-40
859/8/20101:16:01-40.285.1-40
869/8/20101:17:01-40.284.4-40
879/8/20101:18:01-40.185-40
889/8/20101:19:01-40.185.4-40
899/8/20101:20:01-40.185.4-40
909/8/20101:21:01-40.185.7-40
919/8/20101:22:01-40.185.5-40
929/8/20101:23:01-40.185.1-40
939/8/20101:24:01-4084.8-40
949/8/20101:25:01-40.185.1-40
959/8/20101:26:01-4084.5-40
969/8/20101:27:01-40.184.9-40
979/8/20101:28:01-4085.2-40
989/8/20101:29:01-4085.6-40
999/8/20101:30:01-40.688.385
1009/8/20101:31:01-40.18885
TR14061 chamber 9-8-2010

Only 100 rows coppied for forum size limits. This is the original import machine data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
350
awoohaw
awoohaw
A
  • Question
Find entries in a sheet available or not
2
Replies
10
Views
93
AFZAL SOHAIL
A
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
426
KRice
K
J
  • Question
Calculating time frames
Replies
2
Views
161
myall_blues
myall_blues
togenmoser
  • Solved
Finding all possible combinations of 2 sums
2
Replies
10
Views
306
togenmoser
togenmoser

Forum statistics

Threads
1,216,150
Messages
6,129,154
Members
449,488
Latest member
qh017

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top