Why does G2 = 8, when J2 = ""?
if the result were to be F2=8 G2="" and H2=8
I would assume you could do something like
in F2 put
=if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--countif(c2:E2,I2)0),I2,"")
in G2 put
=if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--countif(c2:E2,I2)0),J2,"")
in H2 put
=if(and(--(countif(C2:E2,I2)0)--(countif(C2:E2,J2)0)--(Countif(C2:E2,K2)0)1),--countif(c2:E2,I2)0),k2,"")
"Luke" wrote:
See you caught me at another mistake... I should have used the entire senario
from the beginning...Appologies.
I need, should the conditions of the formula are true, F2 to show I2, G2 to
show J2 and H2 to show K2.
I put the results in F G & H respectively:
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
I really am sorry for the confusion as I made worse trying to make it easier.
Luke
"Vasant Nanavati" wrote:
Still confused. Shouldn't G3 show 9, then?
--
Vasant
"Luke" wrote in message
...
in the example
C2=2
D2=8
E2=8
I2=8
J2=""
K2=8
Sorry if they are not lined up.
I2 is 8 so concatenate(I2,K2)=concatenate(D2,E2) and therefore G2 should
show 8
regards
Luke
"Vasant Nanavati" wrote:
Still isn't right. I2 is "", not 8.
--
Vasant
"Luke" wrote in message
...
Opps again I made an error on this table... It should have read:
A B C D E F G H I J K
1
2 2 8 8 8 8 G2 should
show 8
3 1 0 5 9 5 1 G3 should show
""
4 8 3 4 5 2 4 G4 should show
""
I should have pointed out that in this example I want I2 to show up in
G2
but only if any combination of two or more cells in I2 J2 and K2 are
equal
to
any combination of two or more cells in C2 D2 and E2. each (C2,D2,E2)
and
(I2,J2,K2) has 6 different combinations totaling 36 different
senarios.
So, if I concatenate cell C2 and D2 they don't equal any concatenated
combination of any two given cells in I2,J2 & k2. However if I
concatenate(D2,E2) they are the same as Concatenate(I2,K2).
I'll elaborate with that way too long formula of which I broke up into
6
segments so it is easir to see:
=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(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenat
e(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(J2,I 2),I2,IF(concatenate(C2,E2
)=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=conc atenate(K2,I2),I2,IF(con
ca
tenate(C2,E2)=concatenate(K2,J2),I2,
IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenat
e(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I 2),I2,IF(concatenate(D2,C2
)=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=conc atenate(K2,I2),I2,IF(con
ca
tenate(D2,C2)=concatenate(K2,J2),I2,
IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenat
e(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I 2),I2,IF(concatenate(D2,E2
)=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=conc atenate(K2,I2),I2,IF(con
ca
tenate(D2,E2)=concatenate(K2,J2),I2,
IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenat
e(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I 2),I2,IF(concatenate(E2,C2
)=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=conc atenate(K2,I2),I2,IF(con
ca
tenate(E2,C2)=concatenate(K2,J2),I2,
IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenat
e(I2,K2),I2,IF(concatenate(E2,D2)=concatenate(J2,I 2),I2,IF(concatenate(E2,D2
)=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=conc atenate(K2,I2),I2,IF(con
ca
tenate(E2,D2)=concatenate(K2,J2),I2,"")))))))))))) ))))))))...
I think that is about it... if this don't confound the brain I don't
know
what will:)
Thank you Bob for your responses
"Bob Phillips" wrote:
Can you explain why it should be 88, 15 "" as I am afraid the
formula
makes
no sense (to me)
--
HTH
Bob Phillips
"Luke" wrote in message
...
I made a mistake and put a mutiplication sign in the formula. The
correct
formula:
=IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE(
D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN
ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN
ATE(K2,J2)),I2,"")
Not sure how that "*" sign got in there.
sorry
Regards,
Luke
"Luke" wrote:
I created this function to show you exactly what I need. I know
if I
plug it
in it will return a #VALUE# error. I tried to create it without
the
OR()
function but it is too long for Excel.
Is there a more simple way to write this?
Sheet1:
A B C D E F G H I J K
1
2 2 8 8 8 8 should show
88
3 1 0 5 9 5 1 should show
15
4 8 3 4 5 2 4 should show
""
=IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCA TENATE(D2,C2),CONCATENATE(
D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(C ONCATENATE(I2,J2),CONCATEN
ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),C ONCATENATE(K2,I2),CONCATEN
ATE(K2,J2)),I2,"")
Thank you for your time
Luke
|