Howdy folks,
First time poster, long time viewer. Couldn't find my answer searching today!
I have multiple tables of data inside of an Excel workbook. Each table is in its own worksheet, and each is basically the same format. I've downloaded data from a building automation server trending multiple points of data. Each table is the measurement of a different variable taken over the same time period. (In this case, between the months of April and September) Essentially they follow this format:
mmm dd, yyyy hh:mm:ss xM | Variable, mostly floating-point, occasionally on/off
So, for example I might have data table that looks like this:
May 23, 2014 12:12:00 PM | 62°
- or -
May 23, 2014 12:12:00 PM | Off
What I'm needing to do is create a PivotChart where I can overlay different variables on top of one another. I'll need to do multiple filters, etc. I would love to copy the variables from each worksheet in to the same table to do so, but I have a problem... they have different time stamps.
Most of the data was taken in three-minute intervals, however, some variables are missing entire days and/or months for various reasons; component failure, re-programming in the system, user configuration change, etc. Essentially I have gaps in one variable but not in another, so that while the data I have was taken over the same time period, some variables have all time stamps and some are missing them altogether.
If there is a gap, my data looks like this:
May 23, 2014 12:12:00 PM | 62°
May 23, 2014 12:21:00 PM | 72°
Instead of like this:
May 23, 2014 12:12:00 PM | 62°
May 23, 2014 12:15:00 PM |
May 23, 2014 12:18:00 PM |
May 23, 2014 12:21:00 PM | 72°
In order for me to do a PivotChart, I'm 99% sure I need to get to the latter.
I can chart this out on a standard Excel chart and immediately see where the gaps exist. It seems Excel is smart enough to realize that my X-axis is a date and if there are dates missing it interpolates a value between the two known time stamps. Pivot Charts won't do that... they simply draw the next point, making it appear as if all the data were there.
What makes this even worse is that a few variables were recorded every minute instead of every three, which further complicates the situation. I need everything in a three-minute interval.
I'm pretty sure I'm going to have to generate a second table from the first doing some form of macro or command, but I really don't know where to start.
Fair warning; I'm not proficient in VBA. I won't run away from macros, but I don't know the first thing about how to make one other than by recording them.
Any help appreciated. Thanks!
First time poster, long time viewer. Couldn't find my answer searching today!
I have multiple tables of data inside of an Excel workbook. Each table is in its own worksheet, and each is basically the same format. I've downloaded data from a building automation server trending multiple points of data. Each table is the measurement of a different variable taken over the same time period. (In this case, between the months of April and September) Essentially they follow this format:
mmm dd, yyyy hh:mm:ss xM | Variable, mostly floating-point, occasionally on/off
So, for example I might have data table that looks like this:
May 23, 2014 12:12:00 PM | 62°
- or -
May 23, 2014 12:12:00 PM | Off
What I'm needing to do is create a PivotChart where I can overlay different variables on top of one another. I'll need to do multiple filters, etc. I would love to copy the variables from each worksheet in to the same table to do so, but I have a problem... they have different time stamps.
Most of the data was taken in three-minute intervals, however, some variables are missing entire days and/or months for various reasons; component failure, re-programming in the system, user configuration change, etc. Essentially I have gaps in one variable but not in another, so that while the data I have was taken over the same time period, some variables have all time stamps and some are missing them altogether.
If there is a gap, my data looks like this:
May 23, 2014 12:12:00 PM | 62°
May 23, 2014 12:21:00 PM | 72°
Instead of like this:
May 23, 2014 12:12:00 PM | 62°
May 23, 2014 12:15:00 PM |
May 23, 2014 12:18:00 PM |
May 23, 2014 12:21:00 PM | 72°
In order for me to do a PivotChart, I'm 99% sure I need to get to the latter.
I can chart this out on a standard Excel chart and immediately see where the gaps exist. It seems Excel is smart enough to realize that my X-axis is a date and if there are dates missing it interpolates a value between the two known time stamps. Pivot Charts won't do that... they simply draw the next point, making it appear as if all the data were there.
What makes this even worse is that a few variables were recorded every minute instead of every three, which further complicates the situation. I need everything in a three-minute interval.
I'm pretty sure I'm going to have to generate a second table from the first doing some form of macro or command, but I really don't know where to start.
Fair warning; I'm not proficient in VBA. I won't run away from macros, but I don't know the first thing about how to make one other than by recording them.
Any help appreciated. Thanks!
