How to find first row where condition is true?

  • Thread starter Thread starter WhatsUp31415
  • Start date Start date
W

WhatsUp31415

Looking at rows 2:4888, I want to find the first row number "x" in which
OR(Hx<>Kx,Ix<>Kx,Jx<>Kx) is true.

How can I write that formula in Excel 2003?

I tried the following array formula, to no avail:

=MIN(IF(OR(H2:H4888<>K2:K4888,I2:I4888<>K2:K4888,J2:J4888<>K2:K4888),ROW(G2:G4888)))

making sure that the condition is first true in row 4.

But that formula always returns 2.

I confirmed that the formula is an array formula enclosed in curly braces.

Also, I filled a parallel column with =OR(H2<>K2,I2<>K2,J2<>K2) and copied
down to confirm that the OR function first returns TRUE in row 4.
 
In N2: =--AND(K2<>"",OR(H2<>K2,I2<>K2,J2<>K2))
Copy down. Leave N1 empty.

(I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful)

Then you could use in say, M2: =MATCH(1,N:N,0)
to get the "first" row number which satisfies the conditions

Above helps in some way? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
 
In N2: =--AND(K2<>"",OR(H2<>K2,I2<>K2,J2<>K2))
Copy down. Leave N1 empty.

(I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful)

Then you could use in say, M2: =MATCH(1,N:N,0)
to get the "first" row number which satisfies the conditions

Above helps in some way? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
 
Maybe this array formula** :

=INDEX(ROW(K:K),MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Maybe this array formula** :

=INDEX(ROW(K:K),MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Max said:
I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful
Click to expand...

Why? Rhetorical question. Only I can determine the "meaningfulness" of
conditions to test. In this case, K2:K4888 is fully populated, just as
columns H, I and J are. So the extra condition is irrelevant

In N2: =--AND(K2<>"",OR(H2<>K2,I2<>K2,J2<>K2))
Copy down. Leave N1 empty.
[....]
Then you could use in say, M2: =MATCH(1,N:N,0)
Click to expand...

Well, if I wanted to use helper cells, I would simply use
=OR(H2<>K2,I2<>K2,J2<>K2), as I did in my original posting. Computationally
more efficient.

And I would use =ROW(M1)+MATCH(TRUE,M2:M4888,0) because I want the row
number, not simply the array index. I use ROW so that the formula is
updated automagically if I insert rows above.

Above helps in some way?
Click to expand...

Not really. I asked for a formula, if possible. If that's not possible, an
explanation of why not would be helpful.

I guess the reason that my formula failed is that OR accepts an array
argument, which foils my attempt to have the formula interpreted as:
MIN({IF(OR(H2<>K2,I2<>K2,J2<>K2),ROW(G2),
IF(OR(H3<>K3,I3<>K3,J3<>K3),ROW(G3), ...}).

Having realized that, I simply need to use an alternative for OR. The
following does the job:

=MIN(IF((H3:H4889<>K3:K4889)+(I3:I4889<>K3:K4889)+(J3:J4889<>K3:K4889),ROW(G3:G4889)))

(Sorry for the row numbering change. I inserted a row.)


----- original message -----
 
Max said:
I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful
Click to expand...

Why? Rhetorical question. Only I can determine the "meaningfulness" of
conditions to test. In this case, K2:K4888 is fully populated, just as
columns H, I and J are. So the extra condition is irrelevant

In N2: =--AND(K2<>"",OR(H2<>K2,I2<>K2,J2<>K2))
Copy down. Leave N1 empty.
[....]
Then you could use in say, M2: =MATCH(1,N:N,0)
Click to expand...

Well, if I wanted to use helper cells, I would simply use
=OR(H2<>K2,I2<>K2,J2<>K2), as I did in my original posting. Computationally
more efficient.

And I would use =ROW(M1)+MATCH(TRUE,M2:M4888,0) because I want the row
number, not simply the array index. I use ROW so that the formula is
updated automagically if I insert rows above.

Above helps in some way?
Click to expand...

Not really. I asked for a formula, if possible. If that's not possible, an
explanation of why not would be helpful.

I guess the reason that my formula failed is that OR accepts an array
argument, which foils my attempt to have the formula interpreted as:
MIN({IF(OR(H2<>K2,I2<>K2,J2<>K2),ROW(G2),
IF(OR(H3<>K3,I3<>K3,J3<>K3),ROW(G3), ...}).

Having realized that, I simply need to use an alternative for OR. The
following does the job:

=MIN(IF((H3:H4889<>K3:K4889)+(I3:I4889<>K3:K4889)+(J3:J4889<>K3:K4889),ROW(G3:G4889)))

(Sorry for the row numbering change. I inserted a row.)


----- original message -----
 
Improvement

Don't really need INDEX:

=MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1

Still array entered
 
Improvement

Don't really need INDEX:

=MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1

Still array entered
 
Ah, so. Dismiss my earlier help attempt which is clearly below your
expertise level. I have no further comments.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
WhatsUp31415 said:
Max said:
I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful
Click to expand...

Why? Rhetorical question. Only I can determine the "meaningfulness" of
conditions to test. In this case, K2:K4888 is fully populated, just as
columns H, I and J are. So the extra condition is irrelevant

In N2: =--AND(K2<>"",OR(H2<>K2,I2<>K2,J2<>K2))
Copy down. Leave N1 empty.
[....]
Then you could use in say, M2: =MATCH(1,N:N,0)
Click to expand...

Well, if I wanted to use helper cells, I would simply use
=OR(H2<>K2,I2<>K2,J2<>K2), as I did in my original posting.
Computationally more efficient.

And I would use =ROW(M1)+MATCH(TRUE,M2:M4888,0) because I want the row
number, not simply the array index. I use ROW so that the formula is
updated automagically if I insert rows above.

Above helps in some way?
Click to expand...

Not really. I asked for a formula, if possible. If that's not possible,
an explanation of why not would be helpful.

I guess the reason that my formula failed is that OR accepts an array
argument, which foils my attempt to have the formula interpreted as:
MIN({IF(OR(H2<>K2,I2<>K2,J2<>K2),ROW(G2),
IF(OR(H3<>K3,I3<>K3,J3<>K3),ROW(G3), ...}).

Having realized that, I simply need to use an alternative for OR. The
following does the job:

=MIN(IF((H3:H4889<>K3:K4889)+(I3:I4889<>K3:K4889)+(J3:J4889<>K3:K4889),ROW(G3:G4889)))

(Sorry for the row numbering change. I inserted a row.)
Click to expand...
 
Ah, so. Dismiss my earlier help attempt which is clearly below your
expertise level. I have no further comments.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
WhatsUp31415 said:
Max said:
I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful
Click to expand...

Why? Rhetorical question. Only I can determine the "meaningfulness" of
conditions to test. In this case, K2:K4888 is fully populated, just as
columns H, I and J are. So the extra condition is irrelevant

In N2: =--AND(K2<>"",OR(H2<>K2,I2<>K2,J2<>K2))
Copy down. Leave N1 empty.
[....]
Then you could use in say, M2: =MATCH(1,N:N,0)
Click to expand...

Well, if I wanted to use helper cells, I would simply use
=OR(H2<>K2,I2<>K2,J2<>K2), as I did in my original posting.
Computationally more efficient.

And I would use =ROW(M1)+MATCH(TRUE,M2:M4888,0) because I want the row
number, not simply the array index. I use ROW so that the formula is
updated automagically if I insert rows above.

Above helps in some way?
Click to expand...

Not really. I asked for a formula, if possible. If that's not possible,
an explanation of why not would be helpful.

I guess the reason that my formula failed is that OR accepts an array
argument, which foils my attempt to have the formula interpreted as:
MIN({IF(OR(H2<>K2,I2<>K2,J2<>K2),ROW(G2),
IF(OR(H3<>K3,I3<>K3,J3<>K3),ROW(G3), ...}).

Having realized that, I simply need to use an alternative for OR. The
following does the job:

=MIN(IF((H3:H4889<>K3:K4889)+(I3:I4889<>K3:K4889)+(J3:J4889<>K3:K4889),ROW(G3:G4889)))

(Sorry for the row numbering change. I inserted a row.)
Click to expand...
 
You cannot use logical functions in an array formula
This gives the row of the first line where H, I OR J do not equal K
=MIN(IF(((H2:H14<>K2:K14)+(I2:I14<>K2:K14)+(J2:J14<>K2:K14)),ROW(K2:K14),10^99))
enter as array formula, of course
change + to - to find the fist row where H, I AND J do not equal K
best wishes
 
You cannot use logical functions in an array formula
This gives the row of the first line where H, I OR J do not equal K
=MIN(IF(((H2:H14<>K2:K14)+(I2:I14<>K2:K14)+(J2:J14<>K2:K14)),ROW(K2:K14),10^99))
enter as array formula, of course
change + to - to find the fist row where H, I AND J do not equal K
best wishes
 
Back
Top