Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 76
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 76
Default Compare 2 Columns in 2 Sheets that are Not Ordered the Same

Thanks Tom.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to compare two columns on two sheets and copy associated data from one sheet to the other? [email protected] Excel Worksheet Functions 2 June 22nd 07 03:40 PM
compare two sheets rexmann Excel Discussion (Misc queries) 2 February 14th 07 04:07 PM
Compare 2 sheets Brian Mosher Excel Discussion (Misc queries) 1 March 8th 06 07:04 PM
How do I compare two columns on seperate sheets and replace text . hag400 Excel Worksheet Functions 1 December 28th 04 03:32 PM
compare and merge 2 sheets if columns same danalee Excel Programming 0 October 13th 04 02:34 AM


All times are GMT +1. The time now is 10:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"