Macro to compare two columns and output min/max

**arpit_rao6** · 02-03-2010, 04:22 AM

Here is the data set that I have in 2 Columns :

Column A Column B
1/1/07 0:00 2.05
1/1/07 0:15 2.32
1/1/07 0:30 2.32
1/1/07 0:45 2.32
1/1/07 1:00 2.16

I want to output the min / max value for Column B, with the corresponding value from Column A. If you observe its data collected every 15 mins. I have given a sample data set for 1 hour, but I have the data set for last 3 years. My aim is to come up with minimum and maximum values for each day of the month.

Thanks for your assistance.

Arpit

**DonkeyOte** · 02-03-2010, 04:49 AM

Push your Data into a Pivot Table

Add Column A as Row Label
Add Column B as Data Field twice over - first set to MAX, second set to MIN

Right click on any value in Date Field and opt to Group - Group by Day & Month & Year.

Result will be a matrix of days down the side with associated MAX & MIN values adjacent.

**arpit_rao6** · 02-03-2010, 01:24 PM

Thanks for your reply. I guess I dint ask the right question. Lemme try again.
I have the following data set in 2 columns named (Date and Value). If you see it is data collected every 15 mins. I want to determine what was the peak value in column B, for every hour, and based on that what is the peak value for every day. My data set is actually over 3 years, where I want to find out the peak value for every day, every month and then every year.

Appreciate your input.

Thanks

Date Value
1/1/07 0:00 2.05
1/1/07 0:15 2.32
1/1/07 0:30 2.32
1/1/07 0:45 2.32
1/1/07 1:00 2.16
1/1/07 1:15 2.16
1/1/07 1:30 2.16
1/1/07 1:45 2.16
1/1/07 2:00 2.16
1/1/07 2:15 2.16
1/1/07 2:30 2.02
1/1/07 2:45 2.02
1/1/07 3:00 2.02
1/1/07 3:15 2.14
1/1/07 3:30 1.99
1/1/07 3:45 2.12
1/1/07 4:00 2.12
1/1/07 4:15 2.12
1/1/07 4:30 2.12
1/1/07 4:45 2.23
1/1/07 5:00 2.23
1/1/07 5:15 2.09
1/1/07 5:30 2.09
1/1/07 5:45 2.24
1/1/07 6:00 2.24
1/1/07 6:15 2.40
1/1/07 6:30 2.40
1/1/07 6:45 2.54
1/1/07 7:00 2.44
1/1/07 7:15 2.58
1/1/07 7:30 2.72
1/1/07 7:45 2.72
1/1/07 8:00 2.72
1/1/07 8:15 2.30
1/1/07 8:30 2.30
1/1/07 8:45 2.30
1/1/07 9:00 2.15
1/1/07 9:15 2.16
1/1/07 9:30 1.74
1/1/07 9:45 1.74
1/1/07 10:00 1.74
1/1/07 10:15 1.49
1/1/07 10:30 1.24
1/1/07 10:45 1.24
1/1/07 11:00 1.10
1/1/07 11:15 1.10
1/1/07 11:30 0.94
1/1/07 11:45 0.96
1/1/07 12:00 1.13
1/1/07 12:15 1.28
1/1/07 12:30 1.28
1/1/07 12:45 1.28
1/1/07 13:00 1.28
1/1/07 13:15 1.28
1/1/07 13:30 1.28
1/1/07 13:45 1.42
1/1/07 14:00 1.42
1/1/07 14:15 1.27
1/1/07 14:30 1.27
1/1/07 14:45 1.27
1/1/07 15:00 1.42
1/1/07 15:15 1.42
1/1/07 15:30 1.27
1/1/07 15:45 1.27
1/1/07 16:00 1.42
1/1/07 16:15 1.42
1/1/07 16:30 1.45
1/1/07 16:45 1.45
1/1/07 17:00 1.45
1/1/07 17:15 1.62
1/1/07 17:30 1.62
1/1/07 17:45 1.76
1/1/07 18:00 1.69
1/1/07 18:15 1.56
1/1/07 18:30 1.56
1/1/07 18:45 1.40
1/1/07 19:00 1.40
1/1/07 19:15 1.43
1/1/07 19:30 1.43
1/1/07 19:45 1.43
1/1/07 20:00 1.43
1/1/07 20:15 1.72
1/1/07 20:30 1.72
1/1/07 20:45 1.72
1/1/07 21:00 1.61
1/1/07 21:15 1.62
1/1/07 21:30 1.62
1/1/07 21:45 1.62
1/1/07 22:00 1.50
1/1/07 22:15 1.50
1/1/07 22:30 1.50
1/1/07 22:45 1.61
1/1/07 23:00 1.61
1/1/07 23:15 1.61
1/1/07 23:30 1.61
1/1/07 23:45 1.46

**DonkeyOte** · 02-03-2010, 01:34 PM

Originally Posted by arpit_rao6

I guess I dint ask the right question

No, your question was fine - you should perhaps try the suggestion ?
(though you have now added an "every hour" scenario which was not in your first post)

To reiterate, push your Data into a Pivot setup such that Date field is Row Label, Value field is used as Data Field twice over - once set to MIN and once to MAX.
Now right click on any Date value and Group by Year & Month & Day & Hour - this will give you your hourly hi's and low's
Introduce a Subtotal by Day to get the Daily values.

There is no formula requirement here... Pivots will analyse your data far more efficiently.