Data reduction macro

[jkey07]

Hi,

Just in the process of learning to sort myself out with respect to macros etc but in the meantime I have more of an immediate requirement I was hoping you guys might be able to help with.

I have a text file with a number of columns of data, the sheer amount of data is both more than excel can reliably handle and the graphing function can facilitate. I'm looking to reduce the number of data points.

To be more explicit I have columns A, B & C which contain time stamp, speed and amplitude respectively. The rows then contain all of this data. The data logger we have collects one sample every 1/10th of a second and the change in speed occurs at a ramp rate of 1 rpm per second. So I have 10 samples I need to reduce down to 1 to get one sample per speed change.

I need a macro that would allow me to carry out this reduction, but one problem still remains. These vibration amplitudes are taken at various location, such as location 1,2 & 3, which is denoted in colum D only at the point at which each location sample range starts again, at this point the time and speed return back to the start as per the initial location point and the data points continue in the rows.

Example:

A. B. C. D
10:23:01.1. 17344. 58 um. 10XN1
10:23:01.2. 17344. 55 um.
10:23:01.3. 17344. 57 um.
10:23:02.1. 17345. 58 um.
10:23:02.2. 17345. 55 um.
10:23:02.3. 17345. 57 um.
10:23:01.1. 17344. 59 um. 11XN1
10:23:01.2. 17344. 61 um.
10:23:01.3. 17344. 58 um.
10:23:02.1. 17345. 58 um.
10:23:02.2. 17345. 55 um.
10:23:02.3. 17345. 57 um.

I'm not to interested in the average but just the first sample point in each set and then to be able to distinguish between the various measurement locations.

Any help would be much appreciated.

Thanks

[djbomaha]

Hi jkey07,

Are you trying to take what you have above and reduce it to what is shown below?

A. B. C. D
10:23:01.1. 17344. 58 um. 10XN1
10:23:02.1. 17345. 58 um.
10:23:01.1. 17344. 59 um. 11XN1
10:23:02.1. 17345. 58 um.

From you description, it sounded like you were trying to reduce the data down to the first first 1/10th of a second upon speed change for each location.

Thanks,

Dan

[jkey07]

Hi djbomaha,

The above is an example of the raw data I have to deal with. The time stamp is in Column A, the speed in Column B, vibration amplitude in Column C and the location point in Column D (the location at which these readings are taken.

I have a text file with all of this information in it where every discrete point is separated onto a different row. However, this files contains all of the data from the various locations. If you see the table I made to try to convey this a little clearer (which is not so clear in retrospect) I have time stamped 3 data points all at the same speed as each data point is taken every 1/10th of a second, but the speed changes by only 1 rpm every second, so I get 10 data points for every speed change effectively….I’m looking to reduce that to 1 data sample per speed point. I should have clearly noted that I’m reading this from a .txt file into excel.

This is a file that contains all data for all locations so every 10,000 data points (I choose this as an example, I’ve not counted them) the measuring location changes and reads back all of the equivalent time stamp data for that location point. I need something that will read the .txt, take the first data point (using the speed column as its reference) and dump the remaining 9 data points for that speed, then move onto the next set of 10 points from that speed sample (it’s not always 10 as well because the engine ramp rate varies very slightly resulting in some speed points having 10 samples and some having 8 or 11 etc, so it needs to be able to read the speed column for a value if that makes sense). Then it should be able to distinguish which location point the data has come from.

So it should go something like:

Read column D, and take the first line – this makes sure that we capture the first data point in that location.
If Column D remains unchanged then carry on with next steps.

Read column B and take first line – this makes sure we capture the first data point relative to that speed reading.
Read next line, if column B is unchanged then ignore, if column B has changed then take that data.
So on and so forth.

That should give me only one reading for each discrete speed point and ensure that I capture the data from all locations.

[djbomaha]

Hi jkey07,

Here is code to do what you are describing:

Please Login or Register  to view this content.

Hope that helps,

Dan