Luke,
Question:
If:
C2.........D2.........E2.........F2.........G2.... .....H2.........I2.........J2.........K2
4............5............5....................... .........................................4........ ...5
What results would you expect in F2, G2 and H2?
Biff
"Luke" wrote in message
...
Thank you guys, I'll check back periodically to this post. Meanwhile I
will
also keep on it.
Luke
"Biff" wrote:
Luke,
As you discovered my formula does not work as Vasant pointed out. I
obviously didn't test it enough!
Just hang in there. "We'll" figure it out!
Biff
"Luke" wrote in message
...
Okay I you a pat on the back and now I think maybe Vasant had a point.
Let
me show you the errors that appeared after futher review:
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
7 0 4 1 4 4
8 6 2 6 2 2 2
9 4 5 5 4 5 5 4 5
10 3 6 9 6 6 6
11 5 0 1 1 1
12 0 0 5 5 5
13 9 2 4 2 2 2
14 2 6 9 9 9 9
15 1 4 9 1 1 1
This is weird in that why would that formula work for most situations
but
error on others? I keep reverting back to the very long formula I
mentioned
before where, in F2,
IF the concatenated pairs C2,D2 or, C2,E2 are = to any one of the 6
possible
concatenated pairs of I2,J2 & K2, then return C2, otherwise "".
I wonder though if we are not considering this:
in F2,
IF the concatenated pairs C2,D2 or, D2,C2 or, C2,E2 or, E2,C2 are = to
any
one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2,
otherwise "".
I'll keep chugging... Appologies for saying that it worked before I ran
through the master sheet. I've been using an abreviated sheet and only
tried
the formula there.
Thanks again Biff It is closer than I've been able to do and I will try
and
understand the formula so that I might find a solution.
Luke
"Biff" wrote:
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
|