Comparing two columns

G

Guest

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.
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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
 
G

Guest

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?
 
G

Guest

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
 
G

Guest

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?
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
G

Guest

Thanks very much.

Did you get the workbook/formulas from one of my later previous posts:
http://www.microsoft.com/office/com...ions&mid=759bf35d-c71b-401a-88ad-a9297f84ba90
??

Sharon said:
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 said:
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 said:
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.


:

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


:

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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes, I did. Thank you so much!
--
S


JLatham said:
Thanks very much.

Did you get the workbook/formulas from one of my later previous posts:
http://www.microsoft.com/office/com...ions&mid=759bf35d-c71b-401a-88ad-a9297f84ba90
??

Sharon said:
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 said:
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


:

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.


:

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


:

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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Great. Enjoy!

Sharon said:
Yes, I did. Thank you so much!
--
S


JLatham said:
Thanks very much.

Did you get the workbook/formulas from one of my later previous posts:
http://www.microsoft.com/office/com...ions&mid=759bf35d-c71b-401a-88ad-a9297f84ba90
??

Sharon said:
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


:

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


:

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.


:

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


:

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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
The code below worked for a few days and just totally stoppedworking. 1
VBA group the number in the Column and Copy to the new worksheet. 0
Single formula to find match across four columns? 2
Need Formula to "Adjust" to New Column Assignment 3
Conditional Formatting - Comparative Versus Other Cells 4
Sum every Nth Row (almost working) 1
v look up 1

Top