simplify function

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

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

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

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

--
HTH

Bob Phillips

"Luke" <Luke@discussions.microsoft.com> wrote in message
news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> 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),CONCATEN
ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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" <phillips@tiscali.co.uk> wrote in message
news:eR3ag7ZTFHA.228@TK2MSFTNGP12.phx.gbl...
> Can you explain why it should be 88, 15 "" as I am afraid the formula
makes
> no sense (to me)
>
> --
> HTH
>
> Bob Phillips
>
> "Luke" <Luke@discussions.microsoft.com> wrote in message
> news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > 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),CONCATEN
>
ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
>
D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
>
ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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(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
"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" <Luke@discussions.microsoft.com> wrote in message
> news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > 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),CONCATEN
> ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATEN
> ATE(K2,J2)),I2,"")
> > >
> > > Thank you for your time
> > > Luke
>
>
>

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

--

Vasant



"Luke" <Luke@discussions.microsoft.com> wrote in message
news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
e(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(conca
tenate(C2,E2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
e(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(conca
tenate(D2,C2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
e(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(conca
tenate(D2,E2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
e(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(conca
tenate(E2,C2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > 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),CONCATEN
> >
ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> >
D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> >
ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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" <Luke@discussions.microsoft.com> wrote in message
news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
atenate(C2,D2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
e(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(conca
tenate(C2,E2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
e(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(conca
tenate(D2,C2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
e(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(conca
tenate(D2,E2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
e(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(conca
tenate(E2,C2)=concatenate(K2,J2),I2,
>
>
IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > 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),CONCATEN
> >
ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> >
D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> >
ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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" <Luke@discussions.microsoft.com> wrote in message
> news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> > 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
> atenate(C2,D2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
> e(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(conca
> tenate(C2,E2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
> e(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(conca
> tenate(D2,C2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
> e(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(conca
> tenate(D2,E2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
> e(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(conca
> tenate(E2,C2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
> e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > > 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),CONCATEN
> > >
> ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> > >
> D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> > >
> ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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" <Luke@discussions.microsoft.com> wrote in message
> news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> > 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
> atenate(C2,D2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
> e(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(conca
> tenate(C2,E2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
> e(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(conca
> tenate(D2,C2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
> e(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(conca
> tenate(D2,E2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
> e(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(conca
> tenate(E2,C2)=concatenate(K2,J2),I2,
> >
> >
> IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
> e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > > 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),CONCATEN
> > >
> ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> > >
> D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> > >
> ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATEN
> > > ATE(K2,J2)),I2,"")
> > > > >
> > > > > Thank you for your time
> > > > > Luke
> > >
> > >
> > >
>
>
>

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

--

Vasant


"Luke" <Luke@discussions.microsoft.com> wrote in message
news:0590F416-29D7-4D84-AD90-658AB4DB0B25@microsoft.com...
> 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" <Luke@discussions.microsoft.com> wrote in message
> > news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> > > 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
> > atenate(C2,D2)=concatenate(K2,J2),I2,
> > >
> > >
> >
IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
> >
e(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(con
ca
> > tenate(C2,E2)=concatenate(K2,J2),I2,
> > >
> > >
> >
IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
> >
e(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(con
ca
> > tenate(D2,C2)=concatenate(K2,J2),I2,
> > >
> > >
> >
IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
> >
e(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(con
ca
> > tenate(D2,E2)=concatenate(K2,J2),I2,
> > >
> > >
> >
IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
> >
e(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(con
ca
> > tenate(E2,C2)=concatenate(K2,J2),I2,
> > >
> > >
> >
IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
> >
e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > > > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > > > 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),CONCATEN
> > > >
> >
ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> > > >
> >
D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> > > >
> >
ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATEN
> > > > ATE(K2,J2)),I2,"")
> > > > > >
> > > > > > Thank you for your time
> > > > > > 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:
> > > > > 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" <Luke@discussions.microsoft.com> wrote in message
> news:0590F416-29D7-4D84-AD90-658AB4DB0B25@microsoft.com...
> > 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" <Luke@discussions.microsoft.com> wrote in message
> > > news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> > > > 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
> > > atenate(C2,D2)=concatenate(K2,J2),I2,
> > > >
> > > >
> > >
> IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
> > >
> e(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(con
> ca
> > > tenate(C2,E2)=concatenate(K2,J2),I2,
> > > >
> > > >
> > >
> IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
> > >
> e(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(con
> ca
> > > tenate(D2,C2)=concatenate(K2,J2),I2,
> > > >
> > > >
> > >
> IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
> > >
> e(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(con
> ca
> > > tenate(D2,E2)=concatenate(K2,J2),I2,
> > > >
> > > >
> > >
> IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
> > >
> e(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(con
> ca
> > > tenate(E2,C2)=concatenate(K2,J2),I2,
> > > >
> > > >
> > >
> IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
> > >
> e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > > > > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > > > > 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),CONCATEN
> > > > >
> > >
> ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> > > > >
> > >
> D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> > > > >
> > >
> ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATEN
> > > > > ATE(K2,J2)),I2,"")
> > > > > > >
> > > > > > > Thank you for your time
> > > > > > > Luke
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <Luke@discussions.microsoft.com> wrote in message
> > news:0590F416-29D7-4D84-AD90-658AB4DB0B25@microsoft.com...
> > > 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" <Luke@discussions.microsoft.com> wrote in message
> > > > news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> > > > > 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
> > > > atenate(C2,D2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
> > > >
> > e(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(con
> > ca
> > > > tenate(C2,E2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
> > > >
> > e(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(con
> > ca
> > > > tenate(D2,C2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
> > > >
> > e(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(con
> > ca
> > > > tenate(D2,E2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
> > > >
> > e(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(con
> > ca
> > > > tenate(E2,C2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
> > > >
> > e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > > > > > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > > > > > 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),CONCATEN
> > > > > >
> > > >
> > ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> > > > > >
> > > >
> > D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> > > > > >
> > > >
> > ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATEN
> > > > > > ATE(K2,J2)),I2,"")
> > > > > > > >
> > > > > > > > Thank you for your time
> > > > > > > > Luke
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" 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" <Luke@discussions.microsoft.com> wrote in message
> > news:0590F416-29D7-4D84-AD90-658AB4DB0B25@microsoft.com...
> > > 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" <Luke@discussions.microsoft.com> wrote in message
> > > > news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> > > > > 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
> > > > atenate(C2,D2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
> > > >
> > e(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(con
> > ca
> > > > tenate(C2,E2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
> > > >
> > e(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(con
> > ca
> > > > tenate(D2,C2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
> > > >
> > e(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(con
> > ca
> > > > tenate(D2,E2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
> > > >
> > e(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(con
> > ca
> > > > tenate(E2,C2)=concatenate(K2,J2),I2,
> > > > >
> > > > >
> > > >
> > IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
> > > >
> > e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > > > > > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > > > > > 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),CONCATEN
> > > > > >
> > > >
> > ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> > > > > >
> > > >
> > D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> > > > > >
> > > >
> > ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATEN
> > > > > > ATE(K2,J2)),I2,"")
> > > > > > > >
> > > > > > > > Thank you for your time
> > > > > > > > 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

"bj" wrote:

> 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" 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" <Luke@discussions.microsoft.com> wrote in message
> > > news:0590F416-29D7-4D84-AD90-658AB4DB0B25@microsoft.com...
> > > > 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" <Luke@discussions.microsoft.com> wrote in message
> > > > > news:C3F6206F-E529-4612-B1C1-2723E5DC9A7F@microsoft.com...
> > > > > > 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(concatenate(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)=concatenate(K2,I2),I2,IF(conc
> > > > > atenate(C2,D2)=concatenate(K2,J2),I2,
> > > > > >
> > > > > >
> > > > >
> > > IF(concatenate(C2,E2)=concatenate(I2,J2),I2,IF(concatenate(C2,E2)=concatenat
> > > > >
> > > e(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(con
> > > ca
> > > > > tenate(C2,E2)=concatenate(K2,J2),I2,
> > > > > >
> > > > > >
> > > > >
> > > IF(concatenate(D2,C2)=concatenate(I2,J2),I2,IF(concatenate(D2,C2)=concatenat
> > > > >
> > > e(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(con
> > > ca
> > > > > tenate(D2,C2)=concatenate(K2,J2),I2,
> > > > > >
> > > > > >
> > > > >
> > > IF(concatenate(D2,E2)=concatenate(I2,J2),I2,IF(concatenate(D2,E2)=concatenat
> > > > >
> > > e(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(con
> > > ca
> > > > > tenate(D2,E2)=concatenate(K2,J2),I2,
> > > > > >
> > > > > >
> > > > >
> > > IF(concatenate(E2,C2)=concatenate(I2,J2),I2,IF(concatenate(E2,C2)=concatenat
> > > > >
> > > e(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(con
> > > ca
> > > > > tenate(E2,C2)=concatenate(K2,J2),I2,
> > > > > >
> > > > > >
> > > > >
> > > IF(concatenate(E2,D2)=concatenate(I2,J2),I2,IF(concatenate(E2,D2)=concatenat
> > > > >
> > > e(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(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" <Luke@discussions.microsoft.com> wrote in message
> > > > > > > news:66FBBA05-F34C-4E6F-983E-E1DCD1D56A45@microsoft.com...
> > > > > > > > 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),CONCATEN
> > > > > > >
> > > > >
> > > ATE(I2,K2),CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(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),CONCATENATE(D2,C2),CONCATENATE(
> > > > > > >
> > > > >
> > > D2,E2),CONCATENATE(E2,C2),CONCATENATE(E2,D2))=OR(CONCATENATE(I2,J2),CONCATEN
> > > > > > >
> > > > >
> > > ATE(I2,K2)*CONCATENATE(J2,I2),CONCATENATE(J2,K2),CONCATENATE(K2,I2),CONCATEN
> > > > > > > ATE(K2,J2)),I2,"")
> > > > > > > > >
> > > > > > > > > Thank you for your time
> > > > > > > > > Luke
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >