+ Reply to Thread
Results 1 to 9 of 9

Need help comparing two inventory worksheets and displaying any differences on a third.

  1. #1
    Registered User
    Join Date
    08-25-2013
    Location
    FLorida, USA
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    4

    Need help comparing two inventory worksheets and displaying any differences on a third.

    We have a sophisticated commercial accounting system but it is light on the inventory side. I have three columns to compare. I thought that would be simple.... "piece of cake" until reality set in. The first worksheet contains a barcode that may contain leading zeros so it is presented as a text file and sorted. The second column contains a description and the third is quantity. I "volunteered" to develop a comparison routine with Excel thinking I could use a series of vlookups but as I delve into the problem I realize the project goes beyond Vlookup.

    The first worksheet, column one contains sorted barcode. Column two, a text description and three contains numerical quantity.
    The second worksheet is not sorted since the physical inventory is conducted with a scanner and in no particular order. The scanned data only contains the barcode (either text or numerical by setting) and numerical quantity. Column one contains barcode. I have to match the barcodes, perform a comparison, and report the difference (if any) however, there are many scans containing identical barcodes from like products that are stored in coolers or dry stock. Like stock numbers have to be consolidated to combine the quantities and present one barcode for comparison against the stock records.

    eg:
    Computer inventory
    010205921178 Crackers 10
    011305925621 Orange Juice 40
    etc....

    Scanned data
    022697936271, 20
    022697936271, 85
    011305925621, 40

    Any help is appreciated.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Need help comparing two inventory worksheets and displaying any differences on a third

    Add a new column to your Computer Inventory Sheet. In it add the following formula

    =Sumif(RangeOfScannedDataBarCode,BarCodeOnComputerInventory,RangeOfDataOnScannedSheet). Copy it down.

    This should allow you to compare the scanned data (physical Inventory) to your perpetual.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Need help comparing two inventory worksheets and displaying any differences on a third

    Try this:
    Please Login or Register  to view this content.
    it creates a pivot table to sum up the quantities in the scanned worksheet, and then do the comparison from there.
    Last edited by millz; 08-26-2013 at 11:51 PM.

  4. #4
    Registered User
    Join Date
    08-25-2013
    Location
    FLorida, USA
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    4

    Re: Need help comparing two inventory worksheets and displaying any differences on a third

    Looks like I need a crash course on PivotTables. Having a few problems with PivotTable naming vs. column names or establishing the Pivot Table but I am finally making headway.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Need help comparing two inventory worksheets and displaying any differences on a third

    Look at the link in my signature block on Pivot Table tips.

  6. #6
    Registered User
    Join Date
    08-25-2013
    Location
    FLorida, USA
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    4

    Re: Need help comparing two inventory worksheets and displaying any differences on a third

    Thanks, I will check the tips. Works well right now until I get to the line "For Each c In wsScan.Range(Cells(2,3), Cells(pivotLast), 3))"

    The routine for the identical barcode count is excellent.

  7. #7
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Need help comparing two inventory worksheets and displaying any differences on a third

    Sorry I forgot to mention that in your scan worksheet you need row 1 to be header names, containing "Barcode" and "Qty". Or you can just change accordingly to what you have, in the code.

    Please Login or Register  to view this content.
    if that wasn't the cause of the error, maybe tell us what was the error message where the code stops

  8. #8
    Registered User
    Join Date
    08-25-2013
    Location
    FLorida, USA
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    4

    Re: Need help comparing two inventory worksheets and displaying any differences on a third

    I found that early on. The hangup was in the Range(Cells2,3). The pivot table is created on Rows 1 and 2 and the routine places the count in column 4 thus Range(Cells3,4) works perfectly.

    I consider this one SOLVED!

    Thank you

  9. #9
    Registered User
    Join Date
    03-28-2016
    Location
    england
    MS-Off Ver
    2016
    Posts
    2

    Re: Need help comparing two inventory worksheets and displaying any differences on a third

    hi

    i am trying to do something similar, where do i copy the above text to, is it a macro activated on a button click?

    thanks

    Andy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Comparing two sheets for differences
    By papasmurfuo9 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-16-2013, 06:43 AM
  2. Comparing two worksheets and identifying differences
    By sarahrose20 in forum Excel General
    Replies: 4
    Last Post: 07-24-2012, 08:26 AM
  3. Comparing differences between two columns
    By MI_Analyst in forum Excel General
    Replies: 1
    Last Post: 11-02-2010, 06:51 AM
  4. Displaying differences between two columns
    By chadder44 in forum Excel General
    Replies: 7
    Last Post: 03-04-2010, 04:01 PM
  5. Calculating & Displaying time differences
    By matt002 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 03-12-2009, 05:35 AM

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