| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concerning subtotals and inserting
I am trying to insert subtotals in a spreadsheet at each change of "item code"
However, the item codes are shown as "FL3909B", but I need to insert the subtotal at each change in the core item #, that being "FL3909". Can anyone tell me if it's possible and how to do it? With Regards, Jon |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concerning subtotals and inserting
If your codes are all of the same format, i.e. with a single letter at
the end, then you could add this formula in a helper column: =LEFT(A2,LEN(A2)-1) and copy down, assuming your item codes are in column A. Then use this helper column to determine the change (and therefore where the subtotal appears), rather than the item code. Hope this helps. Pete On Feb 12, 4:57 pm, jmcclain wrote: I am trying to insert subtotals in a spreadsheet at each change of "item code" However, the item codes are shown as "FL3909B", but I need to insert the subtotal at each change in the core item #, that being "FL3909". Can anyone tell me if it's possible and how to do it? With Regards, Jon |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concerning subtotals and inserting
Dear Pete,
Unfortunately, the codes have either a 1 or 2 character color. For example, either FL3990B or FL3990RB. Anyway to handle this? "Pete_UK" wrote: If your codes are all of the same format, i.e. with a single letter at the end, then you could add this formula in a helper column: =LEFT(A2,LEN(A2)-1) and copy down, assuming your item codes are in column A. Then use this helper column to determine the change (and therefore where the subtotal appears), rather than the item code. Hope this helps. Pete On Feb 12, 4:57 pm, jmcclain wrote: I am trying to insert subtotals in a spreadsheet at each change of "item code" However, the item codes are shown as "FL3909B", but I need to insert the subtotal at each change in the core item #, that being "FL3909". Can anyone tell me if it's possible and how to do it? With Regards, Jon |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concerning subtotals and inserting
Dear Pete -
Another caveat - the actual item codes can be either 5 or 6 characters, such as FL3990 or FL910 followed by either a 1 or 2 character color code such as "RB" or "B". Any ideas? "Pete_UK" wrote: If your codes are all of the same format, i.e. with a single letter at the end, then you could add this formula in a helper column: =LEFT(A2,LEN(A2)-1) and copy down, assuming your item codes are in column A. Then use this helper column to determine the change (and therefore where the subtotal appears), rather than the item code. Hope this helps. Pete On Feb 12, 4:57 pm, jmcclain wrote: I am trying to insert subtotals in a spreadsheet at each change of "item code" However, the item codes are shown as "FL3909B", but I need to insert the subtotal at each change in the core item #, that being "FL3909". Can anyone tell me if it's possible and how to do it? With Regards, Jon |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concerning subtotals and inserting
Will you always have a numerical part in the middle followed by one or
two characters? Pete On Feb 12, 7:18 pm, jmcclain wrote: Dear Pete - Another caveat - the actual item codes can be either 5 or 6 characters, such as FL3990 or FL910 followed by either a 1 or 2 character color code such as "RB" or "B". Any ideas? "Pete_UK" wrote: If your codes are all of the same format, i.e. with a single letter at the end, then you could add this formula in a helper column: =LEFT(A2,LEN(A2)-1) and copy down, assuming your item codes are in column A. Then use this helper column to determine the change (and therefore where the subtotal appears), rather than the item code. Hope this helps. Pete On Feb 12, 4:57 pm, jmcclain wrote: I am trying to insert subtotals in a spreadsheet at each change of "item code" However, the item codes are shown as "FL3909B", but I need to insert the subtotal at each change in the core item #, that being "FL3909". Can anyone tell me if it's possible and how to do it? With Regards, Jon- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concerning subtotals and inserting
Dear Pete,
The item codes always start with 2 alpha characters, then either 3 or 4 numerical characters, and then either 1 or 2 alpha characters. Thanks Jon "Pete_UK" wrote: Will you always have a numerical part in the middle followed by one or two characters? Pete On Feb 12, 7:18 pm, jmcclain wrote: Dear Pete - Another caveat - the actual item codes can be either 5 or 6 characters, such as FL3990 or FL910 followed by either a 1 or 2 character color code such as "RB" or "B". Any ideas? "Pete_UK" wrote: If your codes are all of the same format, i.e. with a single letter at the end, then you could add this formula in a helper column: =LEFT(A2,LEN(A2)-1) and copy down, assuming your item codes are in column A. Then use this helper column to determine the change (and therefore where the subtotal appears), rather than the item code. Hope this helps. Pete On Feb 12, 4:57 pm, jmcclain wrote: I am trying to insert subtotals in a spreadsheet at each change of "item code" However, the item codes are shown as "FL3909B", but I need to insert the subtotal at each change in the core item #, that being "FL3909". Can anyone tell me if it's possible and how to do it? With Regards, Jon- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concerning subtotals and inserting
Ok, Jon, in that case you can try this formula:
=IF(AND(CODE(MID(A1,LEN(A1)-1,1))47,CODE(MID(A1,LEN(A1)-1,1))<58),LEFT(A1,LEN(A1)-1),LEFT(A1,LEN(A1)-2)) Basically, it looks at the second character from the right - if this is a number (i.e. only one alpha character at the end) then take the left hand part minus one character, otherwise take the left hand part minus two characters. I tested this out in A1 - amend if necessary. Copy this down your helper column. Hope this helps. Pete On Feb 12, 7:34 pm, jmcclain wrote: Dear Pete, The item codes always start with 2 alpha characters, then either 3 or 4 numerical characters, and then either 1 or 2 alpha characters. Thanks Jon "Pete_UK" wrote: Will you always have a numerical part in the middle followed by one or two characters? Pete On Feb 12, 7:18 pm, jmcclain wrote: Dear Pete - Another caveat - the actual item codes can be either 5 or 6 characters, such as FL3990 or FL910 followed by either a 1 or 2 character color code such as "RB" or "B". Any ideas? "Pete_UK" wrote: If your codes are all of the same format, i.e. with a single letter at the end, then you could add this formula in a helper column: =LEFT(A2,LEN(A2)-1) and copy down, assuming your item codes are in column A. Then use this helper column to determine the change (and therefore where the subtotal appears), rather than the item code. Hope this helps. Pete On Feb 12, 4:57 pm, jmcclain wrote: I am trying to insert subtotals in a spreadsheet at each change of "item code" However, the item codes are shown as "FL3909B", but I need to insert the subtotal at each change in the core item #, that being "FL3909". Can anyone tell me if it's possible and how to do it? With Regards, Jon- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| inserting nested subtotals in excel | Setting up and Configuration of Excel | |||
| How do I display both a sum total and an average in pivot table? | Excel Worksheet Functions | |||
| Loan amortization schedule should have subtotals for each year | Excel Worksheet Functions | |||
| SUBTOTALS IN EXCEL | Excel Discussion (Misc queries) | |||
| subtotals | Excel Discussion (Misc queries) | |||
Hybrid Mode
