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 ?

Witek,

Yes, a pivot table is a way to do it. However, is there a way to do it utilizing a macro, and without creating a pivot table? I know that a macro can be written which produces a pivot table, but can a macro be written such that the result is not in a pivot table? Or is that not possible?

Thank you for your suggestion!!
Rob

Hi,

Am Tue, 8 Oct 2013 20:06:03 +0100 schrieb rbrookov:

Hi, thank you for responding. Yes, that is a technique which works
quite fine in Excel. However, as this task is to be done over & over
again, and on many hundreds, perhaps thousands of rows, each time it is
done, I would think that something a bit more automated might be more
efficient.

then name your data with a dynamic range name and create a pivot table
from this name.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2