Need a Excel VBA to create and compare a list of stock with location and quantity data, from barcode scanned items in store / warehouse location.
The list will be imported into a stock control program called Unleashed, to update the location & quantity of stock.
The unique field will be the scanned barcode. The user will input a location, and scan the barcodes of the items in that location ( Multiple items, of multiple different types of items can exist at one location). Then repeat the process for another location. The list will be created & updated in excel, and used to Import data to Unleashed.
If stock data is already present in Unleashed, to start the process an export is taken from Unleashed and imported to Excel.
In excel, user will open VBA utility and enter location data in the format of a warehouse code or store code ( see Location examples below ).
Location data entered will be retained and used for subsequent barcodes scanned, until it is changed by the user. Barcodes will be scanned by user, and optionally a quantity entered for each. Excel list will be searched for matching barcode. If not found and quantity data has not been entered, it will be prompted for. The barcode, location and quantity data will be added to the list. If found location data will be updated, quantity data will be overwritten if it has been entered by the user. Quantity data will not be changed if not entered by user. Location data will be updated as follows.
If no existing data is present, the field is populated with location data previously entered by user. If location data is present the data type is compared to that entered. If not found the data is saved. It is prepended to store data if that is present and it is warehouse data. It is appended to warehouse data if that is present, and it is store data. If data is present of the type being entered, the location is updated, without changing data of the other type if that is present. For example, if the location field contains warehouse location data, and warehouse location data has been entered it will be updated, without changing store location if that is present.
Once user completes update of list, it is imported to Unleashed. Overwriting the Unleashed data.
‘Location’ field in excel corresponds to ‘Bin Location’ in Unleashed and can contain two types of data, a warehouse code and a store code. Therefore the combinations for the field are, nothing / warehouse data / store data / warehouse and store data.
Warehouse code format is Ann-Bn-Sn. Store code format is AAA-Gnnn-Sn. If. Both warehouse and store data is present it is recorded in that order with a single space character between.
12 freelancere byder i gennemsnit $348 timen for dette job
I possess very good skills in excel. I know each and every formulas of excel and how to use them. I am better at Formatting the Data and Showing it systematically.