Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Text the Apply a SubTotal Next to Text Address | Excel Programming | |||
Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
Mimicking an GroupBy Clause in Excel | Excel Programming | |||
Barchart 'GroupBy' Question | Charts and Charting in Excel |