What code would I use for this?

[shawn]

Let's say I have a bunch of Item Numbers in Col A. In Col B are quantities
we have in stock.

Is there some code I can use to total up each item group? For example let's
say I have the following:

col a | col b
1282-3622 | 10
1282-3611 | 12
1282-3582 | -5
9601-9000 | 8
9601-3672 | 14

... when the code runs it would turn into this automatically

col a | col b
1282-3622 | 10
1282-3611 | 12
1282-3582 | -5
17
9601-9000 | 8
9601-3672 | 14
22

... it totalled up all the 1282's together, and all the 9601's together. Most
of our item number are 4 or 5 digits, then a dash, then 4 or 5 more digits.

 

[Gord Dibben]

I would insert a column between A and B

Then add titles to A1, B1 and C1 Original Number Amount

In B2 enter =LEFT(A1,4)

Copy down.

Now Data>Subtotals

In each change of Number>Sum by>Amount


Gord Dibben MS Excel MVP

 

[JL]

Use the Group & Subtotal features of Excel. To do this:
- enter the formula "=LEFT(A1,4)" in column C and copy down to all rows
- insert titles in row 1
- highlight the table, select Data/Subtotal, change the "At each change in"
field to the column C title

 

[Per Erik Midtrød]

Let's say I have a bunch of Item Numbers in Col A. In Col B are quantities
we have in stock.

Is there some code I can use to total up each item group? For example let's
say I have the following:

col a | col b
1282-3622 | 10
1282-3611 | 12
1282-3582 | -5
9601-9000 | 8
9601-3672 | 14

.. when the code runs it would turn into this automatically

col a | col b
1282-3622 | 10
1282-3611 | 12
1282-3582 | -5
                    17
9601-9000 | 8
9601-3672 | 14
                    22

.. it totalled up all the 1282's together, and all the 9601's together. Most
of our item number are 4 or 5 digits, then a dash, then 4 or 5 more digits.

If you can add an extra column there is no need to use code.
Insert a column before A1 with the following formula: LEFT(B1;FIND
("-";B1)-1)

Then you can just use Data - Subtotal.

Per Erik

 

[shawn]

Thanks for the help. That was easier than I thought it would be.

I got Gord's way to work. JL's way was similar, but always gave me a total
of 0.

Erik's code didn't work at all.. gave me some error message.

 

[Gord Dibben]

Good to hear you got the solution.

That's one of things about these groups.

You usually get a few responses. At least one will do the job.

Gord