Need help for data mining

[johnnyk]

Hello Everyone,

I have struggled with this until I have only a few hairs left on the top of
head...Can any of you kind souls please help?

A B C D E
F G
DATE TIME HIGH LOW DATE TIME OF HIGH
TIME OF LOW
8/29/08 15:55 591.30 590.87 8/29/08 15:35 15:55
8/29/08 15:50 592.12 591.25 8/28/08 15:40 15:30
8/29/08 15:45 592.56 592.17 8/27/08 15:35 15:50
8/29/08 15:40 592.56 591.85
8/29/08 15:35 593.27 592.57
8/29/08 15:30 592.99 592.22
8/28/08 15:55 599.52 598.66
8/28/08 15:50 599.17 598.71
8/28/08 15:45 599.13 598.67
8/28/08 15:40 599.53 599.01
8/28/08 15:35 598.84 598.26
8/28/08 15:30 598.51 597.82
8/27/08 15:55 598.50 597.85
8/27/08 15:50 598.41 597.50
8/27/08 15:45 598.34 597.92
8/27/08 15:40 598.63 597.89
8/27/08 15:35 598.75 598.24
8/27/08 15:30 598.60 597.79
8/26/08 15:55 598.87 598.29
8/26/08 15:50 598.71 598.18
8/26/08 15:45 598.53 598.15
8/26/08 15:40 598.43 598.02
8/26/08 15:35 598.78 598.17
8/27/08 15:30 598.45 598.19

Data entry begins in A1. I need the following:

1. In Column E, return from Column A only one date for each of the dates in
Column A (i.e., E2 should return 8/29/08; E3 should return 8/28/08; E4 should
return 8/257/08; and so on).

2. Next, from the range of Highs in Column C find the MAXIMUM High for each
date in Column A that matches the Date in Column E, and then return its
corresponding Time in column F in the same row as the matched date. (i.e.,
the range of dates in Column A for 8/29/08 is A2:A7, and the range of
corresponding highs in Column C is C2:C7, and the maximum high in this range
is 593.27, thus F2 should return the time of 15:35.)

3. Next, from the range of Lows in Column D find the MIMIMUM Low for each
date in Column A that matches the Date in Column E, and then return its
corresponding Time in column G in the same row as the matched date. (i.e.,
the range of dates in Column A for 8/29/08 is A2:A7, and the range of
corresponding lows in Column D is D27, and the minimum low in this range is
590.87, thus G2 should return the time of 15:55.)

Thanks for any help...

 

[Gary''s Student]

Two steps:

1. using a Pivot Table, create a table that gives the max high and min low
for each date

2. use VLOOKUP to get the times associated with each vlue in the table above.

 

[Don Guillett]

08/29/08 590.87 15:55
08/28/08 597.82 15:30
08/27/08 597.50 15:50
08/26/08 598.02 15:40


=MIN(IF($A$2:$A$25=$E6,$D$2:$D$25))
=MIN(IF(($A$2:$A$25=$E6)*($D$2:$D$25=$F6),$B$2:$B$25))
These are ARRAY formulas that must be entered using ctrl+shift+enter

 

[johnnyk]

Thank you for your speedy response. I got step 1 with no problem, but I can't
get step 2 to work properly. Could you provide an example formula to use?

 

[johnnyk]

To Gary's Student & Don Guillett,

I want to thank you both for your speedy responses to my dilemma. By
combining the suggestions from both of you along with a lot of experimenting,
I was able to get the results I desired. But because I am learning thru the
trial-and-error method, my worksheet may not be as efficient as it could
be...nevertheless it works.

Thanks a million to both of you!