|
Comparing two columns
I have a spreadsheet where I need to compare two columns and get a true,
false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
Maybe I don't understand well enough, but it looks to me like this would work
(change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
This works perfectly! One more step, if possible?
Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
Have you got another spare column hanging around? I'd have to use some VB
coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
The UDF method: copy the code below into a code module in the workbook. To
do so, press [Alt]+[F11] to get into the VB Editor, choose Insert | Module from its menu and copy and paste the code into that module and close the VB Editor. To use it, in a cell use a formula like: =ReportDuplicates(D1) to find duplicates of the value in D1 over in column J or =ReportDuplicates(J5) to find duplicates of value in J5 over in column D Function ReportDuplicates(anyCell As Range) As String Dim tmpResult As String Dim rngSearch As Range Dim anySearchCell As Object Dim searchColumn As String Application.Volatile If anyCell.Column = Range("D1").Column Then 'looking for dupes in column J based on value 'in column D searchColumn = "J" ElseIf anyCell.Column = Range("J1").Column Then 'must be looking for dupes in column D based 'on value in column J searchColumn = "D" Else 'but if not in J (and wasn't in D) then do nothing ReportDuplicates = "" Exit Function End If Set rngSearch = Range(searchColumn & "1:" & searchColumn _ & Range(searchColumn & Rows.Count).End(xlUp).Row) For Each anySearchCell In rngSearch If anySearchCell.Value = anyCell.Value Then tmpResult = tmpResult & " " & anySearchCell.Address End If Next If Len(tmpResult) = 0 Then ReportDuplicates = "No Duplicates" Else ReportDuplicates = "Value " & anyCell.Value & _ " in " & anyCell.Address & " duplicated at: " & tmpResult End If End Function "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
Let me give you the entire picture (at least as far as I received
instructions!). First, I have to compare columns D and J (and columns J and D). I have created columns K and L with the formulas as follows: Column K: =COUNTIF($J$2:$J$457,D2)0 Column L: =COUNTIF($D$2:$D$457,J2)0 Then, I have added column M, with the formula to determine if either columns K and L are true: Column M: =OR(K2,L2) Second, I have to compare columns A and G (and columns G and A). I have created columns N and O with the formulas as follows: Column N: =COUNTIF($A$2:$A$457,G2)0 Column O: =COUNTIF($G$2:$G$457,A2)0 Then, I have added Column P, with the formula to determine if either columns N and O are true: Column P: =OR(N2,O2) The end result would be if Column D has any duplicates in Column J, then show the rows where the duplicate exists. If Column J has any duplicates in Column D, then show the rows were the duplicate exists. If Column D has no duplicates AND Column A and G are equal, do not show this on the report. If Column D has no duplicates AND Column A and G are not equal, show this on the report. So, I guess I need code to add to another column that would show the rows where the duplicates exist? -- S "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
And here is the double-click method. When you double-click in a cell in D or
J, you get message telling where the value in that cell is duplicated in the other column. To put this code in the right place, right-click on the sheet's name tab and choose [View Code] from the popup list. Copy and paste the code into the module provided. Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) Dim tmpResult As String Dim rngSearch As Range Dim anySearchCell As Object Dim searchColumn As String Dim ReportDuplicates As String Application.Volatile If Target.Column = Range("D1").Column Then 'looking for dupes in column J based on value 'in column D searchColumn = "J" ElseIf Target.Column = Range("J1").Column Then 'must be looking for dupes in column D based 'on value in column J searchColumn = "D" Else 'but if not in J (and wasn't in D) then do nothing Exit Sub End If Cancel = True ' negate the double-click action Set rngSearch = Range(searchColumn & "1:" & searchColumn _ & Range(searchColumn & Rows.Count).End(xlUp).Row) For Each anySearchCell In rngSearch If anySearchCell.Value = Target.Value Then tmpResult = tmpResult & " " & anySearchCell.Address End If Next If Len(tmpResult) = 0 Then MsgBox "No Duplicates" Else MsgBox "Value " & Target.Value & _ " in " & Target.Address & " duplicated at: " & tmpResult End If End Sub "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
Well, I'm about to get really confused! I need a little clarification here.
Where you say "do not show this on the report" and "show this on the report" - exactly what does "the report" mean? Are you talking about the reporting of duplicate row numbers for the D/J column test results? "Sharon" wrote: Let me give you the entire picture (at least as far as I received instructions!). First, I have to compare columns D and J (and columns J and D). I have created columns K and L with the formulas as follows: Column K: =COUNTIF($J$2:$J$457,D2)0 Column L: =COUNTIF($D$2:$D$457,J2)0 Then, I have added column M, with the formula to determine if either columns K and L are true: Column M: =OR(K2,L2) Second, I have to compare columns A and G (and columns G and A). I have created columns N and O with the formulas as follows: Column N: =COUNTIF($A$2:$A$457,G2)0 Column O: =COUNTIF($G$2:$G$457,A2)0 Then, I have added Column P, with the formula to determine if either columns N and O are true: Column P: =OR(N2,O2) The end result would be if Column D has any duplicates in Column J, then show the rows where the duplicate exists. If Column J has any duplicates in Column D, then show the rows were the duplicate exists. If Column D has no duplicates AND Column A and G are equal, do not show this on the report. If Column D has no duplicates AND Column A and G are not equal, show this on the report. So, I guess I need code to add to another column that would show the rows where the duplicates exist? -- S "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
P.S. - this really IS the last 'step' right? It is much easier to develop a
solution given all the pieces/requirements up front than to continually backstep and try to modify/patch things to work with 'next step's unless those steps are known up front. It's tough to hit a rapidly moving target. "Sharon" wrote: Let me give you the entire picture (at least as far as I received instructions!). First, I have to compare columns D and J (and columns J and D). I have created columns K and L with the formulas as follows: Column K: =COUNTIF($J$2:$J$457,D2)0 Column L: =COUNTIF($D$2:$D$457,J2)0 Then, I have added column M, with the formula to determine if either columns K and L are true: Column M: =OR(K2,L2) Second, I have to compare columns A and G (and columns G and A). I have created columns N and O with the formulas as follows: Column N: =COUNTIF($A$2:$A$457,G2)0 Column O: =COUNTIF($G$2:$G$457,A2)0 Then, I have added Column P, with the formula to determine if either columns N and O are true: Column P: =OR(N2,O2) The end result would be if Column D has any duplicates in Column J, then show the rows where the duplicate exists. If Column J has any duplicates in Column D, then show the rows were the duplicate exists. If Column D has no duplicates AND Column A and G are equal, do not show this on the report. If Column D has no duplicates AND Column A and G are not equal, show this on the report. So, I guess I need code to add to another column that would show the rows where the duplicates exist? -- S "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
Sometimes seeing is easier than telling, so to help with that I've made some
changes based on your new data in column N, O and P and put it all into a workbook which you can download from: http://www.jlathamsite.com/uploads/for_Sharon_v001.xls just click the link and choose Save and save to your hard drive. It does include the UDF code, which it also uses. Here's what I did, label for column Q (in Q1): Rows where D is duplicated in J at Q2 I put in formula: =reportduplicates(D2) label for column R (in R1): Rows where J is duplicated in D in R2 I put in formula: =reportduplicates(J2) label for column S (in S1): Value in D has no duplicates in J, BUT A does not equal G then in S2 I put in formula: =IF(AND(K2=FALSE,A2<G2),reportduplicates(D2),"") then I filled those on down the sheet to get comparisons at each row. I'm hoping I got the logic the way you needed, or if I didn't that you can take what's available and provided as samples, to set up the logic you actually need. "Sharon" wrote: Let me give you the entire picture (at least as far as I received instructions!). First, I have to compare columns D and J (and columns J and D). I have created columns K and L with the formulas as follows: Column K: =COUNTIF($J$2:$J$457,D2)0 Column L: =COUNTIF($D$2:$D$457,J2)0 Then, I have added column M, with the formula to determine if either columns K and L are true: Column M: =OR(K2,L2) Second, I have to compare columns A and G (and columns G and A). I have created columns N and O with the formulas as follows: Column N: =COUNTIF($A$2:$A$457,G2)0 Column O: =COUNTIF($G$2:$G$457,A2)0 Then, I have added Column P, with the formula to determine if either columns N and O are true: Column P: =OR(N2,O2) The end result would be if Column D has any duplicates in Column J, then show the rows where the duplicate exists. If Column J has any duplicates in Column D, then show the rows were the duplicate exists. If Column D has no duplicates AND Column A and G are equal, do not show this on the report. If Column D has no duplicates AND Column A and G are not equal, show this on the report. So, I guess I need code to add to another column that would show the rows where the duplicates exist? -- S "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
You are brilliant!!! You have gone above and beyond what I expected, but I
couldn't have done it without you! Thank you so much. -- S "JLatham" wrote: And here is the double-click method. When you double-click in a cell in D or J, you get message telling where the value in that cell is duplicated in the other column. To put this code in the right place, right-click on the sheet's name tab and choose [View Code] from the popup list. Copy and paste the code into the module provided. Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) Dim tmpResult As String Dim rngSearch As Range Dim anySearchCell As Object Dim searchColumn As String Dim ReportDuplicates As String Application.Volatile If Target.Column = Range("D1").Column Then 'looking for dupes in column J based on value 'in column D searchColumn = "J" ElseIf Target.Column = Range("J1").Column Then 'must be looking for dupes in column D based 'on value in column J searchColumn = "D" Else 'but if not in J (and wasn't in D) then do nothing Exit Sub End If Cancel = True ' negate the double-click action Set rngSearch = Range(searchColumn & "1:" & searchColumn _ & Range(searchColumn & Rows.Count).End(xlUp).Row) For Each anySearchCell In rngSearch If anySearchCell.Value = Target.Value Then tmpResult = tmpResult & " " & anySearchCell.Address End If Next If Len(tmpResult) = 0 Then MsgBox "No Duplicates" Else MsgBox "Value " & Target.Value & _ " in " & Target.Address & " duplicated at: " & tmpResult End If End Sub "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
Thanks very much.
Did you get the workbook/formulas from one of my later previous posts: http://www.microsoft.com/office/comm...d-a9297f84ba90 ?? "Sharon" wrote: You are brilliant!!! You have gone above and beyond what I expected, but I couldn't have done it without you! Thank you so much. -- S "JLatham" wrote: And here is the double-click method. When you double-click in a cell in D or J, you get message telling where the value in that cell is duplicated in the other column. To put this code in the right place, right-click on the sheet's name tab and choose [View Code] from the popup list. Copy and paste the code into the module provided. Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) Dim tmpResult As String Dim rngSearch As Range Dim anySearchCell As Object Dim searchColumn As String Dim ReportDuplicates As String Application.Volatile If Target.Column = Range("D1").Column Then 'looking for dupes in column J based on value 'in column D searchColumn = "J" ElseIf Target.Column = Range("J1").Column Then 'must be looking for dupes in column D based 'on value in column J searchColumn = "D" Else 'but if not in J (and wasn't in D) then do nothing Exit Sub End If Cancel = True ' negate the double-click action Set rngSearch = Range(searchColumn & "1:" & searchColumn _ & Range(searchColumn & Rows.Count).End(xlUp).Row) For Each anySearchCell In rngSearch If anySearchCell.Value = Target.Value Then tmpResult = tmpResult & " " & anySearchCell.Address End If Next If Len(tmpResult) = 0 Then MsgBox "No Duplicates" Else MsgBox "Value " & Target.Value & _ " in " & Target.Address & " duplicated at: " & tmpResult End If End Sub "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
Yes, I did. Thank you so much!
-- S "JLatham" wrote: Thanks very much. Did you get the workbook/formulas from one of my later previous posts: http://www.microsoft.com/office/comm...d-a9297f84ba90 ?? "Sharon" wrote: You are brilliant!!! You have gone above and beyond what I expected, but I couldn't have done it without you! Thank you so much. -- S "JLatham" wrote: And here is the double-click method. When you double-click in a cell in D or J, you get message telling where the value in that cell is duplicated in the other column. To put this code in the right place, right-click on the sheet's name tab and choose [View Code] from the popup list. Copy and paste the code into the module provided. Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) Dim tmpResult As String Dim rngSearch As Range Dim anySearchCell As Object Dim searchColumn As String Dim ReportDuplicates As String Application.Volatile If Target.Column = Range("D1").Column Then 'looking for dupes in column J based on value 'in column D searchColumn = "J" ElseIf Target.Column = Range("J1").Column Then 'must be looking for dupes in column D based 'on value in column J searchColumn = "D" Else 'but if not in J (and wasn't in D) then do nothing Exit Sub End If Cancel = True ' negate the double-click action Set rngSearch = Range(searchColumn & "1:" & searchColumn _ & Range(searchColumn & Rows.Count).End(xlUp).Row) For Each anySearchCell In rngSearch If anySearchCell.Value = Target.Value Then tmpResult = tmpResult & " " & anySearchCell.Address End If Next If Len(tmpResult) = 0 Then MsgBox "No Duplicates" Else MsgBox "Value " & Target.Value & _ " in " & Target.Address & " duplicated at: " & tmpResult End If End Sub "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
Comparing two columns
Great. Enjoy!
"Sharon" wrote: Yes, I did. Thank you so much! -- S "JLatham" wrote: Thanks very much. Did you get the workbook/formulas from one of my later previous posts: http://www.microsoft.com/office/comm...d-a9297f84ba90 ?? "Sharon" wrote: You are brilliant!!! You have gone above and beyond what I expected, but I couldn't have done it without you! Thank you so much. -- S "JLatham" wrote: And here is the double-click method. When you double-click in a cell in D or J, you get message telling where the value in that cell is duplicated in the other column. To put this code in the right place, right-click on the sheet's name tab and choose [View Code] from the popup list. Copy and paste the code into the module provided. Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) Dim tmpResult As String Dim rngSearch As Range Dim anySearchCell As Object Dim searchColumn As String Dim ReportDuplicates As String Application.Volatile If Target.Column = Range("D1").Column Then 'looking for dupes in column J based on value 'in column D searchColumn = "J" ElseIf Target.Column = Range("J1").Column Then 'must be looking for dupes in column D based 'on value in column J searchColumn = "D" Else 'but if not in J (and wasn't in D) then do nothing Exit Sub End If Cancel = True ' negate the double-click action Set rngSearch = Range(searchColumn & "1:" & searchColumn _ & Range(searchColumn & Rows.Count).End(xlUp).Row) For Each anySearchCell In rngSearch If anySearchCell.Value = Target.Value Then tmpResult = tmpResult & " " & anySearchCell.Address End If Next If Len(tmpResult) = 0 Then MsgBox "No Duplicates" Else MsgBox "Value " & Target.Value & _ " in " & Target.Address & " duplicated at: " & tmpResult End If End Sub "JLatham" wrote: Have you got another spare column hanging around? I'd have to use some VB coding to find exact location of multiple duplicates and report them. This could be in the form of a user defined function (UDF) that could be used like a formula in a cell, or it could be written to respond to a double-click on a cell in column D (or J or both) to report duplicates in the other columns via a message when a cell in one of those columns is double-clicked. "Sharon" wrote: This works perfectly! One more step, if possible? Is there anyway to find out which rows have the duplicate values? For instance, if column D2 has a value and there is a duplicate in J6, J7, J8, is there a way to have it list the cells where the duplicate is located? I appreciate your help. -- S "JLatham" wrote: Maybe I don't understand well enough, but it looks to me like this would work (change ranges if necessary) in first cell in K: =COUNTIF($J$2:$J$457,D2)0 That will give you TRUE if value in D2 appears anywhere in J2:J456 in first cell in L: =COUNTIF($D$2:$D$457,J2)0 again, if value in J2 appears anywhere in D2:D457, will return TRUE finally in first cell in L: =OR(K2,L2) Will return TRUE if only K2 is True or only L2 is true or both are True: it only returns FALSE when both K2 and L2 are false. "Sharon" wrote: I have a spreadsheet where I need to compare two columns and get a true, false value. Column D Column J I need to compare column D to column J: I used the following in column K: =ISNA(MATCH(D2,$J$2:$J$457,FALSE)) to compare J to D in column L: =ISNA(MATCH(J2,$D$2:$D$457,FALSE)) I can see that the first one is incorrect because there is a duplicate value in D2 and J6. Then, I want to have a column where the value returns true if column K or L is true: =IF(OR(K2=TRUE,L2=TRUE),TRUE) I tried th =VLOOKUP(D2,'Sheet 1'!$J$2:$J$457,2,FALSE) which I got from another post. Can someone please tell me how to do this? Thanks. -- S |
| All times are GMT +1. The time now is 07:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com