Excel 2007 : Inventory calculation

**lifeisboost** · 09-05-2010, 03:44 AM

hello everyone i have been trying to figure this one out for hours. i hope it can be done as it will be greatly beneficial. I have an excel spreadsheet for inventory. Column A has the unique part numbers, column b has the part numbers i gather from a hand scanner and column C has the quantity i have in stock.

Basically i want to scan my bar codes and have them entered into column b1-b500. I need a formula that can count how many times the bar codes in A1-A500 are matched in B1-B500 and then add it to the quantity which is column c so if i have the master bar code in a of 777 listed in A1 but i scanned the bar code 777 in the b column 9 times column c1 would be 9.

hopefully this is possible, i have tried countif and match to no avail.

**DonkeyOte** · 09-05-2010, 04:00 AM

Originally Posted by lifeisboost

Basically i want to scan my bar codes and have them entered into column b1-b500. I need a formula that can count how many times the bar codes in A1-A500 are matched in B1-B500 and then add it to the quantity which is column c

If you want Col C to become Col C + Count of Part# in Col B then you will need to use VBA - Col C can not contain both constant (existing stock) and formula ("new" stock) simultaneously

If C should just be count of Col A in Col B (ie "new" stock only) then

C4: =COUNTIF(B:B,A4)
copied down

**lifeisboost** · 09-05-2010, 04:18 AM

oh wow thanks, i was so close from what i had i greatly appreciate it. One last question, what if my main inventory sheet is called Master same column A holds the master part numbers, but i use another sheet to scan called rcved and column A is where my bar codes are scanned into but the qty remains on my master sheet.

so we are using master sheet column a and column C but we need to compare sheet rcved column a to master a then update qty column c on the master

**DonkeyOte** · 09-05-2010, 04:46 AM

Originally Posted by lifeisboost

we are using master sheet column a and column C but we need to compare sheet rcved column a to master a then update qty column c on the master

I'm not sure if we're ignoring the (possible) issue of circular referencing* but:

Please Login or Register  to view this content.

* circular given: existing = existing + new

**lifeisboost** · 09-05-2010, 03:09 PM

many thanks everything works perfectly. now one last possibility, instead of having excel count how many times it finds the product match in the master A column and the rcved A column. can we try the scenario below

on sheet rcved A i scan 1 bar code, then in rcved column B i enter how many i have. C on the master sheet then matches rcved A and then takes the numerical values from rcved B and enters it in master c for the match on master A?