Hi,
I've created a pivotchart/table containing values that have been recorded by 3 different PLCs. The CSV files contain the date and time for each value. My problem is that they all collect data at different time intervals. (One is every 5 mins, another is every 10 mins, and the last is insanely sporadic and has ~ 10% the number of values as the other 2). It was easy enough to line up the first two using the following VBA code which simply adds a blank row after every data pair:
The tricky part is getting the 3rd set of values to line up correctly with the first 2. The data seems to be collected at random intervals, (sometimes 15mins apart others 4-5 hours) so there's no set pattern for me to divide the data up. I can manually go in and add rows until the data points line up, but as we all know, there's always a macro/VBA solution. The date/time is stored in the excel decimal format so I'm thinking there's some sort of conditional copy paste I can do based on a subtraction of the date/time? It would be parsing through the compiled data file column (We''ll call it column A) for the date/time, and would only paste the data value from the PLC_3 data file if the date value for each was within a certain range (x amount of sec or min) Any help would be appreciated and I'm open to completely going back to the drawing board too.
I hope this table helps describe my issue, thanks for any help!
I've created a pivotchart/table containing values that have been recorded by 3 different PLCs. The CSV files contain the date and time for each value. My problem is that they all collect data at different time intervals. (One is every 5 mins, another is every 10 mins, and the last is insanely sporadic and has ~ 10% the number of values as the other 2). It was easy enough to line up the first two using the following VBA code which simply adds a blank row after every data pair:
Code:
Sub Insert_Blank_Rows()
'Select last row in worksheet.
Selection.End(xlDown).Select
Do Until ActiveCell.Row = 1
'Insert blank row.
ActiveCell.EntireRow.Insert shift:=xlDown
'Move up one row.
ActiveCell.Offset(-1, 0).Select
Loop
End SubThe tricky part is getting the 3rd set of values to line up correctly with the first 2. The data seems to be collected at random intervals, (sometimes 15mins apart others 4-5 hours) so there's no set pattern for me to divide the data up. I can manually go in and add rows until the data points line up, but as we all know, there's always a macro/VBA solution. The date/time is stored in the excel decimal format so I'm thinking there's some sort of conditional copy paste I can do based on a subtraction of the date/time? It would be parsing through the compiled data file column (We''ll call it column A) for the date/time, and would only paste the data value from the PLC_3 data file if the date value for each was within a certain range (x amount of sec or min) Any help would be appreciated and I'm open to completely going back to the drawing board too.
| PLC1 Time | PLC1 value | PLC2 Time | PLC2 value | PLC3 Time | PLC3 value |
| 10:00 | 21 | 10:05 | 19 | 10:17 | 22 |
| 10:10 | 22 | 10:10 | 19 | 11:38 | 20 |
| 10:20 | 18 | 10:15 | 22 | 2:19 | 20 |
| 10:30 | 19 | 10:20 | 17 | 14:22 | 20 |
| 10:40 | 22 | 10:25 | 21 | 14:25 | 21 |
| 10:50 | 23 | 10:30 | 20 | 21:07 | 19 |
| 11:00 | 22 | 10:35 | 22 | 23:16 | 22 |
I hope this table helps describe my issue, thanks for any help!
