V
vezerid
Hi all,
I ran into this while trying to develop a solution for another OP. I
did come up with a solution, but as I was working the intermediate
steps of this complex problem, I ran into this strange behavior.
The input of the problem is a structure like this. F1, F2, etc are not
cells, they are codes.
F1 a b c d
F2 b h
F3 d
F4 a d
F5 c e h
The object is to reach an output structure like this:
a F1 F4
b F1
c F1 F5
d F1 F3 F4
e F5
f
g
h F2 F5
Below you see my efforts while trying to build the solution. The first
column contains a, b, c, and so on. The second column contains the
following array formula:
=MIN(IF(COUNTIF(OFFSET($A$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5)))
The idea being to find the smallest row number in which the letter of
the first column appears. As you see it contains correct numbers. a, b,
c, d are all in F1. e first appears in F5. f and g do not appear, hence
the 0. And h appears first in F2.
a 1 F1 F1
b 1 F1 F1
c 1 F1 F1
d 1 F1 F1
e 5 F5 F5
f 0 F1 #VALUE!
g 0 F1 #VALUE!
h 2 F2 F2
The third column is the result of plugging the previous formula into
INDEX (again array-entered):
=INDEX($A$1:$A$5,MIN(IF(COUNTIF(OFFSET($A$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5))))
And as you see, in f and g (the two 0's), I am getting *erroneously*
F1, instead of an error value. The error value is successfully produced
in the last column, where I am simply using:
=INDEX($A$1:$A$5,H1)
where H1 contains the value in the second column above.
Given all this: Is this a glitch? Am I missing something? I worked the
problem around by using a more elaborate IF:
=INDEX($A$1:$A$5,MIN(IF(COUNTIF(OFFSET($B$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5),1000)))
This would cause a #REF! error in the place of 0's of the 2nd column by
introducing a very large number and could be handled with error
checking. But why did the previous formula not work?
Regards,
Kostis Vezerides
I ran into this while trying to develop a solution for another OP. I
did come up with a solution, but as I was working the intermediate
steps of this complex problem, I ran into this strange behavior.
The input of the problem is a structure like this. F1, F2, etc are not
cells, they are codes.
F1 a b c d
F2 b h
F3 d
F4 a d
F5 c e h
The object is to reach an output structure like this:
a F1 F4
b F1
c F1 F5
d F1 F3 F4
e F5
f
g
h F2 F5
Below you see my efforts while trying to build the solution. The first
column contains a, b, c, and so on. The second column contains the
following array formula:
=MIN(IF(COUNTIF(OFFSET($A$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5)))
The idea being to find the smallest row number in which the letter of
the first column appears. As you see it contains correct numbers. a, b,
c, d are all in F1. e first appears in F5. f and g do not appear, hence
the 0. And h appears first in F2.
a 1 F1 F1
b 1 F1 F1
c 1 F1 F1
d 1 F1 F1
e 5 F5 F5
f 0 F1 #VALUE!
g 0 F1 #VALUE!
h 2 F2 F2
The third column is the result of plugging the previous formula into
INDEX (again array-entered):
=INDEX($A$1:$A$5,MIN(IF(COUNTIF(OFFSET($A$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5))))
And as you see, in f and g (the two 0's), I am getting *erroneously*
F1, instead of an error value. The error value is successfully produced
in the last column, where I am simply using:
=INDEX($A$1:$A$5,H1)
where H1 contains the value in the second column above.
Given all this: Is this a glitch? Am I missing something? I worked the
problem around by using a more elaborate IF:
=INDEX($A$1:$A$5,MIN(IF(COUNTIF(OFFSET($B$1:$E$1,ROW($1:$5)-1,0),$G1)>0,ROW($1:$5),1000)))
This would cause a #REF! error in the place of 0's of the 2nd column by
introducing a very large number and could be handled with error
checking. But why did the previous formula not work?
Regards,
Kostis Vezerides