Macro - Date and data analysis....

[petry_37]

Hi, I am analysing a bunch of file data and due to the large volume of data the sorts and filters take ages to run.

Column B contains the size of the files in bytes.
Columns D, E and F contains their last access, date created and last modified dates.
Column H contains the file extension.

I would like to be able to do the following -

Insert a new column after each of those mentioned above.

Change column B to reflect Megabytes as opposed to bytes((B2\1024)\1024).
In the new column C, analyse the data in column B into the following categories - <1MB, 1-10MB, 10-100MB, 100-500MB, 500MB-1GB, >1GB.

In the new columns after D, E and F analyse the date data into categories, just show the years if the dates are in the last 5 years, older than 5 years for those dates between 2002 and 2005 inclusive, older than 10 years for those dates between 1992 and 2001 inclusive, older than 20 years for those dates between 1982 and 1991 inclusive, older than 30 years for those dates earlier than 1981.

Finally do a lookup in the new column afer column H to get the actual name of the file extension(I have a basic list of file extension names).

I realise this is quite a task but any help would be much appreciated! I can manipulate code but writing it from scratch is a bit of a stretch for me....

Many thanks!

 

[Derek Brown]

Are you sure that you want to do this with a macro?
I would have thought that this could be achieved with a formula in one column and a pivot table, perhaps with a calculated field or two.

 

[silkfire]

You should use PHP/MySQL for this. PM me if you need further help.