Automating Table Data Mining

[student1976]

Greetings Gurus,


I am in the process of creating automation for mining data within a large table containing times, data, and type etc.

Once the table is loaded in excel I want a macro that will do three things :

1: Create a new sheet for each unique type and copy all the data corresponding to each sheet. (with all corresponding columns)

2: Create a new calculation sheet and new tables within that sheet with counts for how many occurrences within the data happen each month (a new year would have a new table)

ex:

2008
Type - January - February - March etc.
Cotton - 5 - 7 - 8

2009
Type - January - February - March etc.
Cotton - 58 - 70 - 4

2008
Type - January - February - March etc.
Fiber - 18 - 700 - 88

2009
Type - January - February - March etc.
Fiber - 8 - 628 - 58
etc.

3: Finally, I would want to gather all the types from the same year and create a new Stats sheet and new table for each year with Total, Mean, mode, median, STDEV, Sparkline (for each type).

ex:

Type  Mean  Mode Median STDEV Sparkline
Cotton  5         5         5             0          
Fiber     10       10      10           0
...

I know this seems like HUGE question, but any tip on where to start or even a basic macro to help me get started would be extremely welcomed.

Many thanks,


Robert

[mikerickson]

If you create a small sample workbook of the kind of data you are starting with and the results you want, it will help you understand some of the details of what you are doing.

Attaching it to a post here will help us understand as well.

[student1976]

If you create a small sample workbook of the kind of data you are starting with and the results you want, it will help you understand some of the details of what you are doing.

Attaching it to a post here will help us understand as well.

Thanks for your reply. I just created a test file. File was found using Google Fusion so it is not law enforcement restricted.

I added a few of the required sheets, but obviously all unique crimes would need to have their own sheet. Also, not sure how to go around using a less complicated way to get the "unique crime type" count per month. Using sum() by hand seems so convoluted!

Any help on automating the whole process would be much appreciated!

Thanks