Developed an Excel macro using VBA and MS Access for price reconciliation. The purpose of this macro was to select a mass product output sheet from Retail Express with prices and compare it with a supplier price sheet for products. The macro performed automatic matching where products were matched using their supplier SKU or manufacturer SKU. The macro had multiple steps to completely sanitize the data in order to perform matching and update prices in Retail Express.

Client: International Salon Supplies

Role: Full-stack Developer

Tech Stack

• Excel VBA
• MS Access
• Retail Express

Responsibilities:

• Macro Development: Designed and implemented a comprehensive Excel macro using VBA to automate the price reconciliation process between Retail Express and supplier price sheets. This involved writing complex VBA code to handle data extraction, transformation, and loading, ensuring seamless integration with the existing Retail Express system.
• Data Integration: Integrated MS Access to manage and process large datasets, ensuring efficient data handling and storage. This integration allowed for the consolidation of multiple data sources, enabling the macro to process and analyze data more efficiently and accurately.
• Data Sanitization: Developed multiple data sanitization steps within the macro to prepare the data for accurate matching and reconciliation. This included removing duplicates, standardizing data formats, and validating data integrity, which were crucial for ensuring the reliability of the reconciliation process.
• Automation of Matching Process: Automated the matching process using supplier SKU or manufacturer SKU to compare Retail Express output sheets with supplier price sheets. This automation significantly reduced manual effort and minimized errors, ensuring that prices were accurately matched and updated.

Features

• Data Selection: The macro allowed users to select a mass product output sheet from Retail Express and a supplier price sheet for comparison. This feature provided a user-friendly interface for selecting and loading data files, making the reconciliation process more accessible and efficient.
• Automatic Matching: Performed automatic matching through multiple steps to ensure accurate reconciliation:

1. Exact Match: Supplier SKU from Retail Express matched barcode from the supplier sheet or supplier SKU2 matched supplier code from the supplier sheet. This step ensured that the most straightforward matches were identified and processed first, simplifying subsequent matching steps.
2. Alternative Match: Supplier SKU from Retail Express matched supplier code or supplier SKU2 matched barcode. This step provided an alternative matching method to capture records that might not have been matched in the first step, enhancing the overall accuracy of the reconciliation process.
3. Partial Match: Either supplier SKU matched barcode or supplier SKU matched supplier code. This step allowed for partial matches, capturing records that had some degree of similarity but did not meet the criteria for exact or alternative matches, thus broadening the scope of the reconciliation.
4. Manual Match: Provided a manual matching interface for records without direct matching points, allowing users to match manually based on certain conditions. This feature ensured that even the most complex and ambiguous records could be reconciled, providing flexibility and control to the users.

• Data Sanitization: Included comprehensive data sanitization steps to clean and prepare data for accurate matching. This involved multiple stages of data cleaning, including removing invalid characters, standardizing date formats, and ensuring consistency in product codes, which were essential for reliable matching.

Challenges

• Data Inconsistencies: Handling inconsistencies and discrepancies between Retail Express and supplier data, requiring robust data cleaning and validation. Addressing these inconsistencies was crucial for ensuring the accuracy and reliability of the reconciliation process, as even minor discrepancies could lead to significant errors.
• Complex Matching Logic: Developing complex matching logic to cover various scenarios and ensure accurate reconciliation. This involved creating and testing multiple matching algorithms to capture all possible matches, while balancing performance and accuracy.
• Performance Optimization: Optimizing the macro to handle large datasets efficiently without compromising performance. This challenge required careful consideration of memory usage, processing speed, and resource management to ensure that the macro could process large volumes of data quickly and accurately.
• User-friendly Interface: Creating an intuitive and user-friendly interface for manual matching and data entry. Ensuring that the interface was easy to use and navigate was essential for reducing user errors and improving overall efficiency, particularly for users who were not familiar with Excel VBA.

Outcomes

• Improved Accuracy: Enhanced the accuracy of price reconciliation by automating the matching process and reducing manual errors. This resulted in more reliable pricing data, which in turn improved inventory management and financial reporting.
• Increased Efficiency: Streamlined the price update process, saving significant time and effort for the users. By automating repetitive tasks and reducing the need for manual intervention, the macro enabled users to focus on higher-value activities.
• Scalable Solution: Provided a scalable solution that could handle large datasets and be adapted for future reconciliation needs. This scalability ensured that the solution could grow with the business, accommodating increasing data volumes and complexity.
• Enhanced Data Management: Improved data management and processing through the integration of MS Access, ensuring efficient handling of large datasets. This integration allowed for better organization, storage, and retrieval of data, supporting more effective decision-making and analysis.

Images

Demo: Loom Recording