How to set up a sub-total routine?

[Ed]

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:



Date Store Item Style Color Size

1/1/04 A Shoe This Blue 10

1/1/04 A Shoe This Blue 10

1/1/04 A Shoe This Blue 10

1/1/04 A Shoe This Blue 10

1/1/04 A Shoe This Red 10 << new start

1/1/04 A Shoe This Red 10

1/1/04 A Shoe This Red 10

1/1/04 A Shoe This Red 10

1/1/04 B Shoe This Red 10 << new start

1/1/04 B Shoe This Red 10

1/1/04 B Shoe This Red 11 << new start

1/1/04 B Shoe This Red 11



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]

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]

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 Data>>Subtotals. 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

 

[Ed]

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 Data>>Subtotals. 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