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.
Bookmarks