Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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." |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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." |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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." |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing text within cells in two columns | Excel Worksheet Functions | |||
comparing cells in two columns ? | Excel Discussion (Misc queries) | |||
Sum product of many pairs of columns | Excel Worksheet Functions | |||
Removing Duplicate Pairs(2 Columns) | Excel Discussion (Misc queries) | |||
chart with stacked columns in pairs | Charts and Charting in Excel |