lookup item number with operation

[lochem]

Hi,
Ive seen similar posts but cant find what i need to do:

i have a database with many fields, "item number" is in column A and "amount in stock" is column N.

item numbers are repeated often in column A and are unsorted.
for each item appearance, i need to deduct -1 into "amount in stock" from the last time any item appeared.
i need the code to perform the following:
For example the result should be:

Item Num Amount in Stock
10802 21
10301 20
10203 17
10301 19
10502 15
10203 16

 

[Andrew Poulsom]

Try:

=IF(COUNTIF(A$1:A2,A2)=1,B2,INDEX(C$1:C1,MATCH(A2,A$1:A1,FALSE))-1)

in C2 copied down.

 

[lochem]

hmm.. can you please explain your function? so i can appropriately apply it where necessary

thanks!

 

[Andrew Poulsom]

If A2 is the fist occurrence of itself in column A, B2 is used, otherwise the first matching value above is used, with 1 deducted.

Actually, i just reread your post. My formula won't work for more than 2 occurences.

 

[Andrew Poulsom]

For multiple occurrences try:

=IF(COUNTIF(A$1:A2,A2)=1,B2,LOOKUP(2,1/(A$1:A1=A2),C$1:C1)-1)

 

[lochem]

thanks, ill give this a go and see how it works out.