Excel VBA Automation for Multi-Source CSV Imports
Budget: £20 – £250 GBP
Description: We are looking for an experienced Excel VBA/macro developer to build an automated data import system for a master spreadsheet. This system will handle CSV imports from multiple sources, automate data matching, and perform calculations based on the data.
Project Overview: We previously utilized separate macros to import data into spreadsheets, but now require an integrated solution that consolidates the functionality of two existing macros, while adding the ability to import data from two additional CSV reports. The project involves importing data related to sales quantities, stock deliveries, and stock counts into a master sheet, using buttons to initiate the process and applying specific data validation rules to prevent errors.
Key Responsibilities:
Store1 Imports:
Create a button to import weekly CSV sales reports.
Match store names and sales quantities from the CSV with the master spreadsheet based on store numbers.
Correctly import sales data into corresponding rows and columns based on the "Week Commencing" date.
Store2 Imports:
Automate the process to import weekly sales data.
Extract store numbers from CSV reports, match them to the master spreadsheet, and import sales quantities based on week start dates.
Stock Count Imports:
Import stock count reports by matching store information.
Calculate the difference between live stock and stock count values, and display the result in the appropriate columns.
Handle multiple stock deliveries during the same week by summing quantities.
Stock Delivery Imports:
Import daily stock delivery reports and sum delivery quantities for each store.
Match delivery points with the master sheet, calculate total units delivered, and import these totals into the corresponding week columns.
Validation and Data Integrity:
Implement validation rules to ensure the same report isn’t imported multiple times.
Maintain a log file to track all imports by type and date.
Prevent stock delivery imports from being processed until the stock count is completed.
Additional Requirements:
Ensure the system processes multiple deliveries in the same week without overwriting previous data.
Create a user-friendly interface, where users can easily select CSV files and trigger the correct import processes.
Project Overview: We previously utilized separate macros to import data into spreadsheets, but now require an integrated solution that consolidates the functionality of two existing macros, while adding the ability to import data from two additional CSV reports. The project involves importing data related to sales quantities, stock deliveries, and stock counts into a master sheet, using buttons to initiate the process and applying specific data validation rules to prevent errors.
Key Responsibilities:
Store1 Imports:
Create a button to import weekly CSV sales reports.
Match store names and sales quantities from the CSV with the master spreadsheet based on store numbers.
Correctly import sales data into corresponding rows and columns based on the "Week Commencing" date.
Store2 Imports:
Automate the process to import weekly sales data.
Extract store numbers from CSV reports, match them to the master spreadsheet, and import sales quantities based on week start dates.
Stock Count Imports:
Import stock count reports by matching store information.
Calculate the difference between live stock and stock count values, and display the result in the appropriate columns.
Handle multiple stock deliveries during the same week by summing quantities.
Stock Delivery Imports:
Import daily stock delivery reports and sum delivery quantities for each store.
Match delivery points with the master sheet, calculate total units delivered, and import these totals into the corresponding week columns.
Validation and Data Integrity:
Implement validation rules to ensure the same report isn’t imported multiple times.
Maintain a log file to track all imports by type and date.
Prevent stock delivery imports from being processed until the stock count is completed.
Additional Requirements:
Ensure the system processes multiple deliveries in the same week without overwriting previous data.
Create a user-friendly interface, where users can easily select CSV files and trigger the correct import processes.