rbrookov wrote:

Hi,

I have a question that is probably a combination of VBA & Excel
formulas, so if I put this in the wrong location, please let me know &
I'll move it.

Anyway, I have a CSV data file with 3 columns:

Column 1 Column 2 Column 3
(Text) Date (in the following format)
Data (floating point)
a 9/19/2013 12:02:00 AM 6.58667
b 9/19/2013 12:03:00 AM 6.44361
c 9/19/2013 12:04:00 AM 6.32134
d 9/19/2013 12:05:00 AM 6.54321
e 9/20/2013 7:26:00 PM 10.11111
f 9/20/2013 7:27:00 PM 10.23345
g 9/20/2013 7:28:00 PM 10.54321
h 9/20/2013 7:29:00 PM 10.67890
i 9/21/2013 11:58:00 PM 3.12345
j 9/21/2013 11:59:00 AM 3.54321
k 9/21/2013 12:00:00 PM 4.11111
l 9/21/2013 12:01:00 PM 4.54321


The goal is to take an average of the Data in Column 3 for each instance
when the Date in Column 2 changes, ignoring the time.

So, in the example above, the result would be 2 more columns, each with
the following data:

Column 4 Column 5
(Unique Dates) (Average of each Date)
9/19/2013 6.4737075
9/20/2013 10.3916675
9/21/2013 3.830245

If this can be automated such that, when the CSV file is pulled into
Excel, a macro can be run to produce Columns 4 & 5, that would be the
most ideal solution.

If not, any assistance would be appreciated.

Thanks in advance,
Rob

P.S. sorry that the formatting isn't coming out correctly - please let
me know what I'm doing wrong & I'll correct it.





regular pivot table where dates go to rows and column3 as value with
average as agregating function ?