It looks like it should work, but I'm not understanding how it works so I'm
having trouble adapting it. My actual layout has an incrementing key number
in Col. A, and the data fields which can vary in Cols. B:G. I have headers
in Row 1.
In H3, I set the formula
=SUMPRODUCT(N(B2:F2=B3:F3))=5
and ran it down. I noticed H2 was (of course) blank, so I put TRUE in it.
I then selected the entire range from A1 to H24 (it goes on down much
farther, but this just to test it), and went to DataSubtotals. I set "At
Each Change In" to the header for Col. G, and "Add Totals To" Col. H. The
totals did not come out right.
I thought maybe I shouldn't include the incrementing key number, so I
selected only B1:H24 - the OK button was not available.
What have I missed in my non-understanding?
Ed
"keepitcool" wrote in message
...
is this an idea?
starting in f2:
=SUMPRODUCT(N(A1:E1=A2:E2))=5
copy it down.
then use Data/Subtotals on the range
and use col6 as ID.
off the cuff. you may need to adapt.
I use sumproduct() so there's NO need for 'array formula entry'
I use n() to force boolean to integer conversion.
keepITcool
< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool
"Ed" wrote:
Not sure if this needs to be VBA or a formula, or even if there's a
built-in function, so I'll start here and let someone drop-kick me in
the right direction.
I've got six columns of information. I need to sub-total as long as
the information in all six columns stays the same; as soon as one item
in one column changes, I start counting at 1 again and sub-total. For
instance:
My first thought is to build a string consisting of all the text
values in the first row, drop a row and build a similar string, and
compare. If they match, increment my count. If they don't match, set
my current count aas the sub-total, take the dis-similar row as the
new starting point, and begin comparing again. Is there an easier and
better way?
Ed
"keepitcool" wrote in message
...
is this an idea?
starting in f2:
=SUMPRODUCT(N(A1:E1=A2:E2))=5
copy it down.
then use Data/Subtotals on the range
and use col6 as ID.
off the cuff. you may need to adapt.
I use sumproduct() so there's NO need for 'array formula entry'
I use n() to force boolean to integer conversion.
keepITcool
< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool
"Ed" wrote:
Not sure if this needs to be VBA or a formula, or even if there's a
built-in function, so I'll start here and let someone drop-kick me in
the right direction.
I've got six columns of information. I need to sub-total as long as
the information in all six columns stays the same; as soon as one item
in one column changes, I start counting at 1 again and sub-total. For
instance:
My first thought is to build a string consisting of all the text
values in the first row, drop a row and build a similar string, and
compare. If they match, increment my count. If they don't match, set
my current count aas the sub-total, take the dis-similar row as the
new starting point, and begin comparing again. Is there an easier and
better way?
Ed