Auto-calculate 2 column that contains numbers and words words

[RepetitiveLady]

Hi everyone,

I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:

Table 1 - Before totaling up:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"><tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr><td style="vertical-align: top;">5
</td><td style="vertical-align: top;">2 x Button A White
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">4 x Button B Pink
</td></tr><tr><td style="vertical-align: top;">4
</td><td style="vertical-align: top;">5 x Ribbon A Black
</td></tr><tr><td style="vertical-align: top;">2
</td><td style="vertical-align: top;">3 x Thread A White
</td></tr><tr><td style="vertical-align: top;">6
</td><td style="vertical-align: top;">2 x Cloth A Blue
</td></tr></tbody></table>

Table 2 - After totaling up:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"> <tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr> <td style="vertical-align: top;">10
</td> <td style="vertical-align: top;">Button A White
</td> </tr> <tr> <td style="vertical-align: top;">12
</td> <td style="vertical-align: top;">Button B Pink
</td> </tr> <tr> <td style="vertical-align: top;">20
</td> <td style="vertical-align: top;">Ribbon A Black
</td> </tr> <tr> <td style="vertical-align: top;">6
</td> <td style="vertical-align: top;">Thread A White
</td> </tr> <tr> <td style="vertical-align: top;">12
</td> <td style="vertical-align: top;">Cloth A Blue
</td> </tr> </tbody> </table>

I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.

Can anyone help me out? I'm tired of the conventional method which is extremely time consuming as the list is getting really long.

Cheers
- Cassandra -

 

[Datsmart]

This can be done by a Macro:

Sub Macro1()
'Assign variable for last Row in column B
LR = Range("B65536").End(xlUp).Row
'Text to Columns
    Range("B2:B" & LR).TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="x", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
'Insert New Column
    Columns("C:C").Insert Shift:=xlToRight
'Assign Formulas to Range
    Range("C2:C" & LR).FormulaR1C1 = "=RC[-2]*RC[-1]"
'Copy/Paste Values inplace of Formulas
    Range("C2:C" & LR).Copy
    Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Move Range to Cell A2
    Range("C2:D" & LR).Cut
    Range("A2").Select
    ActiveSheet.Paste
End Sub

The code uses Text to Columns to separate the number from column B.
Then inserts an extra column, inserts a formula, then converts the formula to a value.
It then moves the data over to column A.

 

[MickG]

Hi, Try this:-
NB:- This will overwrite your Data in columns "A" & "B".

[COLOR=navy]Sub[/COLOR] MG16Jul05
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Pt
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Pt = Split(Dn.Next, chr(32))
    Dn = Dn * Pt(0)
    Dn.Next = Right(Dn.Next, Len(Dn.Next) - (Len(Pt(0)) + Len(Pt(1)) + 2))
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[RepetitiveLady]

Sorry Datsmart, i couldn't get yours to work.

When i run the macro, the only thing i got is #VALUE! in every single row of the column A.

Is there anything special that i need to take note of before running the macro?

 

[RepetitiveLady]

Hi MickG,

i've just tried to use your macro. It does work, but only half way through it. After some rows of converting, a Run Time Error popped up saying:

Run-time error '13':
Type mismatch

When i click on the Debug button, a Visual Basic window popped up and show the following lines highlighted:

Dn = Dn * Pt(0)

I think i forgot to let u know something important. Not all the products in column B will contain the quantity:

Example:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"><tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr><td style="vertical-align: top;">5
</td><td style="vertical-align: top;">2 x Button A White
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">Button B Pink
</td></tr><tr><td style="vertical-align: top;">4
</td><td style="vertical-align: top;">5 x Ribbon A Black
</td></tr><tr><td style="vertical-align: top;">2
</td><td style="vertical-align: top;">3 x Thread A White
</td></tr><tr><td style="vertical-align: top;">6
</td><td style="vertical-align: top;">Cloth A Blue
</td></tr></tbody></table>
Sorry about that.
Can you help me out?

 

[Phox]

A well written macro should do the job just fine but whats wrong with a formula solution?

Assuming Quantity in Column A and Product in Column B, these two formulas should work:

=A2*LEFT(B2,SEARCH(" ",B2)-1)

and

=RIGHT(B2,LEN(B2)-SEARCH(" ",B2)-2)

 

[Phox]

Oh. And since quantities are not always given, change my formulas to these:

=IF(ISNUMBER(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),A2*LEFT(B2,SEARCH(" ",B2)-1), A2)

and

=IF(ISNUMBER(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),RIGHT(B2,LEN(B2)-SEARCH(" ",B2)-2),B2)

 

[RepetitiveLady]

Thanks Phox,

I've tried your codes, it works.
but after processing the Products without the quantity. Like the example below:


<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"><tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr><td style="vertical-align: top;">5
</td><td style="vertical-align: top;">2 x Button A White
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">Button B Pink
</td></tr><tr><td style="vertical-align: top;">4
</td><td style="vertical-align: top;">5 x Ribbon A Black
</td></tr><tr><td style="vertical-align: top;">2
</td><td style="vertical-align: top;">3 x Thread A White
</td></tr><tr><td style="vertical-align: top;">6
</td><td style="vertical-align: top;">Cloth A Blue</td></tr></tbody></table>
The Quantity for "Button B Pink" and "Cloth A Blue" will appear as below:

#VALUE!

As i have quite huge number of products with and without the quantity at the "Product" column, i couldn't use this code.

Please help.

 

[Phox]

Are you using the second set of formulas? They should fix that problem.

 

[RepetitiveLady]

Thanks Phox,

Sorry, i didn't see your 2nd post.
I've tried your 2nd codes, it works.

unfortunately, another problem arise.

I have some item names that start with number, for example:

<table dir="ltr" border="1" bordercolor="#05ad00" cellpadding="2" cellspacing="1" height="154" width="448"> <tbody><tr><td align="center" height="4" width="14%">

Quantity
​

</td><td height="4" width="14%">Product
</td></tr><tr><td style="vertical-align: top;">5
</td><td style="vertical-align: top;">2 x Button A White
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">Button B Pink
</td></tr><tr><td style="vertical-align: top;">4
</td><td style="vertical-align: top;">5 x Ribbon A Black
</td></tr><tr><td style="vertical-align: top;">3
</td><td style="vertical-align: top;">6.5 feet Thread A White
</td></tr><tr><td style="vertical-align: top;">6
</td><td style="vertical-align: top;">9925 Cloth A Blue</td></tr></tbody> </table>
"9925 Cloth A Blue" after being processed will become "59550 oth A Blue"
6 multiply by 9925 equals to 59550. and the "Cl" in front of the word Cloth is missing.
The item code is supposed to be "9925 Cloth A Blue"
The problem also happens to "6.5 feet Thread A White" where after being processed will become "19.5 et Thread A White".

I don't worry much about this problem though as i have only a few items with codes that start with numbers in front.
But if u take this problem as a challenge, i would love to learn.

Cheers
- Cassandra -