What code would I use for this?

  • Thread starter Thread starter shawn
  • Start date Start date
S

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.
 
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
 
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
 
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.
Click to expand...

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
 
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.
 
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
 
Back
Top