Advice on Approach: Collection, Array or Scripting Dictionary?

[BryanBSuperfly]

Hello,

I'm fairly new to VBA, but am becoming more proficient each day. I wanted some advice on the best approach to collect, aggregate and print out data from this sample workbook. (if you would prefer me to post the workbook another way- please advise. No macros or anything in this workbook). These sheets appear as they do for aesthetic reasons, and I would like to retain the current formatting.

What I would like advice on, is which would be the best method; using a collection, array or scripting dictionary. I've done some preliminary research and have come to realize the scripting dictionary is the most flexible and full featured, but also probably the most challenging to learn.

If you review the sample workbook, basically what I want to do is import the data, aggregate into a specific format, and print out to another sheet. I'm attempting to capture and report on: Employee Name, Date, Type & Scheduled Hours.

Which of these would be the most appropriate method (or is there another method I should research)?

I don't want the code given to me; I want to code this myself. But, I would greatly appreciate any advice on which method I should devote my time to.

Thank you for any and all input on this matter!

Regards,
Bryan

 

[RickXL]

Hi,

I am not sure that I would favour any of those approaches.

Basically, you have a report and not a database. My first step would be to re-create the database from the report.

I don't know how big the organisation is but I once had to reconcile three files which were to be matched using people's names. This was OK until I found out that the largest file had 12 people all called David Smith with no middle name. I forget how many David Smiths there were altogether. So I would think seriously about adding Personnel IDs to the list of fields.

A worksheet used as a database table might look like this:

Excel 2013
	A	B	C	D	E	F
1	Pers ID	Name	Start	Finish	Type	Time
2	1	Emp #1	04/01/2016 08:00	04/01/2016 16:30	Reg	08:00
3	1	Emp #1	05/01/2016 08:00	05/01/2015 16:30	Reg	08:00
4	2	Emp #2	04/01/2016 08:00	04/01/2016 16:30	Reg	08:00
5	2	Emp #2	05/01/2016 08:00	05/01/2015 16:30	Reg	08:00
Sheet1

You should be able to do that using VBA and some For/Next loops.

After that, look at PivotTables for displaying the data views that you need.