Simplify formula

B

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
 
G

Guest

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
 
B

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
 
H

Harlan Grove

Biff said:
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.
Click to expand...
....

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 | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
_ | _ | _
 
H

Harlan Grove

Biff said:
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,"")
Click to expand...
....

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.
 
B

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
 
H

Harlan Grove

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

4 | 5 | 5................... _ | 4 | 5
Click to expand...

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!
Click to expand...
....

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

Guest

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
Click to expand...
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
Click to expand...
I'm adding this one because it worked as well:
19 9 9 9 9 9 9 9
Click to expand...

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
 
G

Guest

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
 
H

Harlan Grove

Luke said:
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,""))
Click to expand...

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.
Click to expand...
....

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

Guest

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
Click to expand...
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
Click to expand...
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
 
G

Guest

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
 
G

Guest

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
 
G

Guest

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

Harlan Grove

Luke said:
P.S., I dub this formula the =NOIFANDORBUTIF() formula :) ....
Click to expand...
....

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

simplify function 13
Easier way to concatenate a row? 8
HELP! I need to make an IF Statement comparing date/time fields.. 2
Unnecessary Space 2
Percentage Formula 2
Fun with SUMPRODUCT 14
Change formulas in a column based on a selection. 3
Need help with concatenate formula 4

Top