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(CONCATENATE(I2,J2),CONCAT ENATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2) ,CONCATENATE(K2,I2),CONCATENATE(K2,J2)),I2,"")

Thank you for your time
Luke

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(CONCATENATE(I2,J2),CONCAT ENATE(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

"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(CONCATENATE(I2,J2),CONCAT ENATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2) ,CONCATENATE(K2,I2),CONCATENATE(K2,J2)),I2,"")

Thank you for your time
Luke

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

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


"Bob Phillips" wrote in message
...
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

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

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(conca
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(conca
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(conca
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(conca
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(conca
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

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(conca
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(conca
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(conca
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(conca
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(conca
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

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" 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(conca
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(conca
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(conca
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(conca
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(conca
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

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"

"Alok Joshi" wrote:

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" 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(conca
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(conca
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(conca
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(conca
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(conca
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