Finding a duplicate in multiple columns with varying responses
- Thread starter pr4peace
- Start date
The Doctor
Member
I just discovered this post, and I'm trying to use a slightly modified version of this formula. I've got a named range 2 columns wide for job name and site name. On another page I've got a list of cancelled sites with job numbers. I'm using your formula to try to indicate when I've got a job and site that appear on both lists. I've modified your formula thus:
=LOOKUP("11",DEC2BIN(MMULT((sitedata=$C2:$D2)*{2,1},{1;1}),2))
but, it doesn't show me a result of 11 indicating matches. I know that there are matches because I've ensured that I have duplicates. This formula is going into a helper column, so I can run an if statement providing status:
=if(a2=11,"Site Reissued","Cancelled")
What did I do wrong with your formula that it can't find duplicates?
=LOOKUP("11",DEC2BIN(MMULT((sitedata=$C2:$D2)*{2,1},{1;1}),2))
but, it doesn't show me a result of 11 indicating matches. I know that there are matches because I've ensured that I have duplicates. This formula is going into a helper column, so I can run an if statement providing status:
=if(a2=11,"Site Reissued","Cancelled")
What did I do wrong with your formula that it can't find duplicates?
NARAYANK991
Excel Ninja
Hi ,
I am sorry I cannot help you ; the problem is that in order to help you , I have to understand this topic , understand how your data is organized , and then see why Sajan's formula does not work for you. I think it is too much of trouble.
If you can upload your workbook , it will make it easier.
Narayan
I am sorry I cannot help you ; the problem is that in order to help you , I have to understand this topic , understand how your data is organized , and then see why Sajan's formula does not work for you. I think it is too much of trouble.
If you can upload your workbook , it will make it easier.
Narayan
The Doctor
Member
Here is a dummy file with sensitive data removed.
https://www.dropbox.com/s/vsh0laeyn5eqyqa/example%20problem.xlsx
https://www.dropbox.com/s/vsh0laeyn5eqyqa/example%20problem.xlsx
NARAYANK991
Excel Ninja
Hi ,
I downloaded your file , and would like to understand your exact requirement ; can you please explain what you would like to do ?
The Excel help on the LOOKUP function says :
[pre]
[/pre]
If we take this to be true , then using MMULT and DEC2BIN cannot really guarantee that the resulting column vector will be in ascending order. I don't really want to troubleshoot this further.
My point is that the original problem might have required the DEC2BIN for some purpose ; I can think of only one reason for using DEC2BIN ; it tells you the columns which match and those which don't e.g. if you get a binary pattern such as 1011 , then it means there are 3 columns which match and one which doesn't ; if we count the columns from the left , then the second column from the left does not match.
If you don't have this requirement , and you only want to know whether all the columns match , then it is far simpler to check for this e.g. if there are only 2 columns involved , check for the value 3 , for 3 columns check for 7 , for 4 columns check for 15 and so on.
Rewriting your formula as :
=ISNUMBER(MATCH(3,MMULT((sitedata=$C2:$D2)*{2,1},{1;1}),0))
will return TRUE if there is a duplicate , and FALSE otherwise.
Narayan
I downloaded your file , and would like to understand your exact requirement ; can you please explain what you would like to do ?
The Excel help on the LOOKUP function says :
[pre]
Code:
lookup_vector Required. A range that contains only one row or one column. The values in
lookup_vector can be text, numbers, or logical values.
Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ...,
A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value.If we take this to be true , then using MMULT and DEC2BIN cannot really guarantee that the resulting column vector will be in ascending order. I don't really want to troubleshoot this further.
My point is that the original problem might have required the DEC2BIN for some purpose ; I can think of only one reason for using DEC2BIN ; it tells you the columns which match and those which don't e.g. if you get a binary pattern such as 1011 , then it means there are 3 columns which match and one which doesn't ; if we count the columns from the left , then the second column from the left does not match.
If you don't have this requirement , and you only want to know whether all the columns match , then it is far simpler to check for this e.g. if there are only 2 columns involved , check for the value 3 , for 3 columns check for 7 , for 4 columns check for 15 and so on.
Rewriting your formula as :
=ISNUMBER(MATCH(3,MMULT((sitedata=$C2:$D2)*{2,1},{1;1}),0))
will return TRUE if there is a duplicate , and FALSE otherwise.
Narayan
The Doctor
Member
Looks like your formula works, thanks for simplifying it all for me. On behalf of my management I thank you.