Macros in excel

Supplier Finance Download Automation Scope: Create Macro to get inputs from two input files stored in a fixed location and manipulate the data as per the below specifications to generate an output file. Requirement: The file location where the two input files will be stored should be changeable by us as we are unable to provide live access to the folder. Macros should be built in the output file using the two files that we will provide as sample. Files: Output file: Invoice Upload Summary.xlsx. Please note the output file provided is a manually worked example that needs to be automated. First Input File: Original Supplier Finance [login to view URL] Second Input File: SAP [login to view URL] Specification: 1. Obtain data from first input file: In Invoice Upload Summary (Output file) import all data from the file Original Supplier Finance [login to view URL] (First input file) and do the following: • Delete first 3 rows + first column (A) • Delete the “Buyer Name” column • The new Column A – Invoice ID – Convert to Number • Add filter • Auto select the filtered “invoice Status” column with “Buyer Approved” • Unmerge columns B + C and G + H and delete blank columns • Invoice Status column – select “Buyer Approved” only • Font Trebuchet MS 10 • Format the dates in both the “Issue Date” & “Due Date” columns from MM/DD/YYYY to DD/MM/YYYY • Format the amount with commas • Add columns “Account No.” + “Variance” as the last two columns 2. Compare selected data from step 1 with second input file: In Invoice Upload Summary (Output file) compare/populate data from SAP File (second input file) as below • Ascertain Account number: In Invoice Upload Summary (Output file) use a VLOOKUP to the SAP File (second input file)to populate the fields by looking up the Invoice ID (first 8 digits only) against the [login to view URL] from the SAP File (second input file) to ascertain the account number. Apply the formula to the entire column. =IF(ISERROR(VLOOKUP(A867,'F:\Debtors\Supplier Finance\[SAP [login to view URL]]Sheet1'!$A$2:$H$261,3,FALSE)),"Cannot find transaction",VLOOKUP(A867,'F:\Debtors\Supplier Finance\[SAP [login to view URL]]Sheet1'!$A$2:$H$261,3,FALSE)) • Ascertain Amount variance: In Invoice Upload Summary (Output file) use a VLOOKUP to the SAP File (second input file) to populate the fields by looking up the Invoice ID (first 8 digits only) against the [login to view URL] from the SAP File (second input file) to calculate the difference between the two amounts. Apply the formula to the entire column. =IF(E871-VLOOKUP(A871,'[SAP [login to view URL]]Sheet1'!$A$2:$H$261,7,FALSE)=0,"",E871-VLOOKUP(A871,'[SAP [login to view URL]]Sheet1'!$A$2:$H$261,7,FALSE)) 3. Create Pivot table: In Invoice Upload Summary (Output file), Create a pivot table (Refer “Final Output Summary” tab ) • Filter – Invoice Status – Buyer Approved Only • Column – Due Date (Ensure that Display format is (DD/MM/YY) • Rows – Currency + Account No. • Value – Sum of Amount (number format 2 decimal places with comma) • Grand Totals by currency only • Tabular Form – Font Trebuchet 10