Project Detail

Description

Requirements:
I need Excel to update a manually downloaded Tab-Delimited file from my website online store, with my supplier’s daily updated product inventory file. My supplier inventory and price might change and I need this information to updated my store file. The updated price is based on the supplier cost price. Therefore I need the ability to easily adjust my pricing scheme. For example, do not hard code the percentage increase. I need to be able to go to a separate Excel sheet (or form) to adjust the pricing variables for tiered pricing (see example)
If the cost price is $1 - $20, sell for 20% above the supplier cost price
If the cost price is $21 - $100, sell for 18% above the supplier cost price
If the cost price is $101 - $500, sell for 16% above the supplier cost price, etc.

Ideally I would like to be able to adjust both the tiered level and percentage increase.

There is a catch….some items must be sold at a ‘fixed’ selling price call MAP (Manufacturer Acceptable Price). This price is in the supplier file in a column (MAP) and must over-ride my price increase.

I have included sample files for review.

Background – manual tasks do not need to be done by Excel:
1) WEBSITE FILE: I manually download a Tab-Delimited file from my website which has all my online store products details such as name, description, inventory, price, etc. (see example file called EPA_website). The column headings and format MUST NOT CHANGE.
2) WEBSITE FILE: There is a “v_products_id” column which my website uses as the unique identifier. The common unique identifier to use may have to be the ‘v_products_model’ column. It is in my website and the supplier files.
3) SUPPLIER FILE: I manually down load a text file from different suppliers. NOTE the text file from the suppliers is a text file however the columns are in a different order than the file downloaded from my website. (see example supplier files from 2 different suppliers supplier1.txt, supplier2.txt.
4) I need the supplier files to do a few things:
a. Find the existing products (from my my website file i.e. EPA_website) and update the inventory with the supplier new inventory count
b. Find existing products on my website and update the price if there is a change based on tiered pricing.
c. If MAP pricing is in place the MAP price must over-ride my pricing scheme (see tiered pricing example above)
d. If a product exists in my website file BUT the supplier file does not contain the product, I need the value “delete” (no “’s) to be added to the column “v_action”.
e. If a product exists in my supplier file but not my website file, I need to add the product(s) to the website file to be uploaded. If it is a new product the value “0” is required in the v_products_id column. Also, all the other cells in each column for the product should be filled with the appropriate supplier information (refer to the supplier file).

Using the attached files as the example...the 'EPA_website' file must be updated with the 'supplier1' and 'supplier2' files.

Once my original product file (from my website is updated) I manually upload it back to my website.



Additional files submitted:
EPA_website1.txt
supplier2.txt
supplier1.txt