+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Inventory calculation

  1. #1
    Registered User
    Join Date
    09-05-2010
    Location
    ar
    MS-Off Ver
    Excel 2003
    Posts
    3

    Inventory calculation

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Inventory calculation

    Quote Originally Posted by lifeisboost View Post
    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

  3. #3
    Registered User
    Join Date
    09-05-2010
    Location
    ar
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Inventory calculation

    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

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Inventory calculation

    Quote 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

  5. #5
    Registered User
    Join Date
    09-05-2010
    Location
    ar
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Inventory calculation

    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?
    Last edited by lifeisboost; 09-05-2010 at 03:14 PM.

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Inventory calculation

    see SUMIF function in XL Help (rather than COUNTIF)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1