Excel - Create formula(s) to Update master inventory wrkbk from new updated inventory wrkbk
- or -
Post a project like this4494
$200
- Posted:
- Proposals: 1
- Remote
- #109175
- Awarded
Description
Experience Level: Expert
Bid Request
I have set up a website and is available for viewing.
My relevant background: I am A+ trained and have several years of Windows experience, but have only very basic, self acquired Excel skills and experience. I know only the most basic principles of formulas, and have nowhere near what it takes to produce formulas requiring any advanced degree of skill or knowledge.
Overview: I use Excel 2010, but in 97-2003 compatibility mode since downloads from my distributor is in the 2003 format and my ecommerce program only recognizes that Excel format.
I have a Master workbook with Master sheet. Row 1 = (A)Item#-text, (B)ProductName-text, (C)Units-number, (D)Weight-number, (E)WhlSale-number, formula: =F-(F*55%), (F)Retail-number, (G)Profit-number, formula: =F-E.
I download separately from my distributor, a short weekly update and/or a current total inventory database in Excel 2003.
Currently, I immediately rename (while saving) the downloaded workbook(s) to either AGInventory or AGWklyUpdate respectively. This makes for continuity in formula building (as I understand it) and would continue to do so, unless you instructed me otherwise.
Project: Currently I manually change downloaded header info to match my master’s header info and make sure each columns format matches the master sheets columns format. All columns after (E)retail are irrelevant to my needs on this project and should be disregarded/deleted.
JOB 1: Maybe there is a way to automate the above downloaded workbook/sheet header info and columns change task?
JOB 2: For AGWklyUpdate: Create a means to compare Master Sheet [all rows] (A)Item#, with AGWklyUpdate (A)Item# and if match found, then copy data from AGWklyUpdate (C)Units, (D)Weight, (F)Retail to the same columns in the Master. In other words, update the matched (A)Item# data from the AGWklyUpdate/sheet numbers to the Master workbook/sheet numbers. Important: Existing data in Master column (B)ProductName, must not be changed or updated.
If no (A)Item# match is found on AGWklyUpdate/sheet, then the entire unmatched row(s) of AGWklyUpdate/sheet are to be copied, turned (light) green for easy identification and placed immediately below the existing rows in the Master workbook/sheet.
I would like the row of the discontinued items from the AGWklyUpdate to be highlighted in (bright) orange, for easy identification on the Master, as these must be verified and removed manually from both the Master and the server.
JOB 3: For AGInventory: Create a means to compare Master Sheet [all rows] (A)Item#, with AGInventory/sheet (A)Item# and when match found, copy data from AGInventory (C)Units, (D)Weight, (F)Retail to the same columns in the Master. In other words, update the matched (A)Item# data from the AGInventory/sheet numbers to the Master workbook/sheet numbers. Existing data in Master column (B)ProductName, must not be changed or updated.
If no (A)Item# match is found on AGInventory/sheet, then the entire unmatched row(s) of AGInventory /sheet are to be turned (light) green for easy identification and placed below the existing rows in the Master workbook/sheet.
Additionally, color (light) red for easy identification, any rows on the Master workbook/sheet not found on the AGInventory/sheet.
Job 4: Audit for any additional automation your advanced skills would identify, which would make things simpler for updating the Master from AGInventory/AGWklyUpdate. If you know a (more appropriate than my above ideas) means of accomplishing my needed requirements, then implement them, keeping in mind that it will be necessary for me (with my reduced knowledge/skill level) to similarly implement them for additional ongoing/future distributors workbooks.
I will include the Master workbook and both the (unaltered from how originally received) AGWklyUpdate and AGInventory, so you can use it to better calculate your bid and to make sure your solutions do the job.
I require a copy of each of the modified, properly functioning workbooks to be returned to me and I would need separate copies of the formulas, macros, or whatever you conjure up, and instructions/education/ follow up technical support for at least 30 days on how it works and how to apply them myself, so I could implement them myself to ongoing and future projects.
As I am on a Social Security budget, I will not be financially prepared to proceed with this project until after the 1st of the year, and depending on the size of your fee, perhaps not for 1 to 2 months into the New Year – rest assured, I will do no unnecessary butt dragging, as I do wish to clear the obstacles in getting my store active and doing business as soon as is manageable - this is one of the critical projects required to do so.
If you have any questions in developing your bid, please feel free to contact me.
Thank you,
David (Doc) Rake
I have set up a website and is available for viewing.
My relevant background: I am A+ trained and have several years of Windows experience, but have only very basic, self acquired Excel skills and experience. I know only the most basic principles of formulas, and have nowhere near what it takes to produce formulas requiring any advanced degree of skill or knowledge.
Overview: I use Excel 2010, but in 97-2003 compatibility mode since downloads from my distributor is in the 2003 format and my ecommerce program only recognizes that Excel format.
I have a Master workbook with Master sheet. Row 1 = (A)Item#-text, (B)ProductName-text, (C)Units-number, (D)Weight-number, (E)WhlSale-number, formula: =F-(F*55%), (F)Retail-number, (G)Profit-number, formula: =F-E.
I download separately from my distributor, a short weekly update and/or a current total inventory database in Excel 2003.
Currently, I immediately rename (while saving) the downloaded workbook(s) to either AGInventory or AGWklyUpdate respectively. This makes for continuity in formula building (as I understand it) and would continue to do so, unless you instructed me otherwise.
Project: Currently I manually change downloaded header info to match my master’s header info and make sure each columns format matches the master sheets columns format. All columns after (E)retail are irrelevant to my needs on this project and should be disregarded/deleted.
JOB 1: Maybe there is a way to automate the above downloaded workbook/sheet header info and columns change task?
JOB 2: For AGWklyUpdate: Create a means to compare Master Sheet [all rows] (A)Item#, with AGWklyUpdate (A)Item# and if match found, then copy data from AGWklyUpdate (C)Units, (D)Weight, (F)Retail to the same columns in the Master. In other words, update the matched (A)Item# data from the AGWklyUpdate/sheet numbers to the Master workbook/sheet numbers. Important: Existing data in Master column (B)ProductName, must not be changed or updated.
If no (A)Item# match is found on AGWklyUpdate/sheet, then the entire unmatched row(s) of AGWklyUpdate/sheet are to be copied, turned (light) green for easy identification and placed immediately below the existing rows in the Master workbook/sheet.
I would like the row of the discontinued items from the AGWklyUpdate to be highlighted in (bright) orange, for easy identification on the Master, as these must be verified and removed manually from both the Master and the server.
JOB 3: For AGInventory: Create a means to compare Master Sheet [all rows] (A)Item#, with AGInventory/sheet (A)Item# and when match found, copy data from AGInventory (C)Units, (D)Weight, (F)Retail to the same columns in the Master. In other words, update the matched (A)Item# data from the AGInventory/sheet numbers to the Master workbook/sheet numbers. Existing data in Master column (B)ProductName, must not be changed or updated.
If no (A)Item# match is found on AGInventory/sheet, then the entire unmatched row(s) of AGInventory /sheet are to be turned (light) green for easy identification and placed below the existing rows in the Master workbook/sheet.
Additionally, color (light) red for easy identification, any rows on the Master workbook/sheet not found on the AGInventory/sheet.
Job 4: Audit for any additional automation your advanced skills would identify, which would make things simpler for updating the Master from AGInventory/AGWklyUpdate. If you know a (more appropriate than my above ideas) means of accomplishing my needed requirements, then implement them, keeping in mind that it will be necessary for me (with my reduced knowledge/skill level) to similarly implement them for additional ongoing/future distributors workbooks.
I will include the Master workbook and both the (unaltered from how originally received) AGWklyUpdate and AGInventory, so you can use it to better calculate your bid and to make sure your solutions do the job.
I require a copy of each of the modified, properly functioning workbooks to be returned to me and I would need separate copies of the formulas, macros, or whatever you conjure up, and instructions/education/ follow up technical support for at least 30 days on how it works and how to apply them myself, so I could implement them myself to ongoing and future projects.
As I am on a Social Security budget, I will not be financially prepared to proceed with this project until after the 1st of the year, and depending on the size of your fee, perhaps not for 1 to 2 months into the New Year – rest assured, I will do no unnecessary butt dragging, as I do wish to clear the obstacles in getting my store active and doing business as soon as is manageable - this is one of the critical projects required to do so.
If you have any questions in developing your bid, please feel free to contact me.
Thank you,
David (Doc) Rake
David R.
0% (0)Projects Completed
1
Freelancers worked with
1
Projects awarded
100%
Last project
24 Feb 2012
United States
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies