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)=concatenate(I2,K2),I2,IF(concaten ate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2 ,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=c oncatenate(K2,I2),I2,IF(concatenate(C2,D2)=concate nate(K2,J2),I2,
IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(con catenate(C2,E2)=concatenate(I2,K2),I2,IF(concatena te(C2,E2)=concatenate(J2,I2),I2,IF(concatenate(C2, E2)=concatenate(J2,K2),I2,IF(concatenate(C2,E2)=co ncatenate(K2,I2),I2,IF(concatenate(C2,E2)=concaten ate(K2,J2),I2,
IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(con catenate(D2,C2)=concatenate(I2,K2),I2,IF(concatena te(D2,C2)=concatenate(J2,I2),I2,IF(concatenate(D2, C2)=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=co ncatenate(K2,I2),I2,IF(concatenate(D2,C2)=concaten ate(K2,J2),I2,
IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(con catenate(D2,E2)=concatenate(I2,K2),I2,IF(concatena te(D2,E2)=concatenate(J2,I2),I2,IF(concatenate(D2, E2)=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=co ncatenate(K2,I2),I2,IF(concatenate(D2,E2)=concaten ate(K2,J2),I2,
IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(con catenate(E2,C2)=concatenate(I2,K2),I2,IF(concatena te(E2,C2)=concatenate(J2,I2),I2,IF(concatenate(E2, C2)=concatenate(J2,K2),I2,IF(concatenate(E2,C2)=co ncatenate(K2,I2),I2,IF(concatenate(E2,C2)=concaten ate(K2,J2),I2,
IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(con catenate(E2,D2)=concatenate(I2,K2),I2,IF(concatena te(E2,D2)=concatenate(J2,I2),I2,IF(concatenate(E2, D2)=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=co ncatenate(K2,I2),I2,IF(concatenate(E2,D2)=concaten ate(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
|