Is there a way to parse multiple values in a cell and applycalculations to those cell ?

[exceluser]

Is there a way to parse multiple values in a cell and apply a
calculation on those values ?

The Inventory column has a formula to determine the total inventory of
the store where each unique item has its quantity appended in
parentheses to the item’s name. Each unique item is separated by a
comma. Each item’s price will change over time and VLOOKUP can be used
to determine the current price of that item on another worksheet.

What I’m trying to do is:

1) Parse out the multiple values in the Inventory column on the
INVENTORY worksheet

2) VLOOKUP each item’s current price on the PRICES worksheet and
multiply it by the item’s quantity

3) SUM the value for all items and display it in the
Inventory_Value column on the INV_CHART worksheet

The following would be stored on a worksheet named INVENTORY.

Date Item Qty Price Inventory
1/1/2010 Apples 1 2 Apples(1)
1/3/2010 Oranges 3 2 Apples(1),Oranges(3)
1/5/2010 Apples 0 1.75 Oranges(3)

The following would be stored on another worksheet called PRICES:

Item 1/1/2010 1/2/2010 1/3/2010 1/4/2010 1/5/2010
Apples 2 2.5 2.25 2.5 1.75
Oranges 2 1.75 2 2.5 2

The Inventory_Value column values will be used for charting purposes.
The goal is to be able to determine the inventory’s value on any given
day.

The following would be the data source for a chart on another
worksheet called INV_CHART:

Date Inventory_Value
1/1/2010 2
1/2/2010 2.50
1/3/2010 8.25
1/4/2010 10
1/5/2010 6



Exceluser

 

[Don Guillett Excel MVP]

Is there a way to parse multiple values in a cell and apply a
calculation on those values ?

The Inventory column has a formula to determine the total inventory of
the store where each unique item has its quantity appended in
parentheses to the item’s name. Each unique item is separated by a
comma. Each item’s price will change over time and VLOOKUP can be used
to determine the current price of that item on another worksheet.

What I’m trying to do is:

   1) Parse out the multiple values in the Inventory column on the
INVENTORY worksheet

   2) VLOOKUP each item’s current price on the PRICES worksheet and
multiply it by the item’s quantity

   3) SUM the value for all items and display it in the
Inventory_Value column on the INV_CHART worksheet

The following would be stored on a worksheet named INVENTORY.

Date    Item    Qty     Price   Inventory
1/1/2010        Apples  1       2       Apples(1)
1/3/2010        Oranges 3       2       Apples(1),Oranges(3)
1/5/2010        Apples  0       1.75    Oranges(3)

The following would be stored on another worksheet called PRICES:

Item    1/1/2010        1/2/2010        1/3/2010        1/4/2010        1/5/2010
Apples  2       2.5     2.25    2.5     1.75
Oranges 2       1.75    2       2.5     2

The Inventory_Value column values will be used for charting purposes.
The goal is to be able to determine the inventory’s value on any given
day.

The following would be the data source for a chart on another
worksheet called INV_CHART:

Date    Inventory_Value
1/1/2010        2
1/2/2010        2.50
1/3/2010        8.25
1/4/2010        10
1/5/2010        6

Exceluser

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."