Build a script that will take 3 spreadsheets, perform calculations on them and produce a new spreadsheet
Budget: $250 – $750 USD
Summary
1) I have 2 excel stock lists from two suppliers
2) I have 3 websites. The stock for these 3 websites are in an excel spreadsheet with three worksheets
3) I need the stock on the 3 sites to be updated by the combined stock available from both suppliers
4) This must happen as follows: I upload the two excel stock lists and the the excel spreadsheet with the 3 sites separated on 3 worksheets. The program needs to produce a new spreadsheet where the stock on the three worksheets are updated to show the combined stock available from both suppliers
5) The Unique Identifier for the stock is called SKU (NewSku), and that is what is used to look up the stock counts on all the spreadsheets
Programming requirement:
I need a script that will run on my linux server. Not on a desktop PC. Please indicate what programming language you intend to use. My preference is PHP or similar. If you do not submit your programming language with the bid, it will be ignored. The script does not need a fancy interface. I can upload the input files into a directory and the script can deposit the output file into a directory, if that is easier. If the excel sheets are an issue, we can separate the inout and output sheets into CSV files. Therefore working with CSV files, in stead of excel sheets.
Steps:
Step1: Stocklist 1
1.1) Create new sku (refer to formula in spreadsheet Column O)
1.2) Remove all duplicates
Step 2: Stocklist 2
2.1) Create new sku (sku (refer to formula in spreadsheet Column K)
2.2) Remove all duplicates
Step 3: Stocklist 1 and 2 - combine into Combining Stock Lists spreadsheet
3.1) Combine stocklist 1 and 2, into one stocklist with these columns:
NewSKU, Total stock (add the stock from stocklist 1 and 2 together) - column Y and Z (See spreadsheet: combining stock lists.xls
Some SKUs will be on both stocklists, some skus only on either 1 or 2. The stock for all the skus on all the stock lists must be included on TOTALSTOCK
Step 4: New Stocklist - quantities combined for all three sites
This is the output desired from the program
4.1) Lookup sku on each of the the three worksheets (site 1, 2 and 3) in the website spreadsheet and list the total stock as contained in Combining Stock Lists spreadsheet
4.2) If a sku on the worksheets (1,2 and 3) are not on the TotalStock spreadsheet, the stock must be set to 0 (zero).
Attached files:
I'm including excel sheets to clarify and to demonstrate how this might be done manually on excel. The sheets only show the first 100 rows. Please keep in mind that the original stock lists can have up to 400,000 rows. You have to design a program that can handle large excel files - the biggest ones around 40 MB.
The columns and format of the input files will be the same as these supplied files.
Thank you
1) I have 2 excel stock lists from two suppliers
2) I have 3 websites. The stock for these 3 websites are in an excel spreadsheet with three worksheets
3) I need the stock on the 3 sites to be updated by the combined stock available from both suppliers
4) This must happen as follows: I upload the two excel stock lists and the the excel spreadsheet with the 3 sites separated on 3 worksheets. The program needs to produce a new spreadsheet where the stock on the three worksheets are updated to show the combined stock available from both suppliers
5) The Unique Identifier for the stock is called SKU (NewSku), and that is what is used to look up the stock counts on all the spreadsheets
Programming requirement:
I need a script that will run on my linux server. Not on a desktop PC. Please indicate what programming language you intend to use. My preference is PHP or similar. If you do not submit your programming language with the bid, it will be ignored. The script does not need a fancy interface. I can upload the input files into a directory and the script can deposit the output file into a directory, if that is easier. If the excel sheets are an issue, we can separate the inout and output sheets into CSV files. Therefore working with CSV files, in stead of excel sheets.
Steps:
Step1: Stocklist 1
1.1) Create new sku (refer to formula in spreadsheet Column O)
1.2) Remove all duplicates
Step 2: Stocklist 2
2.1) Create new sku (sku (refer to formula in spreadsheet Column K)
2.2) Remove all duplicates
Step 3: Stocklist 1 and 2 - combine into Combining Stock Lists spreadsheet
3.1) Combine stocklist 1 and 2, into one stocklist with these columns:
NewSKU, Total stock (add the stock from stocklist 1 and 2 together) - column Y and Z (See spreadsheet: combining stock lists.xls
Some SKUs will be on both stocklists, some skus only on either 1 or 2. The stock for all the skus on all the stock lists must be included on TOTALSTOCK
Step 4: New Stocklist - quantities combined for all three sites
This is the output desired from the program
4.1) Lookup sku on each of the the three worksheets (site 1, 2 and 3) in the website spreadsheet and list the total stock as contained in Combining Stock Lists spreadsheet
4.2) If a sku on the worksheets (1,2 and 3) are not on the TotalStock spreadsheet, the stock must be set to 0 (zero).
Attached files:
I'm including excel sheets to clarify and to demonstrate how this might be done manually on excel. The sheets only show the first 100 rows. Please keep in mind that the original stock lists can have up to 400,000 rows. You have to design a program that can handle large excel files - the biggest ones around 40 MB.
The columns and format of the input files will be the same as these supplied files.
Thank you