Simplify formula

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.

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" <hrlngrv@aol.com> wrote in message
news:1115155194.143783.107820@o13g2000cwo.googlegroups.com...
> 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 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.

That's a lot more efficient than what I came up with and I got the same
results. However, according to the OP, the correct output should be:

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

The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and
this is where I'm stuck, the last 5 should not appear in column H.

Biff

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1115163829.327460.196040@g14g2000cwa.googlegroups.com...
> 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.
>

Stirred things up a bit it appears.
A question arises in that
in F2
if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
Shouldn't it also, in the same sentance say:
but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
Like wise in G2 and H2.
Just a thought here, but perhaps a way out of a good but kickin when it
comes to 455 444 senarios

Luke

"Biff" wrote:

> That's a lot more efficient than what I came up with and I got the same
> results. However, according to the OP, the correct output should be:
>
> _ | 8 | 8
> 1 | _ | 5
> _ | _ | _
> 8 | 6 | _
> 9 | 0 | 2
> _ | _ | _
> _ | _ | _
> 4 | 5 | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
>
> The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and
> this is where I'm stuck, the last 5 should not appear in column H.
>
> Biff
>
> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> news:1115163829.327460.196040@g14g2000cwa.googlegroups.com...
> > 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.
> >
>
>
>

If you only knew! <lol>

OK, I think I have it but I hope Harlan comes back with a more efficient
solution.

It's a 2 formula approach.

Enter this formula in F2 and copy to G2:

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

Enter this formula in H2:

=IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH(C2:E2,I2:K2,0))))>=2,COUNTIF($I2:$K2,E2)>0,COUNT(F2,G2)<COUNT(I2:K2)),E2,"")

Now, select the range F2, G2, H2 and copy down as needed.

Biff

"Luke" <Luke@discussions.microsoft.com> wrote in message
news:2FA0607B-1F27-4B7F-8D69-0B45EE5ACC87@microsoft.com...
> Stirred things up a bit it appears.
> A question arises in that
> in F2
> if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
> Shouldn't it also, in the same sentance say:
> but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
> I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
> Like wise in G2 and H2.
> Just a thought here, but perhaps a way out of a good but kickin when it
> comes to 455 444 senarios
>
> Luke
>
> "Biff" wrote:
>
>> That's a lot more efficient than what I came up with and I got the same
>> results. However, according to the OP, the correct output should be:
>>
>> _ | 8 | 8
>> 1 | _ | 5
>> _ | _ | _
>> 8 | 6 | _
>> 9 | 0 | 2
>> _ | _ | _
>> _ | _ | _
>> 4 | 5 | _
>> _ | _ | _
>> _ | _ | _
>> _ | _ | _
>> _ | _ | _
>> _ | _ | _
>> _ | _ | _
>>
>> The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally,
>> and
>> this is where I'm stuck, the last 5 should not appear in column H.
>>
>> Biff
>>
>> "Harlan Grove" <hrlngrv@aol.com> wrote in message
>> news:1115163829.327460.196040@g14g2000cwa.googlegroups.com...
>> > 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.
>> >
>>
>>
>>

"Biff" <biffinpitt@comcast.net> wrote...
....
>Enter this formula in F2 and copy to G2:
>
>=IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,
>$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH($C2:$E2,
>$I2:$K2,0))))>=2,COUNTIF($I2:$K2,C2)>0),C2,"")
>
>Enter this formula in H2:
>
>=IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,
>$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH(C2:E2,
>I2:K2,0))))>=2,COUNTIF($I2:$K2,E2)>0,
>COUNT(F2,G2)<COUNT(I2:K2)),E2,"")
....

Unfortunately, these formulas would give

5 | 9 | 5 || 5 | 9 | 5 || 5 | 9 | 9

when the results (between the ||s) should presumably be

5 | 9 | _

since there's only one 5 in the I:K cols.

Biff,
Works 98% with visible exceptions:
> 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
Most work great as above but then,
> 7 1 1 0 1 1 1
> 8 0 0 6 0 0 6 0
> 9 9 9 0 9 9 0 0 9
> 10 5 5 7 5 5 5 7
> 11 0 0 7 0 0 0
> 12 0 8 8 8 8
> 13 7 5 5 7 5 5 7 5 7
> 14 7 7 1 7 7 1 7
> 15 0 0 7 0 0 0
> 16 8 8 5 8 8 8 5
> 17 5 5 6 5 5 5 5
> 18 6 6 0 6 6 6
I'm adding this one because it worked as well:
> 19 9 9 9 9 9 9 9

Notice the way the doubles are typically showing in G & H and the 755
situation in line 13 (above example)... courious. These are the only type
situations that failed through hundreds of samples I ran.

This is great! Not sure if this is the response you could live without and
I'm not sure if I understand the formula you wrote but it is very very close
to done.
I would understand if you had enough but if you're into the challenge I'll
await your best shot... You guys rock!
Luke


"Biff" wrote:

> If you only knew! <lol>
>
> OK, I think I have it but I hope Harlan comes back with a more efficient
> solution.
>
> It's a 2 formula approach.
>
> Enter this formula in F2 and copy to G2:
>
> =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH($C2:$E2,$I2:$K2,0))))>=2,COUNTIF($I2:$K2,C2)>0),C2,"")
>
> Enter this formula in H2:
>
> =IF(AND(SUMPRODUCT(--(ISNUMBER(MATCH($I2:$K2,$C2:$E2,0))))>=2,SUMPRODUCT(--(ISNUMBER(MATCH(C2:E2,I2:K2,0))))>=2,COUNTIF($I2:$K2,E2)>0,COUNT(F2,G2)<COUNT(I2:K2)),E2,"")
>
> Now, select the range F2, G2, H2 and copy down as needed.
>
> Biff
>
> "Luke" <Luke@discussions.microsoft.com> wrote in message
> news:2FA0607B-1F27-4B7F-8D69-0B45EE5ACC87@microsoft.com...
> > Stirred things up a bit it appears.
> > A question arises in that
> > in F2
> > if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
> > Shouldn't it also, in the same sentance say:
> > but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
> > I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
> > Like wise in G2 and H2.
> > Just a thought here, but perhaps a way out of a good but kickin when it
> > comes to 455 444 senarios
> >
> > Luke
> >
> > "Biff" wrote:
> >
> >> That's a lot more efficient than what I came up with and I got the same
> >> results. However, according to the OP, the correct output should be:
> >>
> >> _ | 8 | 8
> >> 1 | _ | 5
> >> _ | _ | _
> >> 8 | 6 | _
> >> 9 | 0 | 2
> >> _ | _ | _
> >> _ | _ | _
> >> 4 | 5 | _
> >> _ | _ | _
> >> _ | _ | _
> >> _ | _ | _
> >> _ | _ | _
> >> _ | _ | _
> >> _ | _ | _
> >>
> >> The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally,
> >> and
> >> this is where I'm stuck, the last 5 should not appear in column H.
> >>
> >> Biff
> >>
> >> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> >> news:1115163829.327460.196040@g14g2000cwa.googlegroups.com...
> >> > 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.
> >> >
> >>
> >>
> >>
>
>
>

"Biff" <biffinpitt@comcast.net> wrote...
....
>However, according to the OP, the correct output should be:
>
> _ | 8 | 8
....
> 4 | 5 | _
....
>The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and
>this is where I'm stuck, the last 5 should not appear in column H.
....

vs

_ | 8 | 8
....
4 | 5 | 5

The records (cols C through K) with my results are

2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8
....
4 | 5 | 5 || 4 | 5 | 5 || _ | 4 | 5

and the records with the supposed desired results are

2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8
....
4 | 5 | 5 || 4 | 5 | _ || _ | 4 | 5


My question is what makes the 8s in D2 and E2 different from the 5s in D9
and E9? That there are two 8s in I2:K2 but only one 5 in I9:K9? If so,

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

Fill F2 into G2:H2, then fill F2:H2 into F3:H15. The results then look like

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

Harlan Grove,
I tried it and the returns in blank cells I,J,K were #DIV/0!. to clean it
up I added:
=IF(CONCATENATE($I7279,$J7279,$K7279)="","",IF(AND(ABS(COUNTIF($I7279:$K7279,C7279)/SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E7279))-0.5)<0.5,COUNTIF($C7279:C7279,C7279)<=COUNTIF($I7279:$K7279,C7279)),C7279,""))

I soon discovered some similar exceptions such as the last post (before this
one) I made to Biff. It seems if there are double numbers (ie 988) on the
left C,D,E and only one digit on the right in either I,J or K, it wants to
return one or two digits, usually in G and/or H.
You did eliminate the extra 5 that was appearing so not a total loss.
7 5 5 7 5 x 7 5 7
Perhaps a new function to introduce into excel would be =NOIFANDORBUTIF()
lol
Luke


"Harlan Grove" wrote:

> "Biff" <biffinpitt@comcast.net> wrote...
> ....
> >However, according to the OP, the correct output should be:
> >
> > _ | 8 | 8
> ....
> > 4 | 5 | _
> ....
> >The entries that are 4|5|5 and _|4|5 are kicking my butt! Specifcally, and
> >this is where I'm stuck, the last 5 should not appear in column H.
> ....
>
> vs
>
> _ | 8 | 8
> ....
> 4 | 5 | 5
>
> The records (cols C through K) with my results are
>
> 2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8
> ....
> 4 | 5 | 5 || 4 | 5 | 5 || _ | 4 | 5
>
> and the records with the supposed desired results are
>
> 2 | 8 | 8 || _ | 8 | 8 || 8 | _ | 8
> ....
> 4 | 5 | 5 || 4 | 5 | _ || _ | 4 | 5
>
>
> My question is what makes the 8s in D2 and E2 different from the 5s in D9
> and E9? That there are two 8s in I2:K2 but only one 5 in I9:K9? If so,
>
> F2:
> =IF(AND(ABS(COUNTIF($I2:$K2,C2)/
> SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2))-0.5)<0.5,
> COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")
>
> Fill F2 into G2:H2, then fill F2:H2 into F3:H15. The results then look like
>
> _ | 8 | 8
> 1 | _ | 5
> _ | _ | _
> 8 | 6 | _
> 9 | 0 | 2
> _ | _ | _
> _ | _ | _
> 4 | 5 | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
> _ | _ | _
>
>
>

"Luke" <Luke@discussions.microsoft.com> wrote...
>Harlan Grove,
>I tried it and the returns in blank cells I,J,K were #DIV/0!.
>to clean it up I added:
>=IF(CONCATENATE($I7279,$J7279,$K7279)="","",
>IF(AND(ABS(COUNTIF($I7279:$K7279,C7279)
>/SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E7279))-0.5)<0.5,
>COUNTIF($C7279:C7279,C7279)<=COUNTIF($I7279:$K7279,C7279)),C7279,""))

First, no one in their right mind uses the CONCATENATE function. They use
the & concatenation operator. Less typing, no wasted nested function call.

Second, it's unnecessary. Even if it were necessary to add an outer IF to
trap the condition that all cells in I#:K# were blank or empty, it'd make
more sense to use

=IF(COUNT(I#:K#)=0,"",...)

In this case, even that's unnecessary. In another branch of this thread I
changed the denominator to

MAX(1,SUMPRODUCT(COUNTIF($I7279:$K7279,$C7279:$E7279)))

which eliminates the #DIV/0! problem.

>I soon discovered some similar exceptions such as the last post
>(before this one) I made to Biff. It seems if there are double
>numbers (ie 988) on the left C,D,E and only one digit on the
>right in either I,J or K, it wants to return one or two digits,
>usually in G and/or H.
....

Yup, more stuff to trap.

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

Hi Harlan!

Tried your formula on the OP's sample data and it worked except for the
entry:

4 | 5 | 5................... _ | 4 | 5

When I tried it on larger random sets of numbers it didn't fare too well.
One thing that happened is when there were no matches in either range the
formula returned #DIV/0!

Biff

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1115155194.143783.107820@o13g2000cwo.googlegroups.com...
> 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.
>

"Biff" <biffinpitt@comcast.net> wrote...
>Tried your formula on the OP's sample data and it worked except for the
>entry:
>
>4 | 5 | 5................... _ | 4 | 5

Already dealt with in my revised formula.

>When I tried it on larger random sets of numbers it didn't fare too well.
>One thing that happened is when there were no matches in either range the
>formula returned #DIV/0!
....

OK, I need to revise my revised formula.

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

Harlan Grove,
I ran the formula thru hundreds of samples and results are similar to Biff's
formula in that when there ore double digits on the left and single digits on
the right, results are one digit showing in F,g or h. Such as:
> 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
the above works well, but double digits look like below:
> 7 3 5 5 5 5
> 8 8 9 9 9 9
> 9 2 6 6 6 6
> 10 1 4 1 1 1
> 11 5 1 5 5 5
> 12 6 8 8 8 8
> 13 3 3 9 3 3
> 14 8 8 1 8 8
> 15 0 0 7 0 0
> 16 2 6 2 2 2
> 17 9 2 9 9 9
> 18 4 4 8 4 4
Because there is only one digit on the right in I,J or K, the results seem
to follow suit so to speak.
Earlier I wrote:
A question arises in that
in F2
if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
Shouldn't it also, in the same sentance say:
but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
Like wise in G2 and H2.

I meant to say:
in F2,
if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or
J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2
in F,G or F,H or G,H
---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
in F,G or F,H or G,H --- which ever is the easiest to accomplish.

Eay for me to say huh... You guys are so close!!! The formula are way beyond
me that I am excited to see the final resulting formula. I hope you get paid
well.
Luke

"Harlan Grove" wrote:

> "Biff" <biffinpitt@comcast.net> wrote...
> >Tried your formula on the OP's sample data and it worked except for the
> >entry:
> >
> >4 | 5 | 5................... _ | 4 | 5
>
> Already dealt with in my revised formula.
>
> >When I tried it on larger random sets of numbers it didn't fare too well.
> >One thing that happened is when there were no matches in either range the
> >formula returned #DIV/0!
> ....
>
> OK, I need to revise my revised formula.
>
> F2:
> =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1,
> SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5,
> COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")
>
>
>

"Luke" <Luke@discussions.microsoft.com> wrote in message
news:02A136EA-32C2-4B29-9655-CB894C5FB84F@microsoft.com...
> I hope you get paid well.

Not sure if you were serious, but no one gets paid here. We are all
volunteers (or masochists!)

--

Vasant

Vasant,
You like what you do then... This is good! I volunteer full-time for the
Elma, WA Chamber of Commerce and I know your dedication.
Thank you for that.
Luke

"Vasant Nanavati" wrote:

> "Luke" <Luke@discussions.microsoft.com> wrote in message
> news:02A136EA-32C2-4B29-9655-CB894C5FB84F@microsoft.com...
> > I hope you get paid well.
>
> Not sure if you were serious, but no one gets paid here. We are all
> volunteers (or masochists!)
>
> --
>
> Vasant
>
>
>

Either you guys are still kicking this around or you don't want to see this
thread reach 40 posts lol Okay this isn't exactly the way I thought this
would go but accomplishes the goal in a round about way.

I added 3 columns moving contents of CDE over to IJK and the contents of IJK
over to LMN leaving CDE blank.
I then placed this formula in C and filled over to D & C and filled down:

=IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I2,$K2)="","",IF(CONCATENATE($J2,$I2)="","",IF(CONCATENATE($J2,$K2)="","",IF(CONCATENATE($K2,$I2)="","",IF(CONCATENATE($K2,$J2)="","",J2))))))

In I2, I put in Harlan's Formula and filled over to J & K then filled down:

=IF(AND(ABS(COUNTIF($L2:$N2,C2)/MAX(1,SUMPRODUCT(COUNTIF($L2:$N2,$C2:$E2)))-0.5)<0.5,COUNTIF($C2:C2,C2)<=COUNTIF($L2:$N2,C2)),C2,"")

What my formula did was eliminate single digits that Harlans formula
returned. I couldn't do that with Biff's as he had the double digits going
on and it was, well, kicking my butt. This avenue is cumbersome but it
succeeds.

Then, I was going too see if you guys could incorporate my formula into
Harlan's. But just as I was about to post this, I decided to try and
replaced Harlan's formula with mine. after doing so, I then I got rid of the
three columns (LMN) that I added and courious enough, the disired results
were revealed and there was the formula I was looking for. Not so cumbersome
now.

I find it strange that it fell this way. Thanks to you guys... problem
solved.
I do have Question though. How is the formula I built in relationship with
the ones you guys built?
I'm going to rate this one "DONE" only because if it weren't for your
efforts I might not have seen the light. Thank you deeply for helping me...
You guys rock!

I'll watch for posts to see if you have an answer to that question.
Thanks again
Luke



"Luke" wrote:

> Harlan Grove,
> I ran the formula thru hundreds of samples and results are similar to Biff's
> formula in that when there ore double digits on the left and single digits on
> the right, results are one digit showing in F,g or h. Such as:
> > 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
> the above works well, but double digits look like below:
> > 7 3 5 5 5 5
> > 8 8 9 9 9 9
> > 9 2 6 6 6 6
> > 10 1 4 1 1 1
> > 11 5 1 5 5 5
> > 12 6 8 8 8 8
> > 13 3 3 9 3 3
> > 14 8 8 1 8 8
> > 15 0 0 7 0 0
> > 16 2 6 2 2 2
> > 17 9 2 9 9 9
> > 18 4 4 8 4 4
> Because there is only one digit on the right in I,J or K, the results seem
> to follow suit so to speak.
> Earlier I wrote:
> A question arises in that
> in F2
> if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
> Shouldn't it also, in the same sentance say:
> but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
> I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
> Like wise in G2 and H2.
>
> I meant to say:
> in F2,
> if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
> and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or
> J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2
> in F,G or F,H or G,H
> ---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
> in F,G or F,H or G,H --- which ever is the easiest to accomplish.
>
> Eay for me to say huh... You guys are so close!!! The formula are way beyond
> me that I am excited to see the final resulting formula. I hope you get paid
> well.
> Luke
>
> "Harlan Grove" wrote:
>
> > "Biff" <biffinpitt@comcast.net> wrote...
> > >Tried your formula on the OP's sample data and it worked except for the
> > >entry:
> > >
> > >4 | 5 | 5................... _ | 4 | 5
> >
> > Already dealt with in my revised formula.
> >
> > >When I tried it on larger random sets of numbers it didn't fare too well.
> > >One thing that happened is when there were no matches in either range the
> > >formula returned #DIV/0!
> > ....
> >
> > OK, I need to revise my revised formula.
> >
> > F2:
> > =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1,
> > SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5,
> > COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")
> >
> >
> >

"Luke" <Luke@discussions.microsoft.com> wrote...
>Either you guys are still kicking this around or you don't want to see this
>thread reach 40 posts lol Okay this isn't exactly the way I thought
this
>would go but accomplishes the goal in a round about way.
....

You missed one of my earlier responses. See

http://groups-beta.google.com/group/...e=source&hl=en

(or http://makeashorterlink.com/?B5C51270B ).

Harlan! How the heck did I miss that!?
Thank you for pointing that out... Then I dub yours the NOIFANDORBUTIF()
formula
Thanks!
Luke

"Harlan Grove" wrote:

> "Luke" <Luke@discussions.microsoft.com> wrote...
> >Either you guys are still kicking this around or you don't want to see this
> >thread reach 40 posts lol Okay this isn't exactly the way I thought
> this
> >would go but accomplishes the goal in a round about way.
> ....
>
> You missed one of my earlier responses. See
>
> http://groups-beta.google.com/group/...e=source&hl=en
>
> (or http://makeashorterlink.com/?B5C51270B ).
>
>
>

P.S., I dub this formula the =NOIFANDORBUTIF() formula
Luke

"Luke" wrote:

> Either you guys are still kicking this around or you don't want to see this
> thread reach 40 posts lol Okay this isn't exactly the way I thought this
> would go but accomplishes the goal in a round about way.
>
> I added 3 columns moving contents of CDE over to IJK and the contents of IJK
> over to LMN leaving CDE blank.
> I then placed this formula in C and filled over to D & C and filled down:
>
> =IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I2,$K2)="","",IF(CONCATENATE($J2,$I2)="","",IF(CONCATENATE($J2,$K2)="","",IF(CONCATENATE($K2,$I2)="","",IF(CONCATENATE($K2,$J2)="","",J2))))))
>
> In I2, I put in Harlan's Formula and filled over to J & K then filled down:
>
> =IF(AND(ABS(COUNTIF($L2:$N2,C2)/MAX(1,SUMPRODUCT(COUNTIF($L2:$N2,$C2:$E2)))-0.5)<0.5,COUNTIF($C2:C2,C2)<=COUNTIF($L2:$N2,C2)),C2,"")
>
> What my formula did was eliminate single digits that Harlans formula
> returned. I couldn't do that with Biff's as he had the double digits going
> on and it was, well, kicking my butt. This avenue is cumbersome but it
> succeeds.
>
> Then, I was going too see if you guys could incorporate my formula into
> Harlan's. But just as I was about to post this, I decided to try and
> replaced Harlan's formula with mine. after doing so, I then I got rid of the
> three columns (LMN) that I added and courious enough, the disired results
> were revealed and there was the formula I was looking for. Not so cumbersome
> now.
>
> I find it strange that it fell this way. Thanks to you guys... problem
> solved.
> I do have Question though. How is the formula I built in relationship with
> the ones you guys built?
> I'm going to rate this one "DONE" only because if it weren't for your
> efforts I might not have seen the light. Thank you deeply for helping me...
> You guys rock!
>
> I'll watch for posts to see if you have an answer to that question.
> Thanks again
> Luke
>
>
>
> "Luke" wrote:
>
> > Harlan Grove,
> > I ran the formula thru hundreds of samples and results are similar to Biff's
> > formula in that when there ore double digits on the left and single digits on
> > the right, results are one digit showing in F,g or h. Such as:
> > > 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
> > the above works well, but double digits look like below:
> > > 7 3 5 5 5 5
> > > 8 8 9 9 9 9
> > > 9 2 6 6 6 6
> > > 10 1 4 1 1 1
> > > 11 5 1 5 5 5
> > > 12 6 8 8 8 8
> > > 13 3 3 9 3 3
> > > 14 8 8 1 8 8
> > > 15 0 0 7 0 0
> > > 16 2 6 2 2 2
> > > 17 9 2 9 9 9
> > > 18 4 4 8 4 4
> > Because there is only one digit on the right in I,J or K, the results seem
> > to follow suit so to speak.
> > Earlier I wrote:
> > A question arises in that
> > in F2
> > if C2,D2 & C2,E2 are = to either I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2
> > Shouldn't it also, in the same sentance say:
> > but only if I2>"" or J2>"" OR K2>"", OTHERWISE display the contents of
> > I2,J2 OR J2,I2 OR J2,K2 OR K2,I2 OR K2,J2...?
> > Like wise in G2 and H2.
> >
> > I meant to say:
> > in F2,
> > if C2,D2 & C2,E2 are = to either I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
> > and if either of I2,J2 or K2 are ="", but, the remaining of I2,J2 or J2,I2 or
> > J2,K2 or K2,I2 or K2,J2 are still =C2,D2 or C2,E2, then return C2,D2 or C2,E2
> > in F,G or F,H or G,H
> > ---OR just return the contents of I2,J2 or J2,I2 or J2,K2 or K2,I2 or K2,J2
> > in F,G or F,H or G,H --- which ever is the easiest to accomplish.
> >
> > Eay for me to say huh... You guys are so close!!! The formula are way beyond
> > me that I am excited to see the final resulting formula. I hope you get paid
> > well.
> > Luke
> >
> > "Harlan Grove" wrote:
> >
> > > "Biff" <biffinpitt@comcast.net> wrote...
> > > >Tried your formula on the OP's sample data and it worked except for the
> > > >entry:
> > > >
> > > >4 | 5 | 5................... _ | 4 | 5
> > >
> > > Already dealt with in my revised formula.
> > >
> > > >When I tried it on larger random sets of numbers it didn't fare too well.
> > > >One thing that happened is when there were no matches in either range the
> > > >formula returned #DIV/0!
> > > ....
> > >
> > > OK, I need to revise my revised formula.
> > >
> > > F2:
> > > =IF(AND(ABS(COUNTIF($I2:$K2,C2)/MAX(1,
> > > SUMPRODUCT(COUNTIF($I2:$K2,$C2:$E2)))-0.5)<0.5,
> > > COUNTIF($C2:C2,C2)<=COUNTIF($I2:$K2,C2)),C2,"")
> > >
> > >
> > >

"Luke" <Luke@discussions.microsoft.com> wrote...
>P.S., I dub this formula the =NOIFANDORBUTIF() formula
....
>>=IF(CONCATENATE($I2,$J2)="","",IF(CONCATENATE($I2,$K2)="","",
>>IF(CONCATENATE($J2,$I2)="","",IF(CONCATENATE($J2,$K2)="","",
>>IF(CONCATENATE($K2,$I2)="","",IF(CONCATENATE($K2,$J2)="","",J2))))))
....

The only way I2&J2 = "" is if both I2 and J2 are "" or blank. If so, then
J2&I2 would also be "". If not, neither would J2&I2 be "". Ditto I2&K2 and
J2&K2 and their reversed pairs. So no point to including the reversed pairs.

Indeed, you could simplify to

=IF(COUNTBLANK($I2:$K2)<=1,J2,"")