simplify function

[Luke]

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),CONCATENATE(D2,C2),CONCATENATE(D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATENATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATENATE(K2,J2)),I2,"")

Thank you for your time
Luke

 

[Luke]

I made a mistake and put a mutiplication sign in the formula. The correct
formula
=IF(OR(CONCATENATE(C2,D2),CONCATENATE(C2,E2),CONCATENATE(D2,C2),CONCATENATE(D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATENATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATENATE(K2,J2)),I2,"")

Not sure how that "*" sign got in there.
sorry
Regards,
Luke

 

[Bob Phillips]

Can you explain why it should be 88, 15 "" as I am afraid the formula makes
no sense (to me)

 

[Vasant Nanavati]

Hi Bob:

My interpretation: if any two of the numbers in the first group match any
two of the number in the second group, concatenate the two matching numbers
in the first group. I assume that if all three numbers match, you would
concatenate the first two in the first group.

It's an interesting challenge but I had to get back to real work. I had it
working except for the case where there is a duplicate entry in the first
group.

Regards,

Vasant

 

[Luke]

Opps again I made an error on this table... It should have read:
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(concatenate(C2,D2)=concatenate(I2,K2),I2,IF(concatenate(C2,D2)=concatenate(J2,I2),I2,IF(concatenate(C2,D2)=concatenate(J2,K2),I2,IF(concatenate(C2,D2)=concatenate(K2,I2),I2,IF(concatenate(C2,D2)=concatenate(K2,J2),I2,

IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenate(I2,K2),I2,IF(concatenate(C2,E2)=concatenate(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(concatenate(D2,C2)=concatenate(I2,K2),I2,IF(concatenate(D2,C2)=concatenate(J2,I2),I2,IF(concatenate(D2,C2)=concatenate(J2,K2),I2,IF(concatenate(D2,C2)=concatenate(K2,I2),I2,IF(concatenate(D2,C2)=concatenate(K2,J2),I2,

IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenate(I2,K2),I2,IF(concatenate(D2,E2)=concatenate(J2,I2),I2,IF(concatenate(D2,E2)=concatenate(J2,K2),I2,IF(concatenate(D2,E2)=concatenate(K2,I2),I2,IF(concatenate(D2,E2)=concatenate(K2,J2),I2,

IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenate(I2,K2),I2,IF(concatenate(E2,C2)=concatenate(J2,I2),I2,IF(concatenate(E2,C2)=concatenate(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(concatenate(E2,D2)=concatenate(J2,I2),I2,IF(concatenate(E2,D2)=concatenate(J2,K2),I2,IF(concatenate(E2,D2)=concatenate(K2,I2),I2,IF(concatenate(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

 

[Vasant Nanavati]

Still isn't right. I2 is "", not 8.

 

[Alok Joshi]

Hi Luke,
Try the following formula in G2
=IF(OR(ISNA(MODE(C2:E2)),ISNA(MODE(H2:J2))),"",IF(MODE(C2:E2)=MODE(H2:J2),I2
,""))
Alok

 

[Luke]

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

 

[Luke]

That works if I don't offer up a different combination with the same
digits... ie if I change I2 from 8 to "" and J2 from "" to 8 I get "0"

 

[Vasant Nanavati]

Still confused. Shouldn't G3 show 9, then?

 

[Luke]

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:I really am sorry for the confusion as I made worse trying to make it easier.
Luke

 

[bj]

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,"")

 

[bj]

I meant to add if my assumption that what you want to do is that

if at least two cells from ABC match at least two cells in IJF in any order
and I is one copy I to F
if at least two cells from ABC match at least two cells in IJF in any order
and J is one copy J to G
if at least two cells from ABC match at least two cells in IJF in any order
and K is one copy K to H

after saying this change my equations frorm the last response to:
in F2 put
=if(and(--(countif(C2:E2,I2)>0)--(countif(C2:E2,J2)>0)--(Countif(C2:E2,K2)>0)>1
,--(countif(I2:K2,C2)>0)--(countif(I2:K2,D2)>0)--(Countif(I2:K2,E2)>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(I2:K2,C2)>0)--(countif(I2:K2,D2)>0)--(Countif(I2:K2,E2)>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(I2:K2,C2)>0)--(countif(I2:K2,D2)>0)--(Countif(I2:K2,E2)>0)>1,--countif(c2:E2,I2)>0),k2,"")

 

[Luke]

Hi bj,
I made another thread on 4/30/05 a short time after this post because I kept
making mistakes. Please look at it and know that I tried this formula that
you made and it is probably good but it is based on errors that I
inadvertantly made when I posted this one. read those post /threads as they
will give better insight as to what I am really trying to do.
Thanks for your efforts and I will look for responses there.
Luke