Learning Material recommendation and/or function recommendations for a VBA newbee but competent exceller

[splreece]

Morning all,

Great site guys. I've used this a lot for previous work but only posted now as the following has baffled me.

I am a competent excel user but am starting out in VBA and am comfy manipulating within code but I have no formal training (yet).

I am tasked with the following and I am hoping you guys can point me in the right direction to the sort of code or functions that I can use (below is a sample of the data being used):

Task and problem
In short my data is sales data and each sale is listed individually (sometimes multiple sales under 1 company), but all sales go through a 6 step process before completion. I need to produce intelligence to show where the lags are in the process and the causes (as the length of time to complete sales has increased rapidly this year). (For example, if stage 1 should take 5 days yet there are 2000 sales that are above the 5 days average, then I need to identify it and figure out if there are reasons why (over time)).

Examples of intelligence needed could include:
- Sales progression (identifying the average journey times of each sales stage)
- Looking at each sales stage against location (i.e. are certain offices less efficient at processing certain stages)
- Are certain sales owners less efficient than others etc..

- What are the trends in the above over time (i.e. is it consistency on a daily/weekly basis or are they blips)

Effectively, I would like the user to be able to choose their reports and show results without altering the core dataset.

I know this is a big ask but any advice or help on the functions I should look into would be greatly appreciated.

Many thanks

Location Office Sale Stage Volume Engine Adapted
London Sunderland Stage 1 2 N
Newcastle Peterbrough Stage 3 1 N
Liverpool Glasgow Stage 1 4 Y

 

[ranman256]

This is a database project. Learn Access.
Its great for storing large datasets and analyzing over time.

 

[RickXL]

Hi,

I do have a degree of sympathy with ranman256's answer. It really depends on where your sales data is coming from.

Will the spreadsheet be the place where the master copy of this sales data is stored or will it be mastered elsewhere and you will have access to a copy. If you can copy some data for reporting then that is a comparatively easy problem but if you need to maintain the master sales data in the spreadsheet then that is much more difficult.

From your sample data it would seem that you have sales offices in at least three cities which implies quite a large organisation. So, what sort of data volumes are involved. I would expect an organisation of that size to already have its sales logged on a computer somewhere.

 

[splreece]

Thanks for the reply Ranman256/RickXL.

I should have mentioned source.

Unfortunately Access is not available and the master/core dataset is restricted, so the data populating my dashboard will come from downloaded/extracted excel datasheets.

Effectively we will be supplied with the same dataset each month via a flat csv file and I need to automate as much as possible to allow the user to see trends, control variables (i.e. using dropdowns/combos) etc..

Volumes are in the tens of thousands over a years period from several offices with 20-30 product types...

 

[splreece]

Will access prove a better reporting tool?