Thread: Averaging values and replacing rows

Hi, there. I'm trying to figure out a way to process a lot of information (+65,000 rows...oh, dear). Using functions or macros are welcomed. I've attached a dummy file that has more details of what I'm looking for. Also, I'm on a schedule and need to finish this in the very near future! I'm in desperate need of some help, asap.

In a nutshell, this is my problem:

In column A, I have a list of dates and times (in chronological order), where the time is rounded to the nearest minute. This list of dates and times either occur with duplicates or they only appear once. Also, each entry has a corresponding number, listed in column B as "Value."

What I need:

If the date and time only occurs once, then it should be left alone. If the date and time occurs more than once, I do not need each of the values that occurs within that minute, I need the average of all of the values which occurs within that minute.

So, if 2009-9-22 6:45:00 PM appears four times in sequence in column A, and column B lists 0.3, 0.7, 0.4, 1.3 corresponding to 6:45:00 PM then I need the average of those four values (0.675), and I need to either delete the duplicate date and time cells and their corresponding values, or output them as a list without the duplicate date and times and value cells so that I only have a list of singularly-occurring dates and times, and either its corresponding value (if it occurred once) or its average corresponding value (if it occurred more than once).

See the file to get a better idea of what I'm looking for.

And thank you in advance for any help!!

In C2 and copy down,

=IF(A2=A3, "", AVERAGE(INDEX(B:B, MATCH(A2, A:A, 0)):B2))

Then,

o Copy col C

o Paste as values over itself

o AutoFilter on col C for blanks

o Select the data and delete (it will delete only the visible cells)

o Remove the AutoFilter and sort on col A.

If you want to do it via macro, record a macro doing that and we'll help you clean it up.