Previous Occurences DISCUSSION

[Mustank]

Hey everyone,

I'd love to hear some opinions regarding VALUE OF PREVIOUS OCCURENCES.
Let's take a FRUIT SHOP as an example.

	A	B	C	D	E	F
1	Date	Fruit	Buy	Sell	Previous Stock	Current Stock
2	8/1/13	Apple	50	0	0	50
3	8/1/13	Banana	50	0	0	50
4	8/1/13	Orange	50	0	0	50
5	8/2/13	Apple	0	10	50	40
6	8/3/13	Apple	0	5	40	35
7	8/4/13	Banana	0	10	50	40

We also assume that,

1. Every DATE and FRUIT pair is UNIQUE
2. List are sorted in ASCENDING order

What I'm trying to achieve here is to retrieve value of PREVIOUS STOCK of CURRENT ROW's FRUIT which is CURRENT STOCK of PREVIOUS OCCURENCE of CURRENT ROW's FRUIT.
The formula I'm currently using is,

E5 =LOOKUP(2,1/(B$2:B4=B5),F$2:F4)

Some questions I'd like to ask,

1. Is it possible to replace B$2:B4 and F$2:F4 with a NAMED RANGE?
2. Is there another formula which can perform better when we have thousands of row data?
3. How can we improve the formula so it can be used in cell E2, E3, and E4?

Thank you for reading! I'm looking forward to hear some feedbacks.

Regards,


Mustank

[Ace_XL]

In Row 2

Previous stock
=SUMIF($B$1:B1,B2,$C$1:C1)-SUMIF($B$1:B1,B2,$D$1:D1)

Current stock
=SUMIF($B$1:B2,B2,$C$1:C2)-SUMIF($B$1:B2,B2,$D$1:D2)

Copy down

[Mustank]

@Ace_XL

Wow! I never thought it can be solved that way. Thank you! Is there any chance to substitute range in your formula with NAMED RANGE?

Can you give me an example where formula =LOOKUP(2,1/(B$2:B4=B5),F$2:F4) can be used best?

And don't you think formula =C2-D2+E2 is better for CURRENT STOCK?