PDA

View Full Version : Dataset Help



Decision
06-28-2016, 05:11 AM
Hi guys,

I have been working with VBA for a while and although I understand/can read some code (have even followed an online course) I find it hard to make one myself. Especially for the following purpose:

I have a dataset of 649 unique companies (column A). For two different weeks (2 x 6 days; in column B week is specified - i.e. 0 or 1 - and in column C the day is specified: 1 for Monday, 2 for Tuesday and so on), I have recorded accumulated sales data per half hour (column D specifies which half hour 0-29; column E specifies the acc. sales volume up til that half hour). Not every company however has sales volume during each half hour or each day or sometimes even each week.

I would like to produce a vba code which makes line graphs for each company and place these graphs into a new worksheet. In order to ensure the number of graphs is oversee able, I would like to plot both weeks of sales data per company in one graph, each day may be presented after one another (e.g. on the x axis there will be 6 days, within those days showing the half hours in which sales have been produced) thus stretching six days in one line and producing two lines for one company in one graph.

Consequently, I assume there should be a loop for each company (1-649), for each week (0-1) and for each day (1-6), putting all those details on accumulated sales (column E) per day in one graph and then go on the next company.. I have been cracking my brains over this and checking other codes but I don't know where to start unfortunately. Hope someone could help me out!

Thanks a LOT in advance..

offthelip
06-28-2016, 08:56 AM
if you sort your data on four columns, 1st company, 2nd week, third day, fourth half hour, you will then have the data in correct order in column E. So run a macro from top to bottom of your data copying to a new sheet and start a new row when the week changes or the company changes.

SamT
06-28-2016, 06:45 PM
I have a dataset of 649 unique companies (column A).
IMO, you have 649 datasets, each with 720 records, all on one sheet. Personally, I would have 649 sheets, each with one dataset. (Company.)

That also makes the VBA loop much simpler, ie

For each WkSht in Worksheets
Chartsheet.Add Chart
Chart.Name = WkSht.Name
'code for one chart here
Next Wksht.

If at all possible, I would put the week day numbers horizontally in the first Row and the 30Min volumes vertically below them. That gives you the ability to keep a years worth of data in only ~350 columns of 30 Rows or ~800 Rows of 12 Columns

Then I would set the following values on each Company sheet
"A1" = Total sales
"A2" = Sales Week 1
"A3" = Sales Week 2
Date/Day of max sales
Max Sales Volume
Date/Day of Min Sales
Min Sales Volume
anything else you can think of

Just that gives you the ability to have Top Ten and Bottom Ten Rankings/Charts

With a years worth of data, you can chart sales by any time period.