| Home |
| Search |
| Today's Posts |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| comparing two columns | Excel Discussion (Misc queries) | |||
| Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
| comparing two columns | Excel Discussion (Misc queries) | |||
| comparing columns | Excel Worksheet Functions | |||
| comparing columns | Charts and Charting in Excel | |||
Threaded Mode
