Simplify formula

[Biff]

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

 

[Luke]

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]

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

 

[Harlan Grove]

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]

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]

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]

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]

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

 

[Luke]

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]

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

 

[Luke]

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

 

[Vasant Nanavati]

I hope you get paid well.

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

 

[Luke]

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

 

[Luke]

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

 

[Harlan Grove]

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...tions/msg/39a6f58e868c9a67?dmode=source&hl=en

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

 

[Luke]

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

 

[Luke]

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]

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

....

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