Subtotal GroupBy text string
 

Hi,


Is there anyway to use excel vba codes in a worksheet of about 20 or
3,000 rows or more ( ie dynamic rows and can be no row at all ), that
can subtotal column B to N for each group of text string in column A
which has met full or partial similar text.
The Subtotal will insert after each grouping in every Section ( total
6 sections )
The results will display subtotal with each grouping of similar text
string that fully or partially matched the text string within the same
group together with grand total ( ie include subtotal )


E.g.

ColA ColB C D E ----N
Section A
xxxxxxxxxxxxx 3 1 0 6
xxx Apple xxxxx 20 5 2 3
xxxxxxxxxxx 4 8 12 70
Apple xxxxxxx 1 0 6 150
xxxxxxxxxxxxx 70 6 15 33
"Total Apple" 21 5 8 153
xxxxxxxxxxxx 6 14 0 11
Bananaxxxxxx 0 2 6 10
xxxxxxxxxxxx 9 9 17 6
"Total Banana" 0 2 6 10
xxxxxxxxxxxx 8 35 99 55
Grand Total zzz zz zz zzz

Section B
xxxxxxxxxxxxx 8 0 16 80
xxxxxOrangexx 1 2 3 4
xxxxxxxxxxxxxv 5 6 7 8
xxxxxxxxOrange 9 10 11 12
Orangexxxxxx 13 14 15 16
"Total Orange" 23 26 28 32
xxxxxxxxxxxx 1 2 3 6
xxxxxxxxxxxx 6 5 3 4
xxxxxxxxxxxx 5 4 8 11
Grand Total yyy yy yy yyy


Any helps will be greatly appreciated and thanks in advance


Regards
Len

Subtotal GroupBy text string
 

I'd use a helper column that would return a true or false depending on if the
substring was found in column A.

=countif(a2,"*apple*")0
or
=countif(a2,"*"&z99&"*")0
if z99 held the characters "apple"
(the asterisks are wild cards)

Drag down the column as far as you need and then apply your data|subtotals based
on this helper column.


Len wrote:

Hi,

Is there anyway to use excel vba codes in a worksheet of about 20 or
3,000 rows or more ( ie dynamic rows and can be no row at all ), that
can subtotal column B to N for each group of text string in column A
which has met full or partial similar text.
The Subtotal will insert after each grouping in every Section ( total
6 sections )
The results will display subtotal with each grouping of similar text
string that fully or partially matched the text string within the same
group together with grand total ( ie include subtotal )

E.g.

ColA ColB C D E ----N
Section A
xxxxxxxxxxxxx 3 1 0 6
xxx Apple xxxxx 20 5 2 3
xxxxxxxxxxx 4 8 12 70
Apple xxxxxxx 1 0 6 150
xxxxxxxxxxxxx 70 6 15 33
"Total Apple" 21 5 8 153
xxxxxxxxxxxx 6 14 0 11
Bananaxxxxxx 0 2 6 10
xxxxxxxxxxxx 9 9 17 6
"Total Banana" 0 2 6 10
xxxxxxxxxxxx 8 35 99 55
Grand Total zzz zz zz zzz

Section B
xxxxxxxxxxxxx 8 0 16 80
xxxxxOrangexx 1 2 3 4
xxxxxxxxxxxxxv 5 6 7 8
xxxxxxxxOrange 9 10 11 12
Orangexxxxxx 13 14 15 16
"Total Orange" 23 26 28 32
xxxxxxxxxxxx 1 2 3 6
xxxxxxxxxxxx 6 5 3 4
xxxxxxxxxxxx 5 4 8 11
Grand Total yyy yy yy yyy

Any helps will be greatly appreciated and thanks in advance

Regards
Len

--

Dave Peterson

Subtotal GroupBy text string
 

Hi Dave,

Thanks for your reply and your advice
It is a good idea to use a helper column to search for substring and
then apply subtotal.

It works !

Regards
Len