looking up previous value in column

[mfleuette]

Need some creative insight -

Simple checkbook application:
ColA = Amount
ColB = Account
ColC = Balance (computed field = previous balance - current amount)

For a row curr (curr>1), I want to:
1.look up the previous occurrence of the account in B(curr) (i.e. where B(curr) = B(prev) where prev=Max(1 --> n-1)),
2. get the previous balance from C(prev),
3. subtract the current amount A(curr) from C(prev), and
4. store it in the current balance C(curr)

The interesting part (I think) is finding the PREVIOUS occurrence of the account in ColB, not the FIRST occurrence (e.g. by using VLOOKUP)

Anyone have any thoughts on how to do this?

 

[Aladin Akyurek]

A 10-row sample along with the desired result would help.

 

[bigbob]

Hi
If you separate each account to a different sheet then use column B for income and column C for expenditure then you can do running totals for each colum and take the difference as your current balance
Not an answer to your question just redefining the problem
HTH
Bob

 

[mfleuette]

OK, in re a sample:

DESC AMT ACCT BAL
A B C D
CASH 10.00 <-- orig balance
CHECK 100.00 <-- orig balance
apple .30 CASH 9.70 <-- computed by subtracting .30 from 10.00
dinner 10.00 CHECK 90.00 <-- computed by subtracting 10 from 100
soda 1.00 CASH 8.70 <-- computed by subtracting 1 from 9.70


Maybe a simpler question would be how do you find the last occurrence of an item in a range?

 

[Aladin Akyurek]

OK, in re a sample:

DESC AMT ACCT BAL
A B C D
CASH 10.00<-- orig balance
CHECK 100.00<-- orig balance
apple .30 CASH 9.70<-- computed by subtracting .30 from 10.00
dinner 10.00 CHECK 90.00<-- computed by subtracting 10 from 100
soda 1.00 CASH 8.70<-- computed by subtracting 1 from 9.70


Maybe a simpler question would be how do you find the last occurrence of an item in a range?

Book2
	A	B	C	D
1	DESC	AMT	ACCT	BAL
2			CASH	10
3			CHECK	100
4	apple	0.3	CASH	9.7
5	dinner	10	CHECK	90
6	soda	1	CASH	8.7
7
Sheet1

D4, copied down:

=LOOKUP(2,1/($C$2:C3=C4),$D$2:D3)-B4

 

[mfleuette]

Brilliant! Many thanks -