Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a sub-total routine?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a sub-total routine?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a sub-total routine?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to set up a sub-total routine?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sub routine | Excel Discussion (Misc queries) | |||
SUB ROUTINE | Excel Programming | |||
Which routine called Sub()? | Excel Programming | |||
Routine?? | Excel Programming | |||
Need VBA Routine | Excel Programming |