Need help for data mining
 

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 D2:D7, and the minimum low in this range is
590.87, thus G2 should return the time of 15:55.)

Thanks for any help...
--
johnnyk

Need help for data mining
 

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.
--
Gary''s Student - gsnu200809


"johnnyk" wrote:

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 D2:D7, and the minimum low in this range is
590.87, thus G2 should return the time of 15:55.)

Thanks for any help...
--
johnnyk

Need help for data mining
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"johnnyk" wrote in message
...
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 D2:D7, and the minimum low in this range
is
590.87, thus G2 should return the time of 15:55.)

Thanks for any help...
--
johnnyk

Need help for data mining
 

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


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200809


"johnnyk" wrote:

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 D2:D7, and the minimum low in this range is
590.87, thus G2 should return the time of 15:55.)

Thanks for any help...
--
johnnyk

Need help for data mining
 

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!
--
johnnyk


"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"johnnyk" wrote in message
...
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 D2:D7, and the minimum low in this range
is
590.87, thus G2 should return the time of 15:55.)

Thanks for any help...
--
johnnyk