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

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

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

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

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 -

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 -

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 -

Pete,

I use the subtotal function in the "data" drop down menu. Should I be using
a different method to insert the subtotals in order to use your info?

Thanks

"Pete_UK" wrote:

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 -

Jon,

here's a slightly neater version of the formula:

=IF(ISNUMBER(VALUE(MID(A2,LEN(A2)-1,1))),LEFT(A2,LEN(A2)-1),LEFT(A2,LEN(A2)-2))

This time it looks at A2, and can be copied down the column.

To use the Data | Subtotals... function you need to have headings
above your data and the data needs to be sorted. I assume you will
have headings in row 1. If you put my formula in, say, column F, you
will need to put a heading in F1 (let's say you call it ITEM_num).
Then you need to highlight all your data and headings (including the
extra helper column) and then in Data | Subtotals you need to select
For each change in ITEM_num rather than in item code as you would have
done before. You may need to clear all previous subtotals first
(bottom of the panel).

Hope this helps.

Pete

On Feb 12, 7:57 pm, jmcclain
wrote:
Pete,

I use the subtotal function in the "data" drop down menu. Should I be using
a different method to insert the subtotals in order to use your info?

Thanks



"Pete_UK" wrote:
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 -- Hide quoted text -

- Show quoted text -