This small project requires development of an excel based program to generate a CSV file which can be used to import data in to an excel system.
Each week an excel spreadsheet is received which includes sales data from a website. In order to import this data to the accounting system, the data must be formatted to a required template.
The attached spreadsheet consists of four sheets:
1. 'download file' - this is a sample of data downloaded each week from the website. Each line consists of a single transaction, however, may include purchasing of numerous products. 3 key fields are captured for each product purchase, including product code/sku, price and quantity. These fields are listed horizontally for each product purchased. Importantly the data must be manipulated such that each item purchased is captured on a single line - further explanation below.
2. 'Final Format Explanation' - The final format refers to the structure of the csv file which must be created by the program. This tab details the structure of the output file and variable requirements. It also references where the data can be found from within the download file.
An order will consist of numerous products, freight, and potentially a discount or coupon. Each of these components must be captured as a separate line item.
Additional notes are provided in the 'Final Format Explanation' to explain the program requirements.
3. 'Final Format' - this page is an exact display of how the data should appear in the output csv file. As the Download File tab has sample data we have manually prepared the correct output for the first 4 lines/orders
4. 'Product Barcodes' - Each component of an order, including each individual product in the order, has a barcode. However the download file provides only a product code. A key requirement will be to look up a product code's corresponding barcode from a large table. This table data is found on the 'Product Barcodes' tab. This will not be a straight look up as the product code in the download file is a combination of data found in the Product Barcodes tab. It will be the developer's task to establish a solution to ensure accuracy of the barcode look up which is one of the most important steps.
The way we envisage the program working is as follows:
1. Operator downloads the Download File data from the website
2. Operator opens Excel Program
3. On opening the program the operator is prompted to select the location of the Download file.
4. The program runs and creates a .csv which includes the manipulated data in correct format, saved to the desktop or an agreed location
Please refer carefully to the notes on the 'Final Format Explanation' tab which includes all required parameters. The key area of this project will be creating an 100% accurate, reliable and error free barcode look up.
In your application please provide details on your experience with macros and visual basic coding within excel, and the estimated time to complete this project.
We require a talented developer with excellent communication skills/
Should you have any further queries, please let me know.
Looking forward to hearing from you.
Skills: visual-basic, import