lookup item number with operation

lochem

New Member
Joined
Jun 14, 2010
Messages
28
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jun 14, 2010
Messages
28
hmm.. can you please explain your function? so i can appropriately apply it where necessary

thanks!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
For multiple occurrences try:

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

Forum statistics

Threads
1,136,968
Messages
5,678,866
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top