software

Hello All,

I have three columns of raw data which are x,y and z coordinates
(quot;observationsquot;) for several different points, say points A to Z. The number
of points is variable as is the number of observations of any given point.

I want to group the data into blocks seperated by blank rows by comparing
each observation with the previous one and then assuming that if the
difference in x,y or z is more than say 0.1, then the next observation is of
a new point.

I then would like to calculate the average value of each blockand display
these.

The ideal result would be to have the original data in columns A to C, the
grouped data in coumns E to G, and the averages in I to K.

This is beyond my meagre VBA abilities. Please help!

Thanks in advance

BTW I'm using Excel 2003You'll have to give an example of what you are trying to achieve. I'm afraid
your explanation isn't very clear to me.

Ian

quot;Marquismarcequot; wrote:

gt; Hello All,
gt;
gt; I have three columns of raw data which are x,y and z coordinates
gt; (quot;observationsquot;) for several different points, say points A to Z. The number
gt; of points is variable as is the number of observations of any given point.
gt;
gt; I want to group the data into blocks seperated by blank rows by comparing
gt; each observation with the previous one and then assuming that if the
gt; difference in x,y or z is more than say 0.1, then the next observation is of
gt; a new point.
gt;
gt; I then would like to calculate the average value of each blockand display
gt; these.
gt;
gt; The ideal result would be to have the original data in columns A to C, the
gt; grouped data in coumns E to G, and the averages in I to K.
gt;
gt; This is beyond my meagre VBA abilities. Please help!
gt;
gt; Thanks in advance
gt;
gt; BTW I'm using Excel 2003
gt;

Okay, I'll see what I can do.

Let's say I've got 70 GPS readings from 5 different locations. Each reading
is in the form of x,y,z coordinates. Let's say I've taken 15 readings at
point A, 10 at B, 12 at C, 18 at D, and 15 at E. I want to average these
readings to get more accurate value for the coordinates of the five points.

In Excel, I have 70 rows, 3 columns. Now, I happen to know that the first 15
rows were taken at point A, the next 10 at B and so on. For excel to be able
to identify which points are where, it must compare each row with the
previous one until such time as one or more of the 3 coords differs from the
previous row by more than 0.1.

I envisage something along the lines of:

start at row 1. Copy a1:c1 to e1:g1

row 2
compare with row 1. conclusion: same point. Action: copy to e2:g2

and so on until

row 19
compare with row 18. conclusion: new point. Action: insert blank row. copy
a19:c19 to e20:g20

The idea being to seperate the readings into blocks so that they can then
be averaged and the averages displayed.

I said in the first post that

gt; The ideal result would be to have the original data in columns A to C, the
gt; grouped data in coumns E to G, and the averages in I to K.but it's not crucial to keep the original data on the worksheet as this will
still exist elsewhere.

Anyway, hope this clarifies.

Marcel

quot;Ian Pquot; wrote:

gt; You'll have to give an example of what you are trying to achieve. I'm afraid
gt; your explanation isn't very clear to me.
gt;
gt; Ian
gt;
gt; quot;Marquismarcequot; wrote:
gt;
gt; gt; Hello All,
gt; gt;
gt; gt; I have three columns of raw data which are x,y and z coordinates
gt; gt; (quot;observationsquot;) for several different points, say points A to Z. The number
gt; gt; of points is variable as is the number of observations of any given point.
gt; gt;
gt; gt; I want to group the data into blocks seperated by blank rows by comparing
gt; gt; each observation with the previous one and then assuming that if the
gt; gt; difference in x,y or z is more than say 0.1, then the next observation is of
gt; gt; a new point.
gt; gt;
gt; gt; I then would like to calculate the average value of each blockand display
gt; gt; these.
gt; gt;
gt; gt; The ideal result would be to have the original data in columns A to C, the
gt; gt; grouped data in coumns E to G, and the averages in I to K.
gt; gt;
gt; gt; This is beyond my meagre VBA abilities. Please help!
gt; gt;
gt; gt; Thanks in advance
gt; gt;
gt; gt; BTW I'm using Excel 2003
gt; gt;

Try this:
'----------
Sub group()
Dim iRow As Long
Dim iCol As Long
Dim iLastRow As Long
Dim iCurrRow As Long
iLastRow = Range(quot;A1quot;).End(xlDown).Row
iCurrRow = iLastRow
For iRow = iLastRow To 2 Step -1
For iCol = 1 To 3
If Abs(Cells(iRow, iCol).Value - _
Cells(iRow - 1, iCol).Value) gt;= 0.1 Then

Cells(iRow, quot;Equot;).Resize(, 3).FormulaR1C1 = _
quot;=AVERAGE(RC[-4]:R[quot; amp; iCurrRow - iRow amp; quot;]C[-4])quot;
iCurrRow = iRow
Rows(iRow).Insert
Exit For
End If
Next iCol
Next iRow
iRow = 1
Cells(iRow, quot;Equot;).Resize(, 3).FormulaR1C1 = _
quot;=AVERAGE(RC[-4]:R[quot; amp; iCurrRow - iRow amp; quot;]C[-4])quot;

End Sub
'------------
quot;MMquot; gt; a écrit dans le message de news:
...
gt; Okay, I'll see what I can do.
gt;
gt; Let's say I've got 70 GPS readings from 5 different locations. Each
gt; reading
gt; is in the form of x,y,z coordinates. Let's say I've taken 15 readings at
gt; point A, 10 at B, 12 at C, 18 at D, and 15 at E. I want to average these
gt; readings to get more accurate value for the coordinates of the five
gt; points.
gt;
gt; In Excel, I have 70 rows, 3 columns. Now, I happen to know that the first
gt; 15
gt; rows were taken at point A, the next 10 at B and so on. For excel to be
gt; able
gt; to identify which points are where, it must compare each row with the
gt; previous one until such time as one or more of the 3 coords differs from
gt; the
gt; previous row by more than 0.1.
gt;
gt; I envisage something along the lines of:
gt;
gt; start at row 1. Copy a1:c1 to e1:g1
gt;
gt; row 2
gt; compare with row 1. conclusion: same point. Action: copy to e2:g2
gt;
gt; and so on until
gt;
gt; row 19
gt; compare with row 18. conclusion: new point. Action: insert blank row. copy
gt; a19:c19 to e20:g20
gt;
gt; The idea being to seperate the readings into blocks so that they can then
gt; be averaged and the averages displayed.
gt;
gt; I said in the first post that
gt;
gt;gt; The ideal result would be to have the original data in columns A to C,
gt;gt; the
gt;gt; grouped data in coumns E to G, and the averages in I to K.
gt;
gt;
gt; but it's not crucial to keep the original data on the worksheet as this
gt; will
gt; still exist elsewhere.
gt;
gt; Anyway, hope this clarifies.
gt;
gt; Marcel
gt;
gt; quot;Ian Pquot; wrote:
gt;
gt;gt; You'll have to give an example of what you are trying to achieve. I'm
gt;gt; afraid
gt;gt; your explanation isn't very clear to me.
gt;gt;
gt;gt; Ian
gt;gt;
gt;gt; quot;Marquismarcequot; wrote:
gt;gt;
gt;gt; gt; Hello All,
gt;gt; gt;
gt;gt; gt; I have three columns of raw data which are x,y and z coordinates
gt;gt; gt; (quot;observationsquot;) for several different points, say points A to Z. The
gt;gt; gt; number
gt;gt; gt; of points is variable as is the number of observations of any given
gt;gt; gt; point.
gt;gt; gt;
gt;gt; gt; I want to group the data into blocks seperated by blank rows by
gt;gt; gt; comparing
gt;gt; gt; each observation with the previous one and then assuming that if the
gt;gt; gt; difference in x,y or z is more than say 0.1, then the next observation
gt;gt; gt; is of
gt;gt; gt; a new point.
gt;gt; gt;
gt;gt; gt; I then would like to calculate the average value of each blockand
gt;gt; gt; display
gt;gt; gt; these.
gt;gt; gt;
gt;gt; gt; The ideal result would be to have the original data in columns A to C,
gt;gt; gt; the
gt;gt; gt; grouped data in coumns E to G, and the averages in I to K.
gt;gt; gt;
gt;gt; gt; This is beyond my meagre VBA abilities. Please help!
gt;gt; gt;
gt;gt; gt; Thanks in advance
gt;gt; gt;
gt;gt; gt; BTW I'm using Excel 2003
gt;gt; gt;
Thanks Ardus Petus, I appreciate it.

I tried it with some sample data. The macro runs ok but doesn't generate any
new output. If I hover the cursor over parts of code, it tells me that iRow =
1, iCurrRow = 65536. Is that right? Also, does the Abs(Cells(iRow,
iCol).Value part look at each cell's value?

I'm guessing that there is no output because the data doesn't satisfy the If
statement.

Any thoughts?

quot;Ardus Petusquot; wrote:

gt; Try this:
gt; '----------
gt; Sub group()
gt; Dim iRow As Long
gt; Dim iCol As Long
gt; Dim iLastRow As Long
gt; Dim iCurrRow As Long
gt; iLastRow = Range(quot;A1quot;).End(xlDown).Row
gt; iCurrRow = iLastRow
gt; For iRow = iLastRow To 2 Step -1
gt; For iCol = 1 To 3
gt; If Abs(Cells(iRow, iCol).Value - _
gt; Cells(iRow - 1, iCol).Value) gt;= 0.1 Then
gt;
gt; Cells(iRow, quot;Equot;).Resize(, 3).FormulaR1C1 = _
gt; quot;=AVERAGE(RC[-4]:R[quot; amp; iCurrRow - iRow amp; quot;]C[-4])quot;
gt; iCurrRow = iRow
gt; Rows(iRow).Insert
gt; Exit For
gt; End If
gt; Next iCol
gt; Next iRow
gt; iRow = 1
gt; Cells(iRow, quot;Equot;).Resize(, 3).FormulaR1C1 = _
gt; quot;=AVERAGE(RC[-4]:R[quot; amp; iCurrRow - iRow amp; quot;]C[-4])quot;
gt;
gt; End Sub
gt; '------------
gt; quot;MMquot; gt; a écrit dans le message de news:
gt; ...
gt; gt; Okay, I'll see what I can do.
gt; gt;
gt; gt; Let's say I've got 70 GPS readings from 5 different locations. Each
gt; gt; reading
gt; gt; is in the form of x,y,z coordinates. Let's say I've taken 15 readings at
gt; gt; point A, 10 at B, 12 at C, 18 at D, and 15 at E. I want to average these
gt; gt; readings to get more accurate value for the coordinates of the five
gt; gt; points.
gt; gt;
gt; gt; In Excel, I have 70 rows, 3 columns. Now, I happen to know that the first
gt; gt; 15
gt; gt; rows were taken at point A, the next 10 at B and so on. For excel to be
gt; gt; able
gt; gt; to identify which points are where, it must compare each row with the
gt; gt; previous one until such time as one or more of the 3 coords differs from
gt; gt; the
gt; gt; previous row by more than 0.1.
gt; gt;
gt; gt; I envisage something along the lines of:
gt; gt;
gt; gt; start at row 1. Copy a1:c1 to e1:g1
gt; gt;
gt; gt; row 2
gt; gt; compare with row 1. conclusion: same point. Action: copy to e2:g2
gt; gt;
gt; gt; and so on until
gt; gt;
gt; gt; row 19
gt; gt; compare with row 18. conclusion: new point. Action: insert blank row. copy
gt; gt; a19:c19 to e20:g20
gt; gt;
gt; gt; The idea being to seperate the readings into blocks so that they can then
gt; gt; be averaged and the averages displayed.
gt; gt;
gt; gt; I said in the first post that
gt; gt;
gt; gt;gt; The ideal result would be to have the original data in columns A to C,
gt; gt;gt; the
gt; gt;gt; grouped data in coumns E to G, and the averages in I to K.
gt; gt;
gt; gt;
gt; gt; but it's not crucial to keep the original data on the worksheet as this
gt; gt; will
gt; gt; still exist elsewhere.
gt; gt;
gt; gt; Anyway, hope this clarifies.
gt; gt;
gt; gt; Marcel
gt; gt;
gt; gt; quot;Ian Pquot; wrote:
gt; gt;
gt; gt;gt; You'll have to give an example of what you are trying to achieve. I'm
gt; gt;gt; afraid
gt; gt;gt; your explanation isn't very clear to me.
gt; gt;gt;
gt; gt;gt; Ian
gt; gt;gt;
gt; gt;gt; quot;Marquismarcequot; wrote:
gt; gt;gt;
gt; gt;gt; gt; Hello All,
gt; gt;gt; gt;
gt; gt;gt; gt; I have three columns of raw data which are x,y and z coordinates
gt; gt;gt; gt; (quot;observationsquot;) for several different points, say points A to Z. The
gt; gt;gt; gt; number
gt; gt;gt; gt; of points is variable as is the number of observations of any given
gt; gt;gt; gt; point.
gt; gt;gt; gt;
gt; gt;gt; gt; I want to group the data into blocks seperated by blank rows by
gt; gt;gt; gt; comparing
gt; gt;gt; gt; each observation with the previous one and then assuming that if the
gt; gt;gt; gt; difference in x,y or z is more than say 0.1, then the next observation
gt; gt;gt; gt; is of
gt; gt;gt; gt; a new point.
gt; gt;gt; gt;
gt; gt;gt; gt; I then would like to calculate the average value of each blockand
gt; gt;gt; gt; display
gt; gt;gt; gt; these.
gt; gt;gt; gt;
gt; gt;gt; gt; The ideal result would be to have the original data in columns A to C,
gt; gt;gt; gt; the
gt; gt;gt; gt; grouped data in coumns E to G, and the averages in I to K.
gt; gt;gt; gt;
gt; gt;gt; gt; This is beyond my meagre VBA abilities. Please help!
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks in advance
gt; gt;gt; gt;
gt; gt;gt; gt; BTW I'm using Excel 2003
gt; gt;gt; gt;
gt;
gt;
gt;

Tried it again today. It works! Dunno what I did wrong yesterday...

Thanks very much for your assistance.quot;MMquot; wrote:

gt; Thanks Ardus Petus, I appreciate it.
gt;
gt; I tried it with some sample data. The macro runs ok but doesn't generate any
gt; new output. If I hover the cursor over parts of code, it tells me that iRow =
gt; 1, iCurrRow = 65536. Is that right? Also, does the Abs(Cells(iRow,
gt; iCol).Value part look at each cell's value?
gt;
gt; I'm guessing that there is no output because the data doesn't satisfy the If
gt; statement.
gt;
gt; Any thoughts?
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; Try this:
gt; gt; '----------
gt; gt; Sub group()
gt; gt; Dim iRow As Long
gt; gt; Dim iCol As Long
gt; gt; Dim iLastRow As Long
gt; gt; Dim iCurrRow As Long
gt; gt; iLastRow = Range(quot;A1quot;).End(xlDown).Row
gt; gt; iCurrRow = iLastRow
gt; gt; For iRow = iLastRow To 2 Step -1
gt; gt; For iCol = 1 To 3
gt; gt; If Abs(Cells(iRow, iCol).Value - _
gt; gt; Cells(iRow - 1, iCol).Value) gt;= 0.1 Then
gt; gt;
gt; gt; Cells(iRow, quot;Equot;).Resize(, 3).FormulaR1C1 = _
gt; gt; quot;=AVERAGE(RC[-4]:R[quot; amp; iCurrRow - iRow amp; quot;]C[-4])quot;
gt; gt; iCurrRow = iRow
gt; gt; Rows(iRow).Insert
gt; gt; Exit For
gt; gt; End If
gt; gt; Next iCol
gt; gt; Next iRow
gt; gt; iRow = 1
gt; gt; Cells(iRow, quot;Equot;).Resize(, 3).FormulaR1C1 = _
gt; gt; quot;=AVERAGE(RC[-4]:R[quot; amp; iCurrRow - iRow amp; quot;]C[-4])quot;
gt; gt;
gt; gt; End Sub
gt; gt; '------------
gt; gt; quot;MMquot; gt; a écrit dans le message de news:
gt; gt; ...
gt; gt; gt; Okay, I'll see what I can do.
gt; gt; gt;
gt; gt; gt; Let's say I've got 70 GPS readings from 5 different locations. Each
gt; gt; gt; reading
gt; gt; gt; is in the form of x,y,z coordinates. Let's say I've taken 15 readings at
gt; gt; gt; point A, 10 at B, 12 at C, 18 at D, and 15 at E. I want to average these
gt; gt; gt; readings to get more accurate value for the coordinates of the five
gt; gt; gt; points.
gt; gt; gt;
gt; gt; gt; In Excel, I have 70 rows, 3 columns. Now, I happen to know that the first
gt; gt; gt; 15
gt; gt; gt; rows were taken at point A, the next 10 at B and so on. For excel to be
gt; gt; gt; able
gt; gt; gt; to identify which points are where, it must compare each row with the
gt; gt; gt; previous one until such time as one or more of the 3 coords differs from
gt; gt; gt; the
gt; gt; gt; previous row by more than 0.1.
gt; gt; gt;
gt; gt; gt; I envisage something along the lines of:
gt; gt; gt;
gt; gt; gt; start at row 1. Copy a1:c1 to e1:g1
gt; gt; gt;
gt; gt; gt; row 2
gt; gt; gt; compare with row 1. conclusion: same point. Action: copy to e2:g2
gt; gt; gt;
gt; gt; gt; and so on until
gt; gt; gt;
gt; gt; gt; row 19
gt; gt; gt; compare with row 18. conclusion: new point. Action: insert blank row. copy
gt; gt; gt; a19:c19 to e20:g20
gt; gt; gt;
gt; gt; gt; The idea being to seperate the readings into blocks so that they can then
gt; gt; gt; be averaged and the averages displayed.
gt; gt; gt;
gt; gt; gt; I said in the first post that
gt; gt; gt;
gt; gt; gt;gt; The ideal result would be to have the original data in columns A to C,
gt; gt; gt;gt; the
gt; gt; gt;gt; grouped data in coumns E to G, and the averages in I to K.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; but it's not crucial to keep the original data on the worksheet as this
gt; gt; gt; will
gt; gt; gt; still exist elsewhere.
gt; gt; gt;
gt; gt; gt; Anyway, hope this clarifies.
gt; gt; gt;
gt; gt; gt; Marcel
gt; gt; gt;
gt; gt; gt; quot;Ian Pquot; wrote:
gt; gt; gt;
gt; gt; gt;gt; You'll have to give an example of what you are trying to achieve. I'm
gt; gt; gt;gt; afraid
gt; gt; gt;gt; your explanation isn't very clear to me.
gt; gt; gt;gt;
gt; gt; gt;gt; Ian
gt; gt; gt;gt;
gt; gt; gt;gt; quot;Marquismarcequot; wrote:
gt; gt; gt;gt;
gt; gt; gt;gt; gt; Hello All,
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I have three columns of raw data which are x,y and z coordinates
gt; gt; gt;gt; gt; (quot;observationsquot;) for several different points, say points A to Z. The
gt; gt; gt;gt; gt; number
gt; gt; gt;gt; gt; of points is variable as is the number of observations of any given
gt; gt; gt;gt; gt; point.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I want to group the data into blocks seperated by blank rows by
gt; gt; gt;gt; gt; comparing
gt; gt; gt;gt; gt; each observation with the previous one and then assuming that if the
gt; gt; gt;gt; gt; difference in x,y or z is more than say 0.1, then the next observation
gt; gt; gt;gt; gt; is of
gt; gt; gt;gt; gt; a new point.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; I then would like to calculate the average value of each blockand
gt; gt; gt;gt; gt; display
gt; gt; gt;gt; gt; these.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; The ideal result would be to have the original data in columns A to C,
gt; gt; gt;gt; gt; the
gt; gt; gt;gt; gt; grouped data in coumns E to G, and the averages in I to K.
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; This is beyond my meagre VBA abilities. Please help!
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; Thanks in advance
gt; gt; gt;gt; gt;
gt; gt; gt;gt; gt; BTW I'm using Excel 2003
gt; gt; gt;gt; gt;
gt; gt;
gt; gt;
gt; gt;