Is there a way to extract multiple values from an INDEX result ?

[exceluser]

Is there a way to extract multiple values from an INDEX result ?

For example:

Date Fruit Qty. Inventory
1/1/2010 Apples 10 Apples_10
1/2/2010 Apples 0
1/3/2010 Apples 5 Apples_5
1/4/2010 Oranges 3 Oranges_3,Apples_5


On 1/1/2010, the Inventory column should reflect Apples_10.

On 1/2/2010, the Inventory column should reflect nothing.

On 1/3/2010, the Inventory column should reflect Apples_5.

On 1/4/2010, the Inventory column should reflect Oranges_3,Apples_5.

I can create a formula that contains INDEX({"Oranges_3";"Apples_5"}),
but how would you extract the multiple values in the INDEX ?

Or perhaps, another method can be used to result in concatenated
value ?

 

[Pete_UK]

I don't understand what you are trying to do. Does the Qty column
represent items sold or items received? If the former then shouldn't
the 10 be taken away from what you have in stock to begin with? If the
latter then why isn't it Apples_15 instead of Apples_5?

It's a bit too late here to try to guess what you really want to do,
so please explain more clearly and try to give more examples of what
you want to achieve, and if it makes more sense in the morning then
I'll take another look at it.

Pete

 

[exceluser]

I don't understand what you are trying to do. Does the Qty column
represent items sold or items received? If the former then shouldn't
the 10 be taken away from what you have in stock to begin with? If the
latter then why isn't it Apples_15 instead of Apples_5?

It's a bit too late here to try to guess what you really want to do,
so please explain more clearly and try to give more examples of what
you want to achieve, and if it makes more sense in the morning then
I'll take another look at it.

Pete











- Show quoted text -

Pete,

Thanks for replying.

What I'm trying to accomplish is based on querying existing
inventory on a given date.

Corrected version:

Date Fruit Qty. Inventory
1/1/2010 Apples 10 Apples_10
1/2/2010 Apples -10
1/3/2010 Apples 5 Apples_5
1/4/2010 Oranges 3 Oranges_3,Apples_5

On 1/1/2010, the store's entire inventory consisted of 10 apples.

On 1/2/2010, the store's entire inventory consisted of nothing since
the 10 apples were removed from the inventory.

On 1/3/2010, the store's entire inventory consisted of 5 apples.

On 1/4/2010, the store's entire inventory consisted of 3 oranges and 5
apples.

Basically, I'm trying to create a formula that tells us what inventory
we had on a particular day.

 

[Pete_UK]

It seems a strange way of doing things. If you ONLY have Apples and
Oranges in your inventory, then surely for consistency you should
have:

Date Fruit Qty. Inventory
1/1/2010 Apples 10 Apples_10, Oranges_0
1/2/2010 Apples -10 Apples_0, Oranges_0
1/3/2010 Apples 5 Apples_5, Oranges_0
1/4/2010 Oranges 3 Apples_5, Oranges_3

This would be a bit more workable, however, if you have more than two
items in your inventory then that concatenated list will become very
difficult to handle. Perhaps it might be better to have separate
columns next to your data, one for each item that could be on your
inventory.

Hope this helps.

Pete