Need to determine where a change in table occurs
 

I am trying to look up grade values (column E) in a table that are identified
by a location, type, and date (columns A, B, and C). There are 2 types (a and
b) which are in alternating odd and even columns (type a in even rows, type b
in odd rows) so there are two grades for each date, and each location can
have multiple and different dates associated. Column D is a calculation to
turn dates into years from initial inspection. Grades for a specific location
and type will stay the same for a number of dates, and then increase to n+1
(or sometimes jump to n+2 or 3€¦).

Row1) Location, Type, Date, Calculation, Grade
Row2) location1, a, m/dd/yyyy, calculation1, n
Row3) location1, b, m/dd/yyyy, calculation1, n
up to about 450 rows and 30 different locations

I am interested in determining the length of time it takes to change from
one grade to the next. In order to do this I need to find the row where the
grade changes from n to n+1, and call that rows year calculation to subtract
from it the year where that grade began. This data will go into column F,
with blank wells where there is no change occurring, and the number of years
for change to occur in the well just before the grade change occurred. Is
there a simple way to do this? I imagined a mix of VLOOKUP and ROW functions
or maybe other table lookup functions I am unaware of, but cant figure out
how. I have the data formatted in a pivot table, but cant see a way to get
this kind of information from it. I want to eventually add this data to the
pivot table, where I can see the average amount of time each grade remains
the same before changing, and determine rate of change. I use Excel 2007.

Thanks for your time

Need to determine where a change in table occurs
 

Wox wrote:
I am trying to look up grade values (column E) in a table that are identified
by a location, type, and date (columns A, B, and C). There are 2 types (a and
b) which are in alternating odd and even columns (type a in even rows, type b
in odd rows) so there are two grades for each date, and each location can
have multiple and different dates associated. Column D is a calculation to
turn dates into years from initial inspection. Grades for a specific location
and type will stay the same for a number of dates, and then increase to n+1
(or sometimes jump to n+2 or 3€¦).

Row1) Location, Type, Date, Calculation, Grade
Row2) location1, a, m/dd/yyyy, calculation1, n
Row3) location1, b, m/dd/yyyy, calculation1, n
up to about 450 rows and 30 different locations

I am interested in determining the length of time it takes to change from
one grade to the next. In order to do this I need to find the row where the
grade changes from n to n+1, and call that rows year calculation to subtract
from it the year where that grade began. This data will go into column F,
with blank wells where there is no change occurring, and the number of years
for change to occur in the well just before the grade change occurred. Is
there a simple way to do this? I imagined a mix of VLOOKUP and ROW functions
or maybe other table lookup functions I am unaware of, but cant figure out
how. I have the data formatted in a pivot table, but cant see a way to get
this kind of information from it. I want to eventually add this data to the
pivot table, where I can see the average amount of time each grade remains
the same before changing, and determine rate of change. I use Excel 2007.

Thanks for your time

Hello

I could not make out whether a change in location is significant for
you. Here are formulas for either requirement.

In all cases, copy/paste in cell F2 and fill down. Assumes rows are
chronological within location.

If a change in location /is not/ significant,

(formula 1 - result in days)
=IF(E2=E3,"",C2-INDEX(C:C,MATCH(E2,E:E,0),1))

(formula 2 - result in fractional years)
=IF(E2=E3,"",(C2-INDEX(C:C,MATCH(E2,E:E,0),1))/365)


If a change in location /is/ significant, we have to add some criteria:

(formula 3* - result in days)
=IF(OR(E2=E3,A2<A3),"",C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))

(formula 4* - result in fractional years)
=IF(OR(E2=E3,A2<A3),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))/365)

*formulas 3 and 4 must be array entered. After pasting, press
Ctrl+Shift+Enter, do not just press Enter or Tab. Also, I hard-coded a
limit of 999 rows, which should suit your need of ~450, but can be
changed as needed.

Need to determine where a change in table occurs
 

Thanks for the reply Smartin,

Your formula 4 is really close I believe, and much more efficient than what
I was trying to use. The results do need to be separated out by location, but
also by area. This is what was tripping me up, as they are entered in every
other row. I think this is a little closer, but still not quite right:

=IF(OR(E2=E4,A2<A4),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))/365)

Thanks for any additional time you can help out,
Wox

Need to determine where a change in table occurs
 

Hmm, "area" was not in the original description. Can you show how the
layout?

Wox wrote:
Thanks for the reply Smartin,

Your formula 4 is really close I believe, and much more efficient than what
I was trying to use. The results do need to be separated out by location, but
also by area. This is what was tripping me up, as they are entered in every
other row. I think this is a little closer, but still not quite right:

=IF(OR(E2=E4,A2<A4),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))/365)

Thanks for any additional time you can help out,
Wox

Need to determine where a change in table occurs
 

Sorry, when I said area, actually meant type, which can be 'a' or 'b' in
alternating rows. The formula currently will look up the first instance of
the grade in the current row, regardless of whether that first instance is
type a or b. I would need the formula to make a check for the earliest grade
of the correct type within the current location.
Hope this helps,
Thanks again for your time,
Wox

"smartin" wrote:

Hmm, "area" was not in the original description. Can you show how the
layout?

Wox wrote:
Thanks for the reply Smartin,

Your formula 4 is really close I believe, and much more efficient than what
I was trying to use. The results do need to be separated out by location, but
also by area. This is what was tripping me up, as they are entered in every
other row. I think this is a little closer, but still not quite right:

=IF(OR(E2=E4,A2<A4),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))/365)

Thanks for any additional time you can help out,
Wox

Need to determine where a change in table occurs
 

Hi, I'm not sure if this is what you need, but give it a look.

=IF(OR(E2=E3,A2<A3),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 )*(B2=$B$1:$B$999),0),1))/365)

Wox wrote:
Sorry, when I said area, actually meant type, which can be 'a' or 'b' in
alternating rows. The formula currently will look up the first instance of
the grade in the current row, regardless of whether that first instance is
type a or b. I would need the formula to make a check for the earliest grade
of the correct type within the current location.
Hope this helps,
Thanks again for your time,
Wox

"smartin" wrote:

Hmm, "area" was not in the original description. Can you show how the
layout?

Wox wrote:
Thanks for the reply Smartin,

Your formula 4 is really close I believe, and much more efficient than what
I was trying to use. The results do need to be separated out by location, but
also by area. This is what was tripping me up, as they are entered in every
other row. I think this is a little closer, but still not quite right:

=IF(OR(E2=E4,A2<A4),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))/365)

Thanks for any additional time you can help out,
Wox

Need to determine where a change in table occurs
 

Thank you Smartin,

That function works great. Just had to modify the blank cell check to E2=E4
or A2<A4.
Appreciate it,
Wox

"smartin" wrote:

Hi, I'm not sure if this is what you need, but give it a look.

=IF(OR(E2=E3,A2<A3),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 )*(B2=$B$1:$B$999),0),1))/365)

Wox wrote:
Sorry, when I said area, actually meant type, which can be 'a' or 'b' in
alternating rows. The formula currently will look up the first instance of
the grade in the current row, regardless of whether that first instance is
type a or b. I would need the formula to make a check for the earliest grade
of the correct type within the current location.
Hope this helps,
Thanks again for your time,
Wox

"smartin" wrote:

Hmm, "area" was not in the original description. Can you show how the
layout?

Wox wrote:
Thanks for the reply Smartin,

Your formula 4 is really close I believe, and much more efficient than what
I was trying to use. The results do need to be separated out by location, but
also by area. This is what was tripping me up, as they are entered in every
other row. I think this is a little closer, but still not quite right:

=IF(OR(E2=E4,A2<A4),"",(C2-INDEX(C:C,MATCH(1,(E2=$E$1:$E$999)*(A2=$A$1:$A$999 ),0),1))/365)

Thanks for any additional time you can help out,
Wox