Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I think this is difficult - can it be done?
Hi All,
Sorry for the vague header but l couldn't figure out a sutiable title. I would like to know if there is a way to do this: T1.25 L2.00 T0.25 -------- 3.00 Total of column ignoring letter prefixes If it makes it easier the prefixes could be suffixes instead. Spaces or characters such as : # could be used to seperate the letters from the figures. This is required for a 'Resource Planner' that l am developing that is required to span as many days as possible accross the columns so using a helper column is not a practical option. Additionally l would like to apply group sub-totals (preferably using data/subtotals) and still have the column summed ignoring the letters. Has anyone got any ideas please? TIA Regards Michael Beckinsale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I think this is difficult - can it be done?
=SUMPRODUCT(--(--RIGHT(A1:A3,LEN(A1:A3)-1)))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "michael.beckinsale" wrote in message oups.com... Hi All, Sorry for the vague header but l couldn't figure out a sutiable title. I would like to know if there is a way to do this: T1.25 L2.00 T0.25 -------- 3.00 Total of column ignoring letter prefixes If it makes it easier the prefixes could be suffixes instead. Spaces or characters such as : # could be used to seperate the letters from the figures. This is required for a 'Resource Planner' that l am developing that is required to span as many days as possible accross the columns so using a helper column is not a practical option. Additionally l would like to apply group sub-totals (preferably using data/subtotals) and still have the column summed ignoring the letters. Has anyone got any ideas please? TIA Regards Michael Beckinsale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I think this is difficult - can it be done?
One way,
Using a prefix of # before your data you could achieve what you require with text to columns by:- Select the column Data|text to columns Select the delimeter # and then only import the number part which in effect removes all the prefix data from the numbers and leaves the numbers in the same column. If the numbers are always a fixed width then you could use that instead of a delimeter to decide on where to split the data. Mike "michael.beckinsale" wrote: Hi All, Sorry for the vague header but l couldn't figure out a sutiable title. I would like to know if there is a way to do this: T1.25 L2.00 T0.25 -------- 3.00 Total of column ignoring letter prefixes If it makes it easier the prefixes could be suffixes instead. Spaces or characters such as : # could be used to seperate the letters from the figures. This is required for a 'Resource Planner' that l am developing that is required to span as many days as possible accross the columns so using a helper column is not a practical option. Additionally l would like to apply group sub-totals (preferably using data/subtotals) and still have the column summed ignoring the letters. Has anyone got any ideas please? TIA Regards Michael Beckinsale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I think this is difficult - can it be done?
Hi Bob,
Many thanks, l didn't think of that! A difficult problem made easy when you know how. Any ideas on how that formula could be applied instead of the usual sub-totals formula (preferable using data/subtotals) or by using VBA. My main concern is performance but it is also important that the subtotals are applied with grouping so that the + / - signs can be used to expand & collapse. The number of columns required is approx 270 and approx 500 rows with 100 subtotals. The subtotals will be conditionally formatted. Mike - thanks for your input but l think you missed the bit about 'helper' colomns. TIA Regards MB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
difficult | Excel Programming | |||
Help, too difficult for me. | Excel Worksheet Functions | |||
Difficult but do-able? | Excel Discussion (Misc queries) | |||
Is this difficult??? | Excel Programming | |||
Ok I have to be difficult | Excel Programming |