How to find first row where condition is true?

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.
 
M

Max

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
 
M

Max

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
 
T

T. Valko

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.
 
T

T. Valko

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.
 
W

WhatsUp31415

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 -----
 
W

WhatsUp31415

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 -----
 
T

T. Valko

Improvement

Don't really need INDEX:

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

Still array entered
 
T

T. Valko

Improvement

Don't really need INDEX:

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

Still array entered
 
M

Max

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...
 
M

Max

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...
 
B

Bernard Liengme

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
 
B

Bernard Liengme

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time punch & hours calculations 1
Nested ifs within a row 0
Easiest way to find differences between Rows? 3
Easier way to concatenate a row? 8
Percentage Formula 2
count cell if value present in every other cell + criteria 2
Average 5 columns of data skipping blank columns 10
How to test variable for chr values 1

Top