Searching from one list in another

[Brian]

I have a workbook which has one tab (“Shortlist”) with about 150 rows of
data, and a tab (“Longlist”) with about 20,000 rows of data. I need to
be able to select a column in Shortlist and a column in Longlist (which
I would do manually), then do the following:

- for each row in the Longlist column,
- compare the cell in Longlist with the whole column in Shortlist,
- if the cell in Longlist does NOT match any cell in the corresponding
column in Shortlist, hide the row in Longlist,
- go down to the next row.

I am using XL2000 in XP, and I have managed to produce this so far,
adapting code I have found elsewhere.

Sub Hide_Rows()
Dim R As Long
Dim rng As Range
Application.ScreenUpdating = False

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If

For R = rng.Rows.count To 1 Step -1
‘If [This is the problem area] Then
rng.Rows(R).EntireRow.Hide
End If
Next R

Application.ScreenUpdating = True
End Sub


I would appreciate any suggestions.

 

[Dave Peterson]

Instead of looping through the cells one at a time, it sounds like you could use
Data|filter|Advanced filter.

You'd filter the column in the Lostlist sheet and specify the criteria as the
column in the ShortList sheet.

You could record a macro to get the basic syntax correct. If you need help
generalizing the procedure, you could write back with the code you have so far.

I would think this method would be the fastest.

===========
But if you want, you could try:

Option Explicit
Sub testme()
Dim SLWks As Worksheet
Dim LLWks As Worksheet
Dim LLRng As Range
Dim SLRng As Range
Dim res As Variant
Dim myCell As Range

Set SLWks = Worksheets("ShortList")
Set LLWks = Worksheets("LongList")

Set LLRng = Nothing
On Error Resume Next
Set LLRng = Application.InputBox _
(prompt:="Select a single cell in the LongList sheet", _
Type:=8).Cells(1).EntireColumn
On Error GoTo 0
If LLRng Is Nothing Then
'user hit cancel
Exit Sub
End If

Set SLRng = Nothing
On Error Resume Next
Set SLRng = Application.InputBox _
(prompt:="Select a single cell in the ShortList sheet", _

Type:=8).Cells(1).EntireColumn
On Error GoTo 0
If SLRng Is Nothing Then
'user hit cancel here
Exit Sub
End If

If SLRng.Parent.Name <> SLWks.Name Then
MsgBox "Shortlist column has to be on the Shortlist sheet!"
Exit Sub
End If

If LLRng.Parent.Name <> LLWks.Name Then
MsgBox "Longlist column has to be on the LongList sheet!"
Exit Sub
End If

If Application.CountA(SLRng) = 0 Then
MsgBox "Nothing in the Shortlist range"
Exit Sub
End If

If Application.CountA(LLRng) = 0 Then
MsgBox "Nothing in the LongList range"
Exit Sub
End If

'done with the checking!
For Each myCell In Intersect(LLRng, LLRng.Parent.UsedRange).Cells
res = Application.Match(myCell.Value, SLRng, 0)
If IsNumeric(res) Then
'there's a match on the list
myCell.EntireRow.Hidden = False
Else
myCell.EntireRow.Hidden = True
End If
Next myCell

End Sub

I have a workbook which has one tab (“Shortlist”) with about 150 rows of
data, and a tab (“Longlist”) with about 20,000 rows of data. I need to
be able to select a column in Shortlist and a column in Longlist (which
I would do manually), then do the following:

- for each row in the Longlist column,
- compare the cell in Longlist with the whole column in Shortlist,
- if the cell in Longlist does NOT match any cell in the
corresponding column in Shortlist, hide the row in Longlist,
- go down to the next row.

I am using XL2000 in XP, and I have managed to produce this so far,
adapting code I have found elsewhere.

Sub Hide_Rows()
Dim R As Long
Dim rng As Range
Application.ScreenUpdating = False

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If

For R = rng.Rows.count To 1 Step -1
‘If [This is the problem area] Then
rng.Rows(R).EntireRow.Hide
End If
Next R

Application.ScreenUpdating = True
End Sub


I would appreciate any suggestions.

 

[Brian]

Dave,

Many thanks indeed for your help. Using Data | Filter | Advanced Filter
doesn't really work for me in this case, because I then have to put a
new list (perhaps quite substantial) somewhere else on the LongList tab,
and the space would soon start getting cluttered, whereas using your
code all I have to do afterwards is unhide the rows, before searching on
a different column.

All I did to your code was to switch the screen updating on and off.

Brian

Instead of looping through the cells one at a time, it sounds like you
could use Data|filter|Advanced filter.

You'd filter the column in the Lostlist sheet and specify the criteria
as the column in the ShortList sheet.

You could record a macro to get the basic syntax correct. If you need
help generalizing the procedure, you could write back with the code you
have so far.

I would think this method would be the fastest.

===========
But if you want, you could try:

Option Explicit
Sub testme()
Dim SLWks As Worksheet
Dim LLWks As Worksheet
Dim LLRng As Range
Dim SLRng As Range
Dim res As Variant
Dim myCell As Range

Set SLWks = Worksheets("ShortList")
Set LLWks = Worksheets("LongList")

Set LLRng = Nothing
On Error Resume Next
Set LLRng = Application.InputBox _
(prompt:="Select a single cell in the LongList sheet", _
Type:=8).Cells(1).EntireColumn
On Error GoTo 0
If LLRng Is Nothing Then
'user hit cancel
Exit Sub
End If

Set SLRng = Nothing
On Error Resume Next
Set SLRng = Application.InputBox _
(prompt:="Select a single cell in the ShortList sheet", _
Type:=8).Cells(1).EntireColumn
On Error GoTo 0
If SLRng Is Nothing Then
'user hit cancel here
Exit Sub
End If

If SLRng.Parent.Name <> SLWks.Name Then
MsgBox "Shortlist column has to be on the Shortlist sheet!"
Exit Sub
End If

If LLRng.Parent.Name <> LLWks.Name Then
MsgBox "Longlist column has to be on the LongList sheet!"
Exit Sub
End If

If Application.CountA(SLRng) = 0 Then
MsgBox "Nothing in the Shortlist range"
Exit Sub
End If

If Application.CountA(LLRng) = 0 Then
MsgBox "Nothing in the LongList range"
Exit Sub
End If

'done with the checking!
For Each myCell In Intersect(LLRng, LLRng.Parent.UsedRange).Cells
res = Application.Match(myCell.Value, SLRng, 0)
If IsNumeric(res) Then
'there's a match on the list
myCell.EntireRow.Hidden = False
Else
myCell.EntireRow.Hidden = True
End If
Next myCell

End Sub

I have a workbook which has one tab (“Shortlist”) with about 150 rows
of data, and a tab (“Longlist”) with about 20,000 rows of data. I need
to be able to select a column in Shortlist and a column in Longlist
(which I would do manually), then do the following:

- for each row in the Longlist column,
- compare the cell in Longlist with the whole column in Shortlist,
- if the cell in Longlist does NOT match any cell in the
corresponding column in Shortlist, hide the row in Longlist,
- go down to the next row.

I am using XL2000 in XP, and I have managed to produce this so far,
adapting code I have found elsewhere.

Sub Hide_Rows()
Dim R As Long
Dim rng As Range
Application.ScreenUpdating = False

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If

For R = rng.Rows.count To 1 Step -1
‘If [This is the problem area] Then
rng.Rows(R).EntireRow.Hide
End If
Next R

Application.ScreenUpdating = True
End Sub


I would appreciate any suggestions.

 

[Dave Peterson]

This portion of the code:

Actually showed or hid the row.

I guess it could have just hidden the rows that need to be hidden after adding a
line

Dave,

Many thanks indeed for your help. Using Data | Filter | Advanced Filter
doesn't really work for me in this case, because I then have to put a
new list (perhaps quite substantial) somewhere else on the LongList tab,
and the space would soon start getting cluttered, whereas using your
code all I have to do afterwards is unhide the rows, before searching on
a different column.

All I did to your code was to switch the screen updating on and off.

Brian

Instead of looping through the cells one at a time, it sounds like you
could use Data|filter|Advanced filter.

You'd filter the column in the Lostlist sheet and specify the criteria
as the column in the ShortList sheet.

You could record a macro to get the basic syntax correct. If you need
help generalizing the procedure, you could write back with the code
you have so far.

I would think this method would be the fastest.

===========
But if you want, you could try:

Option Explicit
Sub testme()
Dim SLWks As Worksheet
Dim LLWks As Worksheet
Dim LLRng As Range
Dim SLRng As Range
Dim res As Variant
Dim myCell As Range

Set SLWks = Worksheets("ShortList")
Set LLWks = Worksheets("LongList")

Set LLRng = Nothing
On Error Resume Next
Set LLRng = Application.InputBox _
(prompt:="Select a single cell in the LongList sheet", _
Type:=8).Cells(1).EntireColumn
On Error GoTo 0
If LLRng Is Nothing Then
'user hit cancel
Exit Sub
End If

Set SLRng = Nothing
On Error Resume Next
Set SLRng = Application.InputBox _
(prompt:="Select a single cell in the ShortList sheet", _
Type:=8).Cells(1).EntireColumn
On Error GoTo 0
If SLRng Is Nothing Then
'user hit cancel here
Exit Sub
End If

If SLRng.Parent.Name <> SLWks.Name Then
MsgBox "Shortlist column has to be on the Shortlist sheet!"
Exit Sub
End If

If LLRng.Parent.Name <> LLWks.Name Then
MsgBox "Longlist column has to be on the LongList sheet!"
Exit Sub
End If

If Application.CountA(SLRng) = 0 Then
MsgBox "Nothing in the Shortlist range"
Exit Sub
End If

If Application.CountA(LLRng) = 0 Then
MsgBox "Nothing in the LongList range"
Exit Sub
End If

'done with the checking!
For Each myCell In Intersect(LLRng, LLRng.Parent.UsedRange).Cells
res = Application.Match(myCell.Value, SLRng, 0)
If IsNumeric(res) Then
'there's a match on the list
myCell.EntireRow.Hidden = False
Else
myCell.EntireRow.Hidden = True
End If
Next myCell

End Sub

I have a workbook which has one tab (“Shortlist”) with about 150 rows
of data, and a tab (“Longlist”) with about 20,000 rows of data. I
need to be able to select a column in Shortlist and a column in
Longlist (which I would do manually), then do the following:

- for each row in the Longlist column,
- compare the cell in Longlist with the whole column in Shortlist,
- if the cell in Longlist does NOT match any cell in the
corresponding column in Shortlist, hide the row in Longlist,
- go down to the next row.

I am using XL2000 in XP, and I have managed to produce this so far,
adapting code I have found elsewhere.

Sub Hide_Rows()
Dim R As Long
Dim rng As Range
Application.ScreenUpdating = False

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If

For R = rng.Rows.count To 1 Step -1
‘If [This is the problem area] Then
rng.Rows(R).EntireRow.Hide
End If
Next R

Application.ScreenUpdating = True
End Sub


I would appreciate any suggestions.