manipulating large data sets for graphing

[steveb]

I use a data logging program that records data points in an access
database that I export to excel for graphing. A data log may have many
thousands of readings. I need a quick way to put the data into a form
that I can graph with Excel.

The data is in three columns. The first column contains a repeating
list of the sensor names, which always appear in the same order
(e.g.: [sensor 1, sensor 2, sensor 3...] {sensor 1, sensor 2,
sensor 3...] ). The second column has the time of the sensor reading,
with one time per group of sensor readings. And the third column has
the temperature associated with the read time and the sensor name.

I want to graph the data with time on the x axis, temperature on the
y axis, and a line (or series of data points) for each sensor. Based
on my understanding of how data must appear in Excel in order to graph
it, I have struggled using if statements and data sorting to put the
data into a form where there is a column for each sensor with
temperature readings in the column cells, and a time column with a
line for each time reading.

Is there an easier way?

 

[Max]

Assume source data in Sheet1's cols A to C (Sensor - Time - Temp),
data running in row2 down (data presumed to be fully populated for each col)

In Sheet2,
List the sensors in B1 across, viz: sensor 1, sensor 2, sensor 3, ..
List the times in chrono order in A2 down, eg: 12:00 pm, 1:00 pm, 2:00 pm
etc
(The above listings must match with the data in Sheet1's cols A and B)

Put in B2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(Sheet1!$C$2:$C$100,MATCH(1,(Sheet1!$A$2:$A$100=B$1)*(Sheet1!$B$2:$B$100=$A2),0))
Copy across and fill down to populate the table. Adapt the ranges to suit.
Now you can easily plot a line graph on the table, using as data range, eg:
=Sheet2!$A$1:$F$50 with the series in "columns".

 

[Jon Peltier]

Make a pivot table from the imported data (or link the pivot table to the
Access database). Time can go into the Rows area of the pivot table, Sensor
into the Columns area, and the readings into the Data area. Copy the pivot
table, paste it special as values, and create your chart.

- Jon