Standalone application to manipulate Excel data

Outcome result. A standalone application requiring input of a few variables influencing our price list. A .csv file/s to be imported into our accounting application (Sage Pastel Partner 14) after prices have been altered from a text file exported from this application. 1. Create Excel file from exported Pastel text file (Attached, ExportedInventoryItems) using comma delimited data import with headings, making sure Inventory Code (Column A) & Preferred Supplier Inventory Code (Column Y) are imported as Text to create an Excel file named [login to view URL] 2. Data input by operator to [login to view URL] 3. Using Excel [login to view URL], [login to view URL] & [login to view URL], 4. Create a new [login to view URL] file with exactly same columns, headings and format as [login to view URL] 5. Row by Row in [login to view URL] o Read Item Code (Column A) in [login to view URL] find exact match in Inventory Code (Column A) in [login to view URL]  If Found • Populate Inventory Code (Column A) with Item Code (Column A), • Inventory Description (Column B) with Item Name (Column B), • Exclusive Selling Price 1 (Column C) with Value from Formula 1, • Last Purchase Amount (Column M) with Value from Formula 2, • Populate rest of this row in [login to view URL] identical to columns in [login to view URL], making sure Group (Column AB), Store (Column AJ) and Inventory Category (Column AK) are in the custom format of 000  If not Found • then create [login to view URL] file with columns identical to [login to view URL] • Populate Inventory Code (column A) with Item Code (Column A), • Inventory Description (Column B) with Item Name (Column B), • Exclusive Selling Price 1 (Column C) with Value from Formula 1, using the factor from Group 001 as default • Last Purchase Amount (Column M) with Value from Formula 2, • Populate rest of row in [login to view URL] identical to columns in [login to view URL], • except Group (Column AB), Store (Column AJ) and Inventory Category (Column AK) to be populated with 001, • making sure Group (Column AB), Store (Column AJ) and Inventory Category (Column AK) are in the custom format of 000 • If Group (Column AB), is altered by the operator to another group as per [login to view URL] then the Exclusive Selling Price 1 (Column C) should be changed as per Formula 1 and the Inventory Category (Column AK) must always match Group (Column AB) exactly. Formula 1 {Exclusive Selling Price 1: (Column C)} = Roundup (with no decimals and to nearest 10) ({[login to view URL]: Standard List GBP List (Column C)} ) * ( {[login to view URL]: Factor (Column C)} ) * ( {[login to view URL]: Exchange Rate (Column D)} ) If the [Markup %] of {Exclusive Selling Price 1 (Column C)} < 33% from the [Markup %] formula {Exclusive Selling Price 1 = ( Last Purchase Amount (Column M) ) + ( {[login to view URL]: Minimum Markup% (Column F)} ) * ( {Last Purchase Amount (Column M)} ) then,  Exclusive Selling Price 1 = ( {Last Purchase Amount (Column M)} ) + ( (33% * {Last Purchase Amount (Column M)}) ) Formula 2 {Last Purchase Price (Column M)} = (Round Up ({[login to view URL]: Exchange Rate (Column D)} + ({[login to view URL]: Courier% (Column E)) * ({[login to view URL]: Exchange Rate (Column D)}) * ({[login to view URL]: Distributor GBP (Column D)})