Average data based on unique change in date, ignoring time

Try this macro, run it after you open the CSV file:

Please Login or Register  to view this content.

Hi Rob, I wouldn't say I am 'so good' at VBA, and yes I use macro recorder ever since I started learning VBA ~3 years ago. I learned most of my stuff through the recorder and the help file. I still use the record often, when I am unsure of certain syntax, like if I were to mess with setting borders to cells now, I would first record it.

First I would say I use pivot table because it is indeed a much easier way to get 'unique' records, in this case the dates. And you wanted average data of the unique records, that's the exact purpose of pivot tables - to get summaries, be it Sum, Count, Average, etc.

Below are some explanation to the code:

Please Login or Register  to view this content.

Will get back to you regarding running it on a different sheet, I don't have time to show the code now.

Actually, it would be good if you can post a sample workbook. By "tab", do you mean worksheet or workbook? Because I think CSV files can only contain a single worksheet.

If you meant worksheet, you want the macro the create a new worksheet and display the output?

As for making chart to 'update' source data dynamically, I usually set it through VBA code, but that's just my style. It can also be done by using named ranges. There is a good trick shown here by SoulPrisoner regarding dynamic named ranges, maybe you can look into it too.

Millz,

wow, thank you very much for the comments in the code - that really helps others to gain an understanding of what each line does!! I now can understand what is happening, and improve my skills from there, as well as explain to others who will be using this what is going on!!

As far as the dynamic ranges - I *think* I figured it out by just using COUNT(start cell: end cell). For some reason though, even Microsoft's help guide was instructing to use a combination of "COUNT" and "OFFSET" (see here: http://support.microsoft.com/kb/830287 ). I couldn't get that to work for me, nor could I understand why someone would need to use OFFSET with COUNT when COUNT seems to work fine.

As far as "tabs" - yes, I mean different tabs within 1 workbook (I guess "tabs" are also called "worksheets"). For sake of argument, let's call a WHOLE spreadsheet (.xls file) a "workbook" while within ONE workbook, there can be multiple "tabs" which we'll call "worksheets." Also, while the actual data is too long & boring to post here (not to mention the company probably doesn't want us posting their raw data here), suffice it to say that the data in it's "raw" CSV format looks very much like the example I presented in my 1st post - it's just ONE file with a 3-4 columns & a BUNCH of rows (which can vary from file to file).

Anyway, the user pulls that data into Excel from the CSV file & puts it into the 1st worksheet (or "tab") - let's call this worksheet "raw_data." Since we would like to keep that data "pure" (meaning, not do any manipulations to it on THAT worksheet, or tab), we would want another worksheet (or tab) to be the "output" worksheet.

The "output" worksheet is where I would want the macro to run from, and where I would like the output to be pasted to, but it would need to be smart enough to go over to the "raw_data" worksheet, perform it's commands, then paste the parsed & manipulated data back onto the "output" worksheet (or tab).

Hopefully this makes sense??

Anyway, thank you for all you have done thus far!!

Sorry was kinda busy.

Yes it makes sense, hope this is what you want. Run it right after you open the CSV file. A new worksheet will be added.

Please Login or Register  to view this content.

Originally Posted by millz

Sorry was kinda busy.

Yes it makes sense, hope this is what you want. Run it right after you open the CSV file. A new worksheet will be added.
<SNIP CODE>

Millz - yes, this works perfectly!! Thank you very much!! I will now close this thread as "SOLVED" and add reps to your account!! Again, THANK YOU!!