Simplify formula

[Luke]

Pardon the new thread about the same thing I posted earlier. I would like to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
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
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two or more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or more
digits from C,D & E that are equal to two or more digits from I,J & K then
display contents of J in G. The only thing in the formula that chages is that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits from
C,D & E that are equal to two or more digits from I,J & K then display
contents of K in H. The only thing in the formula that chages is that instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same formula
could be pasted in G & H columns repectively-- If it were in simpler format.

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

Thank you for your time and toleration.
Luke

 

[Biff]

Hi!

Based on your explanation of what you want and comparing that to your table,
I can't see this being done!

Shouldn't F2 = 8 (I2), G2 = "" (J2), H2 = 8 (K2)

Why would F5 = 6 (I5) and F2 not = 8 (I2)

Biff

 

[Luke]

This is hard to keep straight and I realize as I keep trying to prevail, the
responses I get have showed me my own errors.
I did say that I2 is relevant to F2. I should have said F2 is relevant to
C2 so barring any further revelations, keep in mind that it doesn't matter
how the digits in columns I,J & K are aranged. Here is a Corrected Table:

A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 1 5 9 5 1
4 8 3 4 5 2 4
5 8 6 7 8 6 6 2 8
6 9 0 2 9 0 2 2 9 0

F2 is relevant only to C2 and only those 6 different combinations in I2, J2
& K2.
So, in cell F2, =IF(contatenate(C2,D2) or if(concatenate(C2,E2) is equal to
any given concatenated pair of (I2, J2 & K2) then C2, otherwise ""). By
Concatenated pairs I mean:
In cell F2;
IF concatenate(C2,D2)=
concatenate(I2,J2) or
concatenate(I2,K2) or
concatenate(J2,I2) or
concatenate(J2,K2) or
concatenate(K2,I2) or
concatenate(K2,J2) then C2 otherwise
IF concatenate(C2,E2)=
concatenate(I2,J2) or
concatenate(I2,K2) or
concatenate(J2,I2) or
concatenate(J2,K2) or
concatenate(K2,I2) or
concatenate(K2,J2) then C2,""))
Therefore, concatenate(C2,D2)=28 and concatenate(C2,E2)=28 and there is no
"2" in I2, J2 or K2.

Likewise:
G2 is relevant only to D2 and only those 6 different combinations in I2, J2
& K2. So, in F2, =IF(contatenate(D2,C2) or if(concatenate(D2,E2) is equal to
any given concatenated pair of I2, J2 & K2 then D2 otherwise "".
Therefore, concatenate(D2,C2)=82 (no "2" in I,J or K) and
concatenate(D2,E2)=88 which is equal to one of the 'any given combinations of
I2,J2 & K2', in this case happens to be concatenate(I2,K2)=88 so G2 would
show the content of D2=8

There has to be a way to do this little puzzle. I hope this time I have made
sense accurately. If I have articulated this even remotely close to showing
what I would like to have happen in F,G & H then I think I have accomplished
something in that alone but I would like to succeed.
Thank you,
Luke

 

[Biff]

Ok, we're making progress!

So, if F2 references C2

and G2 references D2

then H2 must reference E2

In F2 enter this formula and copy across to H2 then down:

=IF(AND(SUMPRODUCT(COUNTIF($C2:$E2,$I2:$K2))>=2,COUNTIF($I2:$K2,C2)>0),C2,"")

Will return:

F........G........H
............8.........8
1....................5
.........................
8.........6...........
9.........0.........2

Biff

 

[Vasant Nanavati]

Not that easy . I've been working at this off and on for the last couple
of days.

Your formula's result doesn't change even if there is only 1 "8" on the
right side in the top row. My understanding is that if there are 2 "8"s on
the left side, there have to be 2 on the right as well. It's possible to
test for this but it gives rise to a host of other complications.

 

[Biff]

Not that easy . I've been working at this off and on for the last couple

of days

Yeah, but with each new post the desired outcome changes!

I'll take another look!

Biff

 

[Luke]

Thank you Biff that was the ticket formula... and you thought I would change
it again on this post
I really do appreciate all of you that helped me on this. I thought maybe
you guys got bored because I kept changing it on you in the middle of the
stream. Thanks for hanging in there!!!
I've said it before and I'm saying it again... YOU GUY ARE THE BEST.

BTW Vasant, it's apparently all to do with the relevance of C2 to F2, D2 to
G2, & E2 to H2 as Biff mentioned. The I,J & K columns are seemingly
irrelevent to position in the rows. Strange but the formula works thus far
and rest assured that if it fails anywhere in my quest... I'll Be Back
Luke

 

[Luke]

Okay I you a pat on the back and now I think maybe Vasant had a point. Let
me show you the errors that appeared after futher review:
A B C D E F G H I J K
1
2 2 8 8 8 8 8 8
3 1 0 5 1 5 9 5 1
4 8 3 4 5 2 4
5 8 6 7 8 6 6 2 8
6 9 0 2 9 0 2 2 9 0
7 0 4 1 4 4
8 6 2 6 2 2 2
9 4 5 5 4 5 5 4 5
10 3 6 9 6 6 6
11 5 0 1 1 1
12 0 0 5 5 5
13 9 2 4 2 2 2
14 2 6 9 9 9 9
15 1 4 9 1 1 1
This is weird in that why would that formula work for most situations but
error on others? I keep reverting back to the very long formula I mentioned
before where, in F2,
IF the concatenated pairs C2,D2 or, C2,E2 are = to any one of the 6 possible
concatenated pairs of I2,J2 & K2, then return C2, otherwise "".

I wonder though if we are not considering this:
in F2,
IF the concatenated pairs C2,D2 or, D2,C2 or, C2,E2 or, E2,C2 are = to any
one of the 6 possible concatenated pairs of I2,J2 & K2, then return C2,
otherwise "".

I'll keep chugging... Appologies for saying that it worked before I ran
through the master sheet. I've been using an abreviated sheet and only tried
the formula there.
Thanks again Biff It is closer than I've been able to do and I will try and
understand the formula so that I might find a solution.
Luke

 

[Biff]

Luke,

As you discovered my formula does not work as Vasant pointed out. I
obviously didn't test it enough!

Just hang in there. "We'll" figure it out!

Biff

 

[Luke]

Thank you guys, I'll check back periodically to this post. Meanwhile I will
also keep on it.
Luke

 

[Biff]

Luke,

Question:

If:

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5............5................................................................4...........5

What results would you expect in F2, G2 and H2?

Biff

 

[Luke]

Biff,
I want to say that 5 could show in either G2 or H2 as long as only one "5"
shows. But then I start thinking relevance and my logic says that:
C2 is to F2 & I2 as F2 is to C2, But that starts to be a far stretch when
you consider the possibilities.
Therefore I think that the latter, 5 shows in either G2 or H2, may be the
better way to look at it in terms of formulas. As long as the return is only
the 4 and the 5, I don't see where that it would matter either way.

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5..........4........................5.......................4..........5

I hope this helps
Luke

 

[bj]

Im starting out far again since I cant see names any more

try in F
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G
=(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2=K2,E2=I2,E2=J2,E2=K2)),D2,""))
and in H
=(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2=J2,C2=K2)),E2,""))

A simpler, to me, description of what you want would be
If c2 is the same as either I,J,or K2 and if either D2 or E2 is the same as
I,J,or K2 then make F2 = C2
If D2 is the same as either I,J,or K2 and if either C2 or E2 is the same as
I,J,or K2 then make G2 = D2
If E2 is the same as either I,J,or K2 and if either D2 or C2 is the same as
I,J,or K2 then make H2 = E2

If I am wrong please leet me know.

 

[Luke]

bj,
It didn't work but don't discourage... The main thing to consider is:
in F2
if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three
digits of I2, J2 or K2, then F2, otherwise "".

in G2
Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three
digits of I2, J2 or K2, then G2, otherwise "".

in H2
Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three digits
of I2, J2 or K2, then F2, otherwise "".

In the case where two digits are the same in either C2, D2 or E2, the result
in F,G & H would look something like this:

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K
4............5...........5..........4........................5.......................4..........5

OR

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K
4............5...........5..........4............5...................................4..........5

As long as the return is only the one 4 and one 5, I don't see that it would
matter if 5 showed up in G or H either way.

To clarify, the same thing in different arrangements would look like:

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K
5............4...........5..........5............4...................................4..........5
O
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K
4............5...........5.......................5...........5...........5......................5
O
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K
5............5...........4..........5........................4...........5......................4
OR if all three digits are on the righ
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K
4............5...........5..........4............5..........5...........5..........4..........5
OR if all three digits in C,D,E are the sam
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........5...........Nothing is
returned......................4..........5
O
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K
5............5...........5..........5............5.......................5..........4...........5
O
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K
5............5...........5..........5........................5...........5..........4...........5
Does that make since?
Thank you bj
I will work with your formula to see if I can help.
Luke

 

[bj]

from this last example, it appears there is an additional constraint in that
once a cell is used, It cannot be used again, since both G2 and do not equal 5

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2,E2=K2)),D2,"")))
and in H2
=if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and(or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2=J2,C2=K2)),E2,""))))

This still may not do what you want depending

should empty cells be used in concatinations? in other words does ("", 1,3)
give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"]

If the blank cells are not to be used, It complicates things dramatically.
Would it be possible to put an X or some other character in either, but not
both, the IJK or the CDE columns for unused cells? if the answer is no,
then the equations will probably double or triple in size. and it woul
dprobably be worth going to a UDF.

 

[Biff]

Hi!

Those don't work!

I'm stuck on a "minor" detail! The logic needed to satisfy one condition
causes the logic needed for the next condition to fail!

This is much more complicated than it appears!

Biff

from this last example, it appears there is an additional constraint in
that
once a cell is used, It cannot be used again, since both G2 and do not
equal 5

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2,E2=K2)),D2,"")))
and in H2
=if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and(or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2=J2,C2=K2)),E2,""))))

This still may not do what you want depending

should empty cells be used in concatinations? in other words does ("",
1,3)
give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"]

If the blank cells are not to be used, It complicates things
dramatically.
Would it be possible to put an X or some other character in either, but
not
both, the IJK or the CDE columns for unused cells? if the answer is no,
then the equations will probably double or triple in size. and it woul
dprobably be worth going to a UDF.

bj,
It didn't work but don't discourage... The main thing to consider is:
in F2
if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three
digits of I2, J2 or K2, then F2, otherwise "".

in G2
Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three
digits of I2, J2 or K2, then G2, otherwise "".

in H2
Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three
digits
of I2, J2 or K2, then F2, otherwise "".

In the case where two digits are the same in either C2, D2 or E2, the
result
in F,G & H would look something like this:

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5..........4........................5.......................4..........5

OR

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5..........4............5...................................4..........5

As long as the return is only the one 4 and one 5, I don't see that it
would
matter if 5 showed up in G or H either way.

To clarify, the same thing in different arrangements would look like:

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............4...........5..........5............4...................................4..........5
OR
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5.......................5...........5...........5......................5
OR
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........4..........5........................4...........5......................4
OR if all three digits are on the right
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5..........4............5..........5...........5..........4..........5
OR if all three digits in C,D,E are the same
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........5...........Nothing is
returned......................4..........5
Or
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........5..........5............5.......................5..........4...........5
Or
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........5..........5........................5...........5..........4...........5
Does that make since?
Thank you bj
I will work with your formula to see if I can help.
Luke



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

 

[bj]

I may have typed something wrong (I do most of the time).
This worked on the examples I had. Can you show me where the logic fails so
that hopefully, I can do a "mea culpa, I meant to type it this way" ?
(hopefully it won't be the "Mea culpa, I don't know how to fix it" response)

Hi!

Those don't work!

I'm stuck on a "minor" detail! The logic needed to satisfy one condition
causes the logic needed for the next condition to fail!

This is much more complicated than it appears!

Biff

from this last example, it appears there is an additional constraint in
that
once a cell is used, It cannot be used again, since both G2 and do not
equal 5

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=if(and(D2=C2,countif(I2:k2,d2)<2),"",(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,C2=J2,C2=K2,E2=I2,E2=J2,E2=K2)),D2,"")))
and in H2
=if(and(E2=C2,E2=D2,countif(I2:K2,E2)<3,"",if(and(or(E2=C2,E2=D2),Countif(I2:K2,E2)<2),"",(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2=J2,C2=K2)),E2,""))))

This still may not do what you want depending

should empty cells be used in concatinations? in other words does ("",
1,3)
give 2 concatinations[13,31] or 6 [''1,''3,13,31,3",1"]

If the blank cells are not to be used, It complicates things
dramatically.
Would it be possible to put an X or some other character in either, but
not
both, the IJK or the CDE columns for unused cells? if the answer is no,
then the equations will probably double or triple in size. and it woul
dprobably be worth going to a UDF.

bj,
It didn't work but don't discourage... The main thing to consider is:
in F2
if Concatenate(C2,D2) OR Concatenate(C2,E2) is equal to any two or three
digits of I2, J2 or K2, then F2, otherwise "".

in G2
Concatenate(D2,C2) OR Concatenate(D2,E2) is equal to any two or three
digits of I2, J2 or K2, then G2, otherwise "".

in H2
Concatenate(E2,C2) OR Concatenate(E2,D2) is equal to any two or three
digits
of I2, J2 or K2, then F2, otherwise "".

In the case where two digits are the same in either C2, D2 or E2, the
result
in F,G & H would look something like this:

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5..........4........................5.......................4..........5

OR

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5..........4............5...................................4..........5

As long as the return is only the one 4 and one 5, I don't see that it
would
matter if 5 showed up in G or H either way.

To clarify, the same thing in different arrangements would look like:

C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............4...........5..........5............4...................................4..........5
OR
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5.......................5...........5...........5......................5
OR
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........4..........5........................4...........5......................4
OR if all three digits are on the right
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
4............5...........5..........4............5..........5...........5..........4..........5
OR if all three digits in C,D,E are the same
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........5...........Nothing is
returned......................4..........5
Or
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........5..........5............5.......................5..........4...........5
Or
C2.........D2.........E2.........F2.........G2.........H2.........I2.........J2.........K2
5............5...........5..........5........................5...........5..........4...........5
Does that make since?
Thank you bj
I will work with your formula to see if I can help.
Luke


:

Im starting out far again since I cant see names any more

try in F2
=(IF(AND(OR(C2=I1,C2=J1,C2=K1),OR(D2=I2,D2=J2,D2=K2,E2=I2,E2=J2,E2=K2)),C2,""))
and in G2
=(IF(AND(OR(D2=I2,D2=J2,D2=K2),OR(C2=I2,CD2=J2,CD2=K2,E2=I2,E2=J2,E2=K2)),D2,""))
and in H2
=(IF(AND(OR(E2=I2,E2=J2,E2=K2),OR(D2=I2,D2=J2,D2=K2,C2=I2,C2=J2,C2=K2)),E2,""))

A simpler, to me, description of what you want would be
If c2 is the same as either I,J,or K2 and if either D2 or E2 is the
same as
I,J,or K2 then make F2 = C2
If D2 is the same as either I,J,or K2 and if either C2 or E2 is the
same as
I,J,or K2 then make G2 = D2
If E2 is the same as either I,J,or K2 and if either D2 or C2 is the
same as
I,J,or K2 then make H2 = E2

If I am wrong please leet me know.



:

Pardon the new thread about the same thing I posted earlier. I would
like to
clean up the mistakes I made in my earlier quest.
In it's raw form my setup looks like this:
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
6 9 0 2 2 9 0 2 9 0
I need a more simple formula that will perform the following task.
The following formula Below, is referencing Column F in that if any
combination of two or more digits from C,D & E that are equal to two
or more
digits from I,J & K then display contents of I in F.

The same formula would go for G in that any combination of two or
more
digits from C,D & E that are equal to two or more digits from I,J & K
then
display contents of J in G. The only thing in the formula that chages
is that
instead of showing I in F, it would show J in G

The same would go for G in that any combination of two or more digits
from
C,D & E that are equal to two or more digits from I,J & K then
display
contents of K in H. The only thing in the formula that chages is that
instead
of showing I or J in H, it would show K in H

Here is my version of the Formula for column F only... the same
formula
could be pasted in G & H columns repectively-- If it were in simpler
format.

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

Thank you for your time and toleration.
Luke

 

[Harlan Grove]

Luke wrote...

Pardon the new thread about the same thing I posted earlier. I would
like to clean up the mistakes I made in my earlier quest. In it's raw
form my setup looks like this:

....

Supplementing your original example with the additional examples in
rows 7 to 15 you provided in a follow-up, it looks like C2:E15
contains

2 | 8 | 8
1 | 0 | 5
8 | 3 | 4
8 | 6 | 7
9 | 0 | 2
0 | 4 | 1
6 | 2 | 6
4 | 5 | 5
3 | 6 | 9
5 | 0 | 1
0 | 0 | 5
9 | 2 | 4
2 | 6 | 9
1 | 4 | 9

and I2:K15 contains

8 | _ | 8
9 | 5 | 1
5 | 2 | 4
6 | 2 | 8
2 | 9 | _
_ | _ | 4
2 | _ | 2
_ | 4 | 5
6 | _ | 6
1 | _ | _
5 | _ | _
2 | 2 | _
_ | 9 | 9
1 | 1 | _

where underscores represent blank cells. If so, and given your
specifications are refiend in your follow-up messages, try the
following formula in cell F2.

F2:
=IF(ABS(COUNTIF($I2:$K2,C2)/
SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,C2,"")

Fill F2 right into G2:H2, then select F2:H2 and fill down into
F3:H15. This results in the following in F2:H15.

_ | 8 | 8
1 | _ | 5
_ | _ | _
8 | 6 | _
9 | 0 | 2
_ | _ | _
_ | _ | _
4 | 5 | 5
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _

again with underscores representing blanks.

 

[Vasant Nanavati]

Harlan does it again <yawn>.

Seriously, that's brilliant. And you still say there's nothing original in
these NGs? <g>

(Still trying to figure out how it works ...)

Regards,

Vasant

 

[Harlan Grove]

Vasant Nanavati wrote...
....

. . . And you still say there's nothing original in these NGs?

....

Nope. The ABS(x-m)<w/2 idiom has been mentioned before for testing
whether x falls between m-w/2 and m+w/2 without having to calculate m
twice, and the ratio term is akin to the standard way of counting
distinct entries in a range containing duplicates.

The gist is that each of C, D or E must account for some but not all of
the total count of all C:E in I:K.