Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Columns in 2 Sheets that are Not Ordered the Same
Hi,
In matching 2 columns-col J in sheet1 and col A in sheet2-col A in sheet2 is a subset of col J and can contain any values of col J. Col A is ordered differently than col J, and col A does not have to contain all the values of col J. Col J can contain more (different) values than col A. What I want to do is for sheet1 to detect when a change occurs in an existing value in col J that is also present in sheet2 col A. If that cell in col J is changed, I would like to invoke the removal of a filter (col A is the filtered list). Thanks for any help, Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Columns in 2 Sheets that are Not Ordered the Same
Right click on the sheet tab of Sheet1 and select view code. Then paste in
code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng2 As Range, v, v1 Dim cell As Range, num As Long If Target.Count 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Target.Column = 10 Then With Worksheets("Sheet2") Set rng2 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)) End With On Error GoTo ErrHandler Application.EnableEvents = False v = Target.Value Application.Undo v1 = Target.Value num = Application.CountIf(rng2, v1) Target.Value = v If num 0 Then If Worksheets("Sheet2").FilterMode Then Worksheets("Sheet2").ShowAll ' or to remove the autofilter 'Worksheets("Sheet2").Autofiltermode = False End If End If End If ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Arnold" wrote in message oups.com... Hi, In matching 2 columns-col J in sheet1 and col A in sheet2-col A in sheet2 is a subset of col J and can contain any values of col J. Col A is ordered differently than col J, and col A does not have to contain all the values of col J. Col J can contain more (different) values than col A. What I want to do is for sheet1 to detect when a change occurs in an existing value in col J that is also present in sheet2 col A. If that cell in col J is changed, I would like to invoke the removal of a filter (col A is the filtered list). Thanks for any help, Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Columns in 2 Sheets that are Not Ordered the Same
Thanks Tom.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to compare two columns on two sheets and copy associated data from one sheet to the other? | Excel Worksheet Functions | |||
compare two sheets | Excel Discussion (Misc queries) | |||
Compare 2 sheets | Excel Discussion (Misc queries) | |||
How do I compare two columns on seperate sheets and replace text . | Excel Worksheet Functions | |||
compare and merge 2 sheets if columns same | Excel Programming |