Pretty Easy Excel Formula to combine prices of items if the first 3 digits are the same.
Hi Excel Experts!
Basically, I have items with ID's. Example:
ITEM ID Extended Cost
100293 $40
300923 $30
100533 $8
200533 $40
1002433 $10
400253 $80
2002343 $28
If you notice, the first 3 numbers in the item ID is what we use for our categories (100, 200, 300, and 400).
I need a formula that will take the first 3 numbers of the item id and add the extended cost into one final output such as:
100 = 58
200 = 30
300 = 68
400 = 80
I have attached an example of what our actual document looks like when we export it from our software. If someone can help me with this excel formula I can then write the applescript that our user has to run that would automate this.
Note: I am running Microsoft Office 2008 and 2011 (which supports Macros). I am on a MAC.
I plan on creating an applescript.app that would automate this. But if someone could make this into a excel macro that would work for Office 2011 then AWESOME!
Otherwise, I will be fine with just the formula; and write the script myself. I just suck at excel formulas. Thanks experts.
Example.xlsx
Basically, I have items with ID's. Example:
ITEM ID Extended Cost
100293 $40
300923 $30
100533 $8
200533 $40
1002433 $10
400253 $80
2002343 $28
If you notice, the first 3 numbers in the item ID is what we use for our categories (100, 200, 300, and 400).
I need a formula that will take the first 3 numbers of the item id and add the extended cost into one final output such as:
100 = 58
200 = 30
300 = 68
400 = 80
I have attached an example of what our actual document looks like when we export it from our software. If someone can help me with this excel formula I can then write the applescript that our user has to run that would automate this.
Note: I am running Microsoft Office 2008 and 2011 (which supports Macros). I am on a MAC.
I plan on creating an applescript.app that would automate this. But if someone could make this into a excel macro that would work for Office 2011 then AWESOME!
Otherwise, I will be fine with just the formula; and write the script myself. I just suck at excel formulas. Thanks experts.
Example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why a B grade?
Would something like this suffice?
I'm not fantastic with VBA, unfortunately, but it appears to work.
Sub Calc100()
a = 0
b = 0
c = 0
d = 0
For x = 13 To 23
If (Left(Cells(x, 3), 3) = 100) Then a = (a + Cells(x, 9))
If (Left(Cells(x, 3), 3) = 200) Then b = (b + Cells(x, 9))
If (Left(Cells(x, 3), 3) = 300) Then c = (c + Cells(x, 9))
If (Left(Cells(x, 3), 3) = 400) Then d = (d + Cells(x, 9))
Next x
Cells(11, 13) = "100 = " & a
Cells(12, 13) = "200 = " & b
Cells(13, 13) = "300 = " & c
Cells(14, 13) = "400 = " & d
End SubI'm not fantastic with VBA, unfortunately, but it appears to work.
Nevermind! Always refresh the page before submitting :)
If the answer was a "Fast response", why the B grade?!?
ASKER