Not that easy :). I've been working at this off and on for the last couple
of days
Yeah, but with each new post the desired outcome changes! :(
I'll take another look!
Biff
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Not that easy :). I've been working at this off and on for the last couple
of days.
Your formula's result doesn't change even if there is only 1 "8" on the
right side in the top row. My understanding is that if there are 2 "8"s on
the left side, there have to be 2 on the right as well. It's possible to
test for this but it gives rise to a host of other complications.
--
Vasant
"Biff" wrote in message
...
Ok, we're making progress!
So, if F2 references C2
and G2 references D2
then H2 must reference E2
In F2 enter this formula and copy across to H2 then down:
=IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))=2,CO UNTIF($I2:$K2,C2)0),C2,""
)
Will return:
F........G........H
...........8.........8
1....................5
........................
8.........6...........
9.........0.........2
Biff
"Luke" wrote in message
...
This is hard to keep straight and I realize as I keep trying to
prevail,
the
responses I get have showed me my own errors.
I did say that I2 is relevant to F2. I should have said F2 is relevant
to
C2 so barring any further revelations, keep in mind that it doesn't
matter
how the digits in columns I,J & K are aranged. Here is a Corrected
Table:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 1 5 9 5 1
4 8 3 4 5 2 4
5 8 6 7 8 6 6 2 8
6 9 0 2 9 0 2 2 9 0
F2 is relevant only to C2 and only those 6 different combinations in
I2,
J2
& K2.
So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is
equal
to
any given concatenated pair of (I2, J2 & K2) then C2, otherwise "").
By
Concatenated pairs I mean:
In cell F2;
IF concatenate(C2,D2)=
concatenate(I2,J2) or
concatenate(I2,K2) or
concatenate(J2,I2) or
concatenate(J2,K2) or
concatenate(K2,I2) or
concatenate(K2,J2) then C2 otherwise
IF concatenate(C2,E2)=
concatenate(I2,J2) or
concatenate(I2,K2) or
concatenate(J2,I2) or
concatenate(J2,K2) or
concatenate(K2,I2) or
concatenate(K2,J2) then C2,""))
Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is
no
"2" in I2, J2 or K2.
Likewise:
G2 is relevant only to D2 and only those 6 different combinations in
I2,
J2
& K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is
equal
to
any given concatenated pair of I2, J2 & K2 then D2 otherwise "".
Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and
concatenate(D2,E2)=88 which is equal to one of the 'any given
combinations
of
I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2
would
show the content of D2=8
There has to be a way to do this little puzzle. I hope this time I have
made
sense accurately. If I have articulated this even remotely close to
showing
what I would like to have happen in F,G & H then I think I have
accomplished
something in that alone :) but I would like to succeed.
Thank you,
Luke
"Biff" wrote:
Hi!
Based on your explanation of what you want and comparing that to your
table,
I can't see this being done!
Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2)
Why would F5 = 6 (I5) and F2 not = 8 (I2)
Biff
"Luke" wrote in message
...
Pardon the new thread about the same thing I posted earlier. I
would
like
to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 5 1 9 5 1
4 8 3 4 5 2 4
5 8 6 7 6 8 6 2 8
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two
or
more
digits from I,J & K then display contents of I in F.
The same formula would go for G in that any combination of two or
more
digits from C,D & E that are equal to two or more digits from I,J &
K
then
display contents of J in G. The only thing in the formula that
chages
is
that
instead of showing I in F, it would show J in G
The same would go for G in that any combination of two or more
digits
from
C,D & E that are equal to two or more digits from I,J & K then
display
contents of K in H. The only thing in the formula that chages is
that
instead
of showing I or J in H, it would show K in H
Here is my version of the Formula for column F only... the same
formula
could be pasted in G & H columns repectively-- If it were in simpler
format.
=IF(concatenate(C2,D2)=concatenate(I2,J2),I2,IF(co ncatenate(C2,D2)=concatena
te(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2, I2),I2,IF(concatenate(C2,D
2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=con catenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,IF(concatenat e(C2,E2)=concatenate(I2,J2
),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,I F(concatenate(C2,E2)=conca
tenate(J2,I2),I2,IF(concatenate(C2,E2)=concatenate (J2,K2),I2,IF(concatenate(
C2,E2)=concatenate(K2,I2),I2,IF(concatenate(C2,E2) =concatenate(K2,J2),I2,IF(
concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concat enate(D2,C2)=concatenate(I
2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2), I2,IF(concatenate(D2,C2)=c
oncatenate(J2,K2),I2,IF(concatenate(D2,C2)=concate nate(K2,I2),I2,IF(concaten
ate(D2,C2)=concatenate(K2,J2),I2,IF(concatenate(D2 ,E2)=concatenate(I2,J2),I2
,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(co ncatenate(D2,E2)=concatena
te(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2, K2),I2,IF(concatenate(D2,E
2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=con catenate(K2,J2),I2,IF(conc
atenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenat e(E2,C2)=concatenate(I2,K2
),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,I F(concatenate(E2,C2)=conca
tenate(J2,K2),I2,IF(concatenate(E2,C2)=concatenate (K2,I2),I2,IF(concatenate(
E2,C2)=concatenate(K2,J2),I2,IF(concatenate(E2,D2) =concatenate(I2,J2),I2,IF(
concatenate(E2,D2)=concatenate(I2,K2),I2,IF(concat enate(E2,D2)=concatenate(J
2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2), I2,IF(concatenate(E2,D2)=c
oncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concate nate(K2,J2),I2,"")))))))))
)))))))))))
Thank you for your time and toleration.
Luke
|