Comparing pairs of cells in four columns
 

I am trying to compare four columns of text, say A-D, such that if the paired
combination of cells A2 and B2 can be found as a combination of the same pair
text ANYWHERE in columns C and D, I would get "true". I.e., I want to search
for all the A+B combos in corresponding C+D columns
example:

A B C D E
1 oak tree bird dog true
2 gold brick oak brick false
3 dumb bunny oak tree false

The process found the combination for A1+B1 in C3+D3; I would want to do
the same for gold+brick and dumb+bunny, etc.etc. Geez I'm worn out trying
different combos of "logic."

Comparing pairs of cells in four columns
 

In E1:
=ISNUMBER(MATCH(1,(A1=$C$1:$C$100)*(B1=$D$1:$D$100 ),0))
and drag down

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

brents18 wrote:

I am trying to compare four columns of text, say A-D, such that if the paired
combination of cells A2 and B2 can be found as a combination of the same pair
text ANYWHERE in columns C and D, I would get "true". I.e., I want to search
for all the A+B combos in corresponding C+D columns
example:

A B C D E
1 oak tree bird dog true
2 gold brick oak brick false
3 dumb bunny oak tree false

The process found the combination for A1+B1 in C3+D3; I would want to do
the same for gold+brick and dumb+bunny, etc.etc. Geez I'm worn out trying
different combos of "logic."

--

Dave Peterson

Comparing pairs of cells in four columns
 

Dave, thanks. It almost worked. However, for some reason, I get only "0"
(false) though I know there to be "true" combinations present. Does it
matter what the formatting is in the cells (i.e., general, number, etc.)?
When you say "you can only use the whole column in xl2007" do you mean I have
to have xl2007 to do what you illustrated below, or that I must select the
entire column(s) to perform the function?
Brent

"Dave Peterson" wrote:

In E1:
=ISNUMBER(MATCH(1,(A1=$C$1:$C$100)*(B1=$D$1:$D$100 ),0))
and drag down

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

brents18 wrote:

I am trying to compare four columns of text, say A-D, such that if the paired
combination of cells A2 and B2 can be found as a combination of the same pair
text ANYWHERE in columns C and D, I would get "true". I.e., I want to search
for all the A+B combos in corresponding C+D columns
example:

A B C D E
1 oak tree bird dog true
2 gold brick oak brick false
3 dumb bunny oak tree false

The process found the combination for A1+B1 in C3+D3; I would want to do
the same for gold+brick and dumb+bunny, etc.etc. Geez I'm worn out trying
different combos of "logic."

--

Dave Peterson

Comparing pairs of cells in four columns
 

The easy followups first.

#1. In xl2003 and below, you can't use a formula like:
=ISNUMBER(MATCH(1,(A1=$C:$C)*(B1=$D:$D),0))
You have to limit it to a range of rows that is less than the full column.

#2. To get 0 or 1 returned, I'm guessing that you have a Lotus 123 setting
enabled.
Tools|Options|Transition tab|Uncheck "Transition formula evaluation"

Unless you have some overwhelming reason to have any of these options checked,
I'd turn them all off. If you're coming from a Lotus 123 environment, you may
have a reason--if you're a typical excel user, I'd turn them off.

#3a. Are you sure you hit ctrl-shift-enter when you array-entered the formula?

#3b. Formatting won't matter -- but values will.

Pick out a row where you think that the values in A## and B## match (Say C?? and
D??).

Find a pair of empty cells and try:
=a##=c?? (like =a17=C22)
and
=b##=d?? (like B17=D22)

I'm betting that you see at least one false. It could be that you have
leading/trailing spaces in one (or 2 or 3 or all 4) cells.

You can fix the data (maybe using a simple edit|replace if there are not
supposed to be spaces in any cell) or you can adjust the formula

=ISNUMBER(MATCH(1,(trim(A1)=trim($C$1:$C$100))*(tr im(B1)=trim($D$1:$D$100)),0))
Still array-entered (Ctrl-shift-enter)

I'm guessing that 3a was the solution.

Was I right?


brents18 wrote:

Dave, thanks. It almost worked. However, for some reason, I get only "0"
(false) though I know there to be "true" combinations present. Does it
matter what the formatting is in the cells (i.e., general, number, etc.)?
When you say "you can only use the whole column in xl2007" do you mean I have
to have xl2007 to do what you illustrated below, or that I must select the
entire column(s) to perform the function?
Brent

"Dave Peterson" wrote:

In E1:
=ISNUMBER(MATCH(1,(A1=$C$1:$C$100)*(B1=$D$1:$D$100 ),0))
and drag down

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

brents18 wrote:

I am trying to compare four columns of text, say A-D, such that if the paired
combination of cells A2 and B2 can be found as a combination of the same pair
text ANYWHERE in columns C and D, I would get "true". I.e., I want to search
for all the A+B combos in corresponding C+D columns
example:

A B C D E
1 oak tree bird dog true
2 gold brick oak brick false
3 dumb bunny oak tree false

The process found the combination for A1+B1 in C3+D3; I would want to do
the same for gold+brick and dumb+bunny, etc.etc. Geez I'm worn out trying
different combos of "logic."

--

Dave Peterson


--

Dave Peterson

Comparing pairs of cells in four columns
 

Try this:

=ISNUMBER(MATCH(A1&B1,INDEX($C$1:$C$3&$D$1:$D$3,0) ,0))

Just normal enter
adjust your range to suit


"brents18" wrote:

I am trying to compare four columns of text, say A-D, such that if the paired
combination of cells A2 and B2 can be found as a combination of the same pair
text ANYWHERE in columns C and D, I would get "true". I.e., I want to search
for all the A+B combos in corresponding C+D columns
example:

A B C D E
1 oak tree bird dog true
2 gold brick oak brick false
3 dumb bunny oak tree false

The process found the combination for A1+B1 in C3+D3; I would want to do
the same for gold+brick and dumb+bunny, etc.etc. Geez I'm worn out trying
different combos of "logic."

Comparing pairs of cells in four columns
 

Another way...

=SUMPRODUCT(--($C$1:$C$3=A1),--($D$1:$D$3=B1))0
copy down


"brents18" wrote:

I am trying to compare four columns of text, say A-D, such that if the paired
combination of cells A2 and B2 can be found as a combination of the same pair
text ANYWHERE in columns C and D, I would get "true". I.e., I want to search
for all the A+B combos in corresponding C+D columns
example:

A B C D E
1 oak tree bird dog true
2 gold brick oak brick false
3 dumb bunny oak tree false

The process found the combination for A1+B1 in C3+D3; I would want to do
the same for gold+brick and dumb+bunny, etc.etc. Geez I'm worn out trying
different combos of "logic."

Comparing pairs of cells in four columns
 

Dave, I think I have it now. For some reason, when I entered the formula, xl
automatically placed " around the "Match" argument. When I got rid of those
the routine seems to work well. Thanks so much.
Brent

"Dave Peterson" wrote:

The easy followups first.

#1. In xl2003 and below, you can't use a formula like:
=ISNUMBER(MATCH(1,(A1=$C:$C)*(B1=$D:$D),0))
You have to limit it to a range of rows that is less than the full column.

#2. To get 0 or 1 returned, I'm guessing that you have a Lotus 123 setting
enabled.
Tools|Options|Transition tab|Uncheck "Transition formula evaluation"

Unless you have some overwhelming reason to have any of these options checked,
I'd turn them all off. If you're coming from a Lotus 123 environment, you may
have a reason--if you're a typical excel user, I'd turn them off.

#3a. Are you sure you hit ctrl-shift-enter when you array-entered the formula?

#3b. Formatting won't matter -- but values will.

Pick out a row where you think that the values in A## and B## match (Say C?? and
D??).

Find a pair of empty cells and try:
=a##=c?? (like =a17=C22)
and
=b##=d?? (like B17=D22)

I'm betting that you see at least one false. It could be that you have
leading/trailing spaces in one (or 2 or 3 or all 4) cells.

You can fix the data (maybe using a simple edit|replace if there are not
supposed to be spaces in any cell) or you can adjust the formula

=ISNUMBER(MATCH(1,(trim(A1)=trim($C$1:$C$100))*(tr im(B1)=trim($D$1:$D$100)),0))
Still array-entered (Ctrl-shift-enter)

I'm guessing that 3a was the solution.

Was I right?


brents18 wrote:

Dave, thanks. It almost worked. However, for some reason, I get only "0"
(false) though I know there to be "true" combinations present. Does it
matter what the formatting is in the cells (i.e., general, number, etc.)?
When you say "you can only use the whole column in xl2007" do you mean I have
to have xl2007 to do what you illustrated below, or that I must select the
entire column(s) to perform the function?
Brent

"Dave Peterson" wrote:

In E1:
=ISNUMBER(MATCH(1,(A1=$C$1:$C$100)*(B1=$D$1:$D$100 ),0))
and drag down

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

brents18 wrote:

I am trying to compare four columns of text, say A-D, such that if the paired
combination of cells A2 and B2 can be found as a combination of the same pair
text ANYWHERE in columns C and D, I would get "true". I.e., I want to search
for all the A+B combos in corresponding C+D columns
example:

A B C D E
1 oak tree bird dog true
2 gold brick oak brick false
3 dumb bunny oak tree false

The process found the combination for A1+B1 in C3+D3; I would want to do
the same for gold+brick and dumb+bunny, etc.etc. Geez I'm worn out trying
different combos of "logic."

--

Dave Peterson


--

Dave Peterson

Comparing pairs of cells in four columns
 

You may want to look at TM's second suggestion (using =sumproduct()).

It may be easier to type.

brents18 wrote:

Dave, I think I have it now. For some reason, when I entered the formula, xl
automatically placed " around the "Match" argument. When I got rid of those
the routine seems to work well. Thanks so much.
Brent

"Dave Peterson" wrote:

The easy followups first.

#1. In xl2003 and below, you can't use a formula like:
=ISNUMBER(MATCH(1,(A1=$C:$C)*(B1=$D:$D),0))
You have to limit it to a range of rows that is less than the full column.

#2. To get 0 or 1 returned, I'm guessing that you have a Lotus 123 setting
enabled.
Tools|Options|Transition tab|Uncheck "Transition formula evaluation"

Unless you have some overwhelming reason to have any of these options checked,
I'd turn them all off. If you're coming from a Lotus 123 environment, you may
have a reason--if you're a typical excel user, I'd turn them off.

#3a. Are you sure you hit ctrl-shift-enter when you array-entered the formula?

#3b. Formatting won't matter -- but values will.

Pick out a row where you think that the values in A## and B## match (Say C?? and
D??).

Find a pair of empty cells and try:
=a##=c?? (like =a17=C22)
and
=b##=d?? (like B17=D22)

I'm betting that you see at least one false. It could be that you have
leading/trailing spaces in one (or 2 or 3 or all 4) cells.

You can fix the data (maybe using a simple edit|replace if there are not
supposed to be spaces in any cell) or you can adjust the formula

=ISNUMBER(MATCH(1,(trim(A1)=trim($C$1:$C$100))*(tr im(B1)=trim($D$1:$D$100)),0))
Still array-entered (Ctrl-shift-enter)

I'm guessing that 3a was the solution.

Was I right?


brents18 wrote:

Dave, thanks. It almost worked. However, for some reason, I get only "0"
(false) though I know there to be "true" combinations present. Does it
matter what the formatting is in the cells (i.e., general, number, etc.)?
When you say "you can only use the whole column in xl2007" do you mean I have
to have xl2007 to do what you illustrated below, or that I must select the
entire column(s) to perform the function?
Brent

"Dave Peterson" wrote:

In E1:
=ISNUMBER(MATCH(1,(A1=$C$1:$C$100)*(B1=$D$1:$D$100 ),0))
and drag down

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

brents18 wrote:

I am trying to compare four columns of text, say A-D, such that if the paired
combination of cells A2 and B2 can be found as a combination of the same pair
text ANYWHERE in columns C and D, I would get "true". I.e., I want to search
for all the A+B combos in corresponding C+D columns
example:

A B C D E
1 oak tree bird dog true
2 gold brick oak brick false
3 dumb bunny oak tree false

The process found the combination for A1+B1 in C3+D3; I would want to do
the same for gold+brick and dumb+bunny, etc.etc. Geez I'm worn out trying
different combos of "logic."

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson