I need help mapping data from a source excel file to a new format. I have attached an excel file with the general formatting and some narrative (top row), but I need to incorporate some error checking and also need to export CSV files that would all be best done via some coding. Here is how I need it to work:
- I place an unformatted (source) excel file (currently this is the same as the “source data” sheet in the workbook) in the same folder as the template excel file. The template workbook contains the code.
- I open the template excel file and enter some information on “global data” sheet. After I manually enter the information, I hit a button that formats the source excel file. Remember the source excel file is contained in the same folder as the template. The formatting is shown and explained in the top row and seen on the “formatted data” sheet.
- After the data gets formatted, I need to create a PO export. There will be up to 5 PO export csv files. Each csv export will be formatted as shown on the “po export” sheet. I need a PO export for each unique value in column M (start ship date) from the source data. The cvs files will be named: Global Data B1 and the value of the ship date (e.g. Global Sweatshirts – 10.7.2020). The data contained in each PO export will the data associated with that ship date.
- After the PO exports are created, I need to export the formatted data as a csv. During the export I need to check the UPC against the UPC database. If the formatted data contains a upc that already exists in the UPC database, then that row needs to be removed from the export. After duplicates are deleted then all of the new UPC codes need to be added to the end of the upc database. I also need to export the UPC data base sheet (with the new data) with todays date to create a backup.
- The template file needs to be maintained though this process as I will run this file on the daily to map data and maintain an updated UPC database.
See attached file for further clarification.