Finding two numbers simultaneously.

I select a value (1st number), then I need to find a second value located 5,
10 and 15 readings previous. The information is 90 rows long and 5 columns
wide. Because these values don't always show up at 5, 10 and 15 readings
apart, I have to keep looking until they do. The readings don't need to show
up together in a group. I need one result for 5, then another for 10 and
another for 15.

I've been using conditional formatting to highlight the two numbers in the
range of B2:F91 and counting out manually when the readings show up
togetherin the 5, 10 and 15 spans. Readings are being taken once a day, so
the range changes every day.

I've been looking at Match, IF, Offset and MMult, I think Match is out
because I can't figure out how to make it look for the last set first and
match two values at the same time. I'm thinking it's going to be a
combination in an array formula.

I'm working on macros and am picking up a little VBA, but I'm still mostly
lost.

Any assistance will be Greatly appreciated, Thank you.
--
Linda

Aha! I have not been idle. There is a programing site here. I am not a
programer, but I think I am finding answers, but don't know how to implement
them.

A B C D E F
8/13/05 0.156 0.163 0.108 0.124 0.103
8/14/05 0.134 0.173 0.077 0.109 0.105
8/15/05 0.142 0.155 0.105 0.105 0.103
8/16/05 0.156 0.132 0.123 0.105 0.114
8/17/05 0.118 0.122 0.077 0.115 0.118
8/18/05 1.088 0.127 0.112 0.119 0.116
8/19/05 0.116 0.119 0.155 0.124 0.105
8/20/05 0.094 0.105 0.132 0.147 0.127
8/21/05 0.105 0.113 0.118 0.156 0.118
8/22/05 0.133 0.118 0.115 0.147 0.116
8/23/05 0.156 0.121 0.116 0.139 0.118
8/24/05 0.133 0.131 0.105 0.129 0.119
8/25/05 0.116 0.128 0.099 0.111 0.105
8/26/05 0.127 0.133 0.118 0.105 0.119
8/27/05 0.118 0.148 0.108 0.099 0.124
8/28/05 0.105 0.156 0.109 0.107 0.139

These are the readings for the last 16 days. There are actually 27 items,
but these are the ones I can control and they are in these same colums in
Excel .

I need to look for 0.156 as value 1 and 0.105 as value 2. Value 1 could be
highlighted Yellow and bold, Value 2 could be highlighted pale green and
bold.

I think a Loop to go through columns B thru F would do it. I also saw that
you could have the VBA program tell you the location of the values that match
up at 5, 10, and 15 row intervals. How do I do this?

By reading other posts, I realized clarity in my post was an issue. I
appologize!
Thank you for your time and patience.


--
Linda


"Linda" wrote:

> I select a value (1st number), then I need to find a second value located 5,
> 10 and 15 readings previous. The information is 90 rows long and 5 columns
> wide. Because these values don't always show up at 5, 10 and 15 readings
> apart, I have to keep looking until they do. The readings don't need to show
> up together in a group. I need one result for 5, then another for 10 and
> another for 15.
>
> I've been using conditional formatting to highlight the two numbers in the
> range of B2:F91 and counting out manually when the readings show up
> togetherin the 5, 10 and 15 spans. Readings are being taken once a day, so
> the range changes every day.
>
> I've been looking at Match, IF, Offset and MMult, I think Match is out
> because I can't figure out how to make it look for the last set first and
> match two values at the same time. I'm thinking it's going to be a
> combination in an array formula.
>
> I'm working on macros and am picking up a little VBA, but I'm still mostly
> lost.
>
> Any assistance will be Greatly appreciated, Thank you.
> --
> Linda

One non-array formulas play which seems to be able to deliver what you want
(if I've read your intent correctly)

Assume the data posted is in Sheet1,
cols A to F, from row1 down

We'll use 3 empty cols to the right , say cols H, I & J

Put:
in H1: = --ISNUMBER(MATCH(Sheet2!$A$1,B1:F1,0))
in I1: = --ISNUMBER(MATCH(Sheet2!$B$1,B1:F1,0))
in J1: =IF(SUM(H1:I1)=2,ROW(),"")

Select H1:J1, fill down to say, J100,
to cover the max expected data range

In Sheet2
----------
A1:B1 will be where you enter inputs for values 1 & 2
(Enter the 2 values: 0.105, 0.156 into A1:B1)

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$J:$J,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$J:$J,ROWS($A$1:A1)),Sheet1!$J:$J,0)))

Copy A2 across to F2, fill down to F101
(cover the same range size as done in the cols H - J in Sheet1)

Format A2:A101 as dates

Sheet2 will return the desired results from Sheet1 for the inputs made in
A1:B1, all neatly bunched at the top, with blank rows below

For the sample data posted, you'd get:

0.105 0.156 << Inputs in A1:B1
16-Aug-05 0.156 0.132 0.123 0.105 0.114
21-Aug-05 0.105 0.113 0.118 0.156 0.118
28-Aug-05 0.105 0.156 0.109 0.107 0.139
< blank rows >

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Linda" <[email protected]> wrote in message
news:[email protected]...
> Aha! I have not been idle. There is a programing site here. I am not a
> programer, but I think I am finding answers, but don't know how to
implement
> them.
>
> A B C D E F
> 8/13/05 0.156 0.163 0.108 0.124 0.103
> 8/14/05 0.134 0.173 0.077 0.109 0.105
> 8/15/05 0.142 0.155 0.105 0.105 0.103
> 8/16/05 0.156 0.132 0.123 0.105 0.114
> 8/17/05 0.118 0.122 0.077 0.115 0.118
> 8/18/05 1.088 0.127 0.112 0.119 0.116
> 8/19/05 0.116 0.119 0.155 0.124 0.105
> 8/20/05 0.094 0.105 0.132 0.147 0.127
> 8/21/05 0.105 0.113 0.118 0.156 0.118
> 8/22/05 0.133 0.118 0.115 0.147 0.116
> 8/23/05 0.156 0.121 0.116 0.139 0.118
> 8/24/05 0.133 0.131 0.105 0.129 0.119
> 8/25/05 0.116 0.128 0.099 0.111 0.105
> 8/26/05 0.127 0.133 0.118 0.105 0.119
> 8/27/05 0.118 0.148 0.108 0.099 0.124
> 8/28/05 0.105 0.156 0.109 0.107 0.139
>
> These are the readings for the last 16 days. There are actually 27 items,
> but these are the ones I can control and they are in these same colums in
> Excel .
>
> I need to look for 0.156 as value 1 and 0.105 as value 2. Value 1 could
be
> highlighted Yellow and bold, Value 2 could be highlighted pale green and
> bold.
>
> I think a Loop to go through columns B thru F would do it. I also saw
that
> you could have the VBA program tell you the location of the values that
match
> up at 5, 10, and 15 row intervals. How do I do this?
>
> By reading other posts, I realized clarity in my post was an issue. I
> appologize!
> Thank you for your time and patience.
>
>
> --
> Linda
>
>
> "Linda" wrote:
>
> > I select a value (1st number), then I need to find a second value
located 5,
> > 10 and 15 readings previous. The information is 90 rows long and 5
columns
> > wide. Because these values don't always show up at 5, 10 and 15 readings
> > apart, I have to keep looking until they do. The readings don't need to
show
> > up together in a group. I need one result for 5, then another for 10
and
> > another for 15.
> >
> > I've been using conditional formatting to highlight the two numbers in
the
> > range of B2:F91 and counting out manually when the readings show up
> > togetherin the 5, 10 and 15 spans. Readings are being taken once a day,
so
> > the range changes every day.
> >
> > I've been looking at Match, IF, Offset and MMult, I think Match is out
> > because I can't figure out how to make it look for the last set first
and
> > match two values at the same time. I'm thinking it's going to be a
> > combination in an array formula.
> >
> > I'm working on macros and am picking up a little VBA, but I'm still
mostly
> > lost.
> >
> > Any assistance will be Greatly appreciated, Thank you.
> > --
> > Linda

> In Sheet2
> ----------
> A1:B1 will be where you enter inputs for values 1 & 2
> (Enter the 2 values: 0.105, 0.156 into A1:B1)

Just a clarification that the 2 values of interest
can be entered in either A1 or B1. The order is immaterial.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Here's a link to a demo file with the implemented set-up:
http://savefile.com/files/6812399
File: Finding two numbers simultaneously_Linda_newusers.xls

Sheet2 will auto-extract the lines from Sheet1's cols A to F
where both values 1 & 2 occur simultaneously on the same line
anywhere within Sheet1's cols B to F
(that's what I figured you wanted ..)

You could then use Sheet2 for further analysis of the dates lapse in col A,
etc
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Linda,

If your data range is A1:F17, header in A1:F1

Maybe...

In Cell I2, put this array formula

=INDEX($A$1:$F$17,MATCH(ROWS($2:2),(ROW($A$1:$A$17)/5)),COLUMNS($I:I))

Confirmed the formula by pressing Ctrl + Shift + Enter

Drag across to Col N and drag down 3 rows

It will return the result as:

I J K L M N

8/16/05 0.156 0.132 0.123 0.105 0.114
8/21/05 0.105 0.113 0.118 0.156 0.118
8/26/05 0.127 0.133 0.118 0.105 0.119


Hope this help.

kk


"Linda" <[email protected]> wrote in message
news:[email protected]...
Aha! I have not been idle. There is a programing site here. I am not a
programer, but I think I am finding answers, but don't know how to implement
them.

A B C D E F
8/13/05 0.156 0.163 0.108 0.124 0.103
8/14/05 0.134 0.173 0.077 0.109 0.105
8/15/05 0.142 0.155 0.105 0.105 0.103
8/16/05 0.156 0.132 0.123 0.105 0.114
8/17/05 0.118 0.122 0.077 0.115 0.118
8/18/05 1.088 0.127 0.112 0.119 0.116
8/19/05 0.116 0.119 0.155 0.124 0.105
8/20/05 0.094 0.105 0.132 0.147 0.127
8/21/05 0.105 0.113 0.118 0.156 0.118
8/22/05 0.133 0.118 0.115 0.147 0.116
8/23/05 0.156 0.121 0.116 0.139 0.118
8/24/05 0.133 0.131 0.105 0.129 0.119
8/25/05 0.116 0.128 0.099 0.111 0.105
8/26/05 0.127 0.133 0.118 0.105 0.119
8/27/05 0.118 0.148 0.108 0.099 0.124
8/28/05 0.105 0.156 0.109 0.107 0.139

These are the readings for the last 16 days. There are actually 27 items,
but these are the ones I can control and they are in these same colums in
Excel .

I need to look for 0.156 as value 1 and 0.105 as value 2. Value 1 could be
highlighted Yellow and bold, Value 2 could be highlighted pale green and
bold.

I think a Loop to go through columns B thru F would do it. I also saw that
you could have the VBA program tell you the location of the values that
match
up at 5, 10, and 15 row intervals. How do I do this?

By reading other posts, I realized clarity in my post was an issue. I
appologize!
Thank you for your time and patience.


--
Linda


"Linda" wrote:

> I select a value (1st number), then I need to find a second value located
> 5,
> 10 and 15 readings previous. The information is 90 rows long and 5
> columns
> wide. Because these values don't always show up at 5, 10 and 15 readings
> apart, I have to keep looking until they do. The readings don't need to
> show
> up together in a group. I need one result for 5, then another for 10 and
> another for 15.
>
> I've been using conditional formatting to highlight the two numbers in the
> range of B2:F91 and counting out manually when the readings show up
> togetherin the 5, 10 and 15 spans. Readings are being taken once a day,
> so
> the range changes every day.
>
> I've been looking at Match, IF, Offset and MMult, I think Match is out
> because I can't figure out how to make it look for the last set first and
> match two values at the same time. I'm thinking it's going to be a
> combination in an array formula.
>
> I'm working on macros and am picking up a little VBA, but I'm still mostly
> lost.
>
> Any assistance will be Greatly appreciated, Thank you.
> --
> Linda