Coding required based on Excel workbook data as a source
Budget: $750 – $1,500 USD
I have a client who I need to help create a small application from a spreadsheet they are using.
They want to create a master sheet which pulls data from individually named workbooks created by different salespeople. I am attaching an initial draft of the master sheet and an example of a workbook which has source data. Currently there is no uniformity of column structure in the source workbooks i.e. columns have the same header names but are in different columns from one workbook to another. At any time there are approx 75-100 individual workbooks (the naming of the workbooks can be formatted to be the same format) but the names of the workbooks can differ depending on what the customer's name and job name is but you don't know that ahead of time until the job comes in e.g. Customer Name-Job Name-Ship Schedule. Each individial source data workbook could have up to a few hundred rows.
The master sheet will need to check all source workbooks for updates and also bring in new data. The master sheet needs to bring in all the detail (i.e. all the line items) from the individual sheets. They feel they need all the line items in the master so they can the detail if they want to compared to bring in only summary data into the master by category etc. After 6 months we can we start “archiving” or deleting items from master list otherwise the sheet will continue to grow too large.
Initially they just want to create the master sheet with these line items. I think there is way more value add they can create if they go to the effort of an app.
They could potentially use the app to help the warehouse:
1. assess how much space is need in a given period. For example how many pallets of a given Category of items across all jobs will be in the warehouse in a specific time period.
2. use that data to see how much of a shortage or surplus of space they will have based on pallets. They know how much space the warehouse has and they can enter that manually as a field into this application to compare to space needed based on pallets and ship dates.
3. get an alert based on which items are coming up for shipping and which are beyond their ship dates etc.
4. …..
Please also advise if this should be coded in Visual Basic or Python etc?
They want to create a master sheet which pulls data from individually named workbooks created by different salespeople. I am attaching an initial draft of the master sheet and an example of a workbook which has source data. Currently there is no uniformity of column structure in the source workbooks i.e. columns have the same header names but are in different columns from one workbook to another. At any time there are approx 75-100 individual workbooks (the naming of the workbooks can be formatted to be the same format) but the names of the workbooks can differ depending on what the customer's name and job name is but you don't know that ahead of time until the job comes in e.g. Customer Name-Job Name-Ship Schedule. Each individial source data workbook could have up to a few hundred rows.
The master sheet will need to check all source workbooks for updates and also bring in new data. The master sheet needs to bring in all the detail (i.e. all the line items) from the individual sheets. They feel they need all the line items in the master so they can the detail if they want to compared to bring in only summary data into the master by category etc. After 6 months we can we start “archiving” or deleting items from master list otherwise the sheet will continue to grow too large.
Initially they just want to create the master sheet with these line items. I think there is way more value add they can create if they go to the effort of an app.
They could potentially use the app to help the warehouse:
1. assess how much space is need in a given period. For example how many pallets of a given Category of items across all jobs will be in the warehouse in a specific time period.
2. use that data to see how much of a shortage or surplus of space they will have based on pallets. They know how much space the warehouse has and they can enter that manually as a field into this application to compare to space needed based on pallets and ship dates.
3. get an alert based on which items are coming up for shipping and which are beyond their ship dates etc.
4. …..
Please also advise if this should be coded in Visual Basic or Python etc?