Comparing values in multiple columns
Closed
drsantoshh
Mazzaropi
- Posts
- 5
- Registration date
- Friday March 14, 2014
- Status
- Member
- Last seen
- March 15, 2014
Mazzaropi
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
Related:
- Comparing values in multiple columns
- Compare values in two columns and return the value from third ✓ - Forum - Excel
- Duplicate values in multiple columns ✓ - Forum - Excel
- Comparing dates in multiple columns and using one that matches ✓ - Forum - Excel
- Compare cell against multiple columns ✓ - Forum - Excel
- Comparing cells and in multiple columns ✓ - Forum - Excel
9 replies
Mazzaropi
Mar 14, 2014 at 08:07 AM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
Mar 14, 2014 at 08:07 AM
DrSantoshh, Good morning.
Suppose your data starts at A1.
Try to use:
E1 --> =AND(A1=C1,B1=D1)
The result will be only True or False.
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Suppose your data starts at A1.
Try to use:
E1 --> =AND(A1=C1,B1=D1)
The result will be only True or False.
Please, tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
drsantoshh
Mar 14, 2014 at 10:43 AM
- Posts
- 5
- Registration date
- Friday March 14, 2014
- Status
- Member
- Last seen
- March 15, 2014
Mar 14, 2014 at 10:43 AM
@Mazzaropi
sorry its not bringing what I want.
Thank you very much for your kind efforts.
My condition is like if value from column 1 is equal to value of column 3 (which is at any position) and corresponding value of column 2 is equal to value of column 4 (which may be at any position) then it should be true else false.
Regards,
DrSantoshh
sorry its not bringing what I want.
Thank you very much for your kind efforts.
My condition is like if value from column 1 is equal to value of column 3 (which is at any position) and corresponding value of column 2 is equal to value of column 4 (which may be at any position) then it should be true else false.
Regards,
DrSantoshh
Mazzaropi
Mar 14, 2014 at 11:14 AM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
Mar 14, 2014 at 11:14 AM
drsantoshh, Good morning.
Now your explanation was more precise. Thanks.
Try to use:
E1 --> =IFERROR(AND(MATCH(A1,$C$1:$C$6,0),MATCH(B1,$D$1:$D$6,0)),"FALSE")
Please, tell us if it worked for you.
I hope it helps.
Best regards.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Now your explanation was more precise. Thanks.
Try to use:
E1 --> =IFERROR(AND(MATCH(A1,$C$1:$C$6,0),MATCH(B1,$D$1:$D$6,0)),"FALSE")
Please, tell us if it worked for you.
I hope it helps.
Best regards.
--
Belo Horizonte, Brasil.
Marcílio Lobão
drsantoshh
Mar 14, 2014 at 11:46 AM
- Posts
- 5
- Registration date
- Friday March 14, 2014
- Status
- Member
- Last seen
- March 15, 2014
Mar 14, 2014 at 11:46 AM
Dear Mazzaropi
Thank you for your prompt reply..! :)
However still there are some issue.
at some times its giving correct result but some time no :(
here it is
1 A 4 B FALSE
2 B 5 D FALSE
3 C 6 A FALSE
4 B 7 F TRUE
5 D 8 F TRUE
6 E 9 B FALSE
7 F
8 D
9 B
10 A
here 4 has B on both side but result is showing as false. It should be true.
Same for 5D also. And showing wrong result for 8D and 8F as argument should be false and not true.
Hope this will explain.
Regards,
Dr.Santoshh
Thank you for your prompt reply..! :)
However still there are some issue.
at some times its giving correct result but some time no :(
here it is
1 A 4 B FALSE
2 B 5 D FALSE
3 C 6 A FALSE
4 B 7 F TRUE
5 D 8 F TRUE
6 E 9 B FALSE
7 F
8 D
9 B
10 A
here 4 has B on both side but result is showing as false. It should be true.
Same for 5D also. And showing wrong result for 8D and 8F as argument should be false and not true.
Hope this will explain.
Regards,
Dr.Santoshh
Didn't find the answer you are looking for?
Ask a question
Mazzaropi
Mar 14, 2014 at 01:24 PM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
Mar 14, 2014 at 01:24 PM
drsantoshh, Good afternoon.
I believe that you're having a wrong conclusion.
Your requirement:
"...My condition is like if value from column 1 is equal to value of column 3 (which is at any position) and corresponding value of column 2 is equal to value of column 4 (which may be at any position) then it should be true else false. ..."
The columns A and B are the arguments.(Main columns)
The columns C and D are only a reference or data bank.
The result TRUE or FALSE refers to your main columns of search.
Let's see:
1 A 4 B FALSE - It means that 1 and A don't exists at same time at the right side of table.
2 B 5 D FALSE - It means that 2 and B don't exists at same time at the right side of table.
3 C 6 A FALSE - It means that 3 and C don't exists at same time at the right side of table.
4 B 7 F TRUE.. - It means that 4 and B exists at same time at the right side of table.
5 D 8 F TRUE.. - It means that 5 and D exists at same time at the right side of table.
6 E 9 B FALSE - It means that 6 and E don't exists at same time at the right side of table.
7 F.......TRUE.. - It means that 7 and F exists at same time at the right side of table.
8 D.......TRUE.. - It means that 8 and D exists at same time at the right side of table.
9 B.......TRUE.. - It means that 9 and B exists at same time at the right side of table.
10 A.....FALSE - It means that 10 and A don't exists at same time at the right side of table.
Well, the formula does exactly what you asked for.
BUT....pay attention: Maybe you're looking for another thing.
Are you looking for, 1 and B, at the right side of the table, as your requirement, or are you looking for 1B?
To make things easier, I did an example for you.
http://speedy.sh/NZRrr/14-03-2014-en-Kioskea-Comparing-Columns.xlsx
If this isn't what you want, try to put the desirable result at the file manually and please, explain why this is the result.
I keep waiting for your answer.
Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
I believe that you're having a wrong conclusion.
Your requirement:
"...My condition is like if value from column 1 is equal to value of column 3 (which is at any position) and corresponding value of column 2 is equal to value of column 4 (which may be at any position) then it should be true else false. ..."
The columns A and B are the arguments.(Main columns)
The columns C and D are only a reference or data bank.
The result TRUE or FALSE refers to your main columns of search.
Let's see:
1 A 4 B FALSE - It means that 1 and A don't exists at same time at the right side of table.
2 B 5 D FALSE - It means that 2 and B don't exists at same time at the right side of table.
3 C 6 A FALSE - It means that 3 and C don't exists at same time at the right side of table.
4 B 7 F TRUE.. - It means that 4 and B exists at same time at the right side of table.
5 D 8 F TRUE.. - It means that 5 and D exists at same time at the right side of table.
6 E 9 B FALSE - It means that 6 and E don't exists at same time at the right side of table.
7 F.......TRUE.. - It means that 7 and F exists at same time at the right side of table.
8 D.......TRUE.. - It means that 8 and D exists at same time at the right side of table.
9 B.......TRUE.. - It means that 9 and B exists at same time at the right side of table.
10 A.....FALSE - It means that 10 and A don't exists at same time at the right side of table.
Well, the formula does exactly what you asked for.
BUT....pay attention: Maybe you're looking for another thing.
Are you looking for, 1 and B, at the right side of the table, as your requirement, or are you looking for 1B?
To make things easier, I did an example for you.
http://speedy.sh/NZRrr/14-03-2014-en-Kioskea-Comparing-Columns.xlsx
If this isn't what you want, try to put the desirable result at the file manually and please, explain why this is the result.
I keep waiting for your answer.
Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
drsantoshh
Mar 14, 2014 at 03:00 PM
- Posts
- 5
- Registration date
- Friday March 14, 2014
- Status
- Member
- Last seen
- March 15, 2014
Mar 14, 2014 at 03:00 PM
Hi
Column 1 Column 2 Column 3 Column 4
1 A 4 B
2 B 5 D
3 C 1 A
4 B 7 F
5 D 8 F
6 E 9 B
7 F
8 D
9 B
now in above example,
in column-1
Value 1 has corresponding value A which is there in column-3(position no.4) and column -4, so formula should turn to true if not then false.
eg. in column-1 value 8 has corresponding value D in column-2 which is not as corresponding value in column-3 for value 8 is F, so it should be false.
Hope I am clear..
Regards,
DrSantoshh
Column 1 Column 2 Column 3 Column 4
1 A 4 B
2 B 5 D
3 C 1 A
4 B 7 F
5 D 8 F
6 E 9 B
7 F
8 D
9 B
now in above example,
in column-1
Value 1 has corresponding value A which is there in column-3(position no.4) and column -4, so formula should turn to true if not then false.
eg. in column-1 value 8 has corresponding value D in column-2 which is not as corresponding value in column-3 for value 8 is F, so it should be false.
Hope I am clear..
Regards,
DrSantoshh
Mazzaropi
Mar 14, 2014 at 05:01 PM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
Mar 14, 2014 at 05:01 PM
drsantoshh, Good afternoon.
I did a new example for you.
http://speedy.sh/NZSSr/14-03-2014-en-Kioskea-Comparing-Columns-2.xlsx
Please, try next time save your file at a free site, www.speedyshare.com, and put a link to download here.
It's faster and more easier to help you.
I hope it helps.
Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
I did a new example for you.
http://speedy.sh/NZSSr/14-03-2014-en-Kioskea-Comparing-Columns-2.xlsx
Please, try next time save your file at a free site, www.speedyshare.com, and put a link to download here.
It's faster and more easier to help you.
I hope it helps.
Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão
drsantoshh
Mar 15, 2014 at 01:54 AM
- Posts
- 5
- Registration date
- Friday March 14, 2014
- Status
- Member
- Last seen
- March 15, 2014
Mar 15, 2014 at 01:54 AM
Dear Mazzaropi:
Yeppieeee..!
Thank you very much, this what, I wanted..!!!
It will be helpful for me if you explain it, in future I will do it independently..!
:)
Really thankful for your efforts..!
Much thanks for forum tooo...
Regards,
DrSantoshh
Yeppieeee..!
Thank you very much, this what, I wanted..!!!
It will be helpful for me if you explain it, in future I will do it independently..!
:)
Really thankful for your efforts..!
Much thanks for forum tooo...
Regards,
DrSantoshh
Mazzaropi
Mar 15, 2014 at 07:54 AM
- Posts
- 1963
- Registration date
- Monday August 16, 2010
- Status
- Contributor
- Last seen
- April 25, 2022
Mar 15, 2014 at 07:54 AM
DrSantoshh, Good morning.
I feel glad that we found a solution.
The solution is very simple.
The Formula:
=SEERRO(PROCV(A3;$C$3:$D$18;2;0)=B3;FALSO())
This function searchs for A3 (=1) - a number
at a $C$3:$D$18 - your data bank - a number
and brings the correspondent value found at second column. - a letter (=A)
=SEERRO(PROCV(A3;$C$3:$D$18;2;0)=B3;FALSO())
Then it compares the above result (=A) with the correspondent value of A3, that's B3, in this case (A).
As both, result of search (A) and B3 (A) are equal the answer is TRUE.
If not the answer will be FALSE.
I know it's very simple, but it works.
Have a nice weekend.
--
Belo Horizonte, Brasil.
Marcílio Lobão
I feel glad that we found a solution.
The solution is very simple.
The Formula:
=SEERRO(PROCV(A3;$C$3:$D$18;2;0)=B3;FALSO())
This function searchs for A3 (=1) - a number
at a $C$3:$D$18 - your data bank - a number
and brings the correspondent value found at second column. - a letter (=A)
=SEERRO(PROCV(A3;$C$3:$D$18;2;0)=B3;FALSO())
Then it compares the above result (=A) with the correspondent value of A3, that's B3, in this case (A).
As both, result of search (A) and B3 (A) are equal the answer is TRUE.
If not the answer will be FALSE.
I know it's very simple, but it works.
Have a nice weekend.
--
Belo Horizonte, Brasil.
Marcílio Lobão