Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching from one list in another
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching from one list in another
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 Brian wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching from one list in another
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 Dave Peterson wrote: 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 Brian wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching from one list in another
This portion of the code:
If IsNumeric(res) Then 'there's a match on the list myCell.EntireRow.Hidden = False Else myCell.EntireRow.Hidden = True End If Actually showed or hid the row. I guess it could have just hidden the rows that need to be hidden after adding a line 'done with the checking! llrng.parent.rows.hidden = false '<--added 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 '<-- commented Else myCell.EntireRow.Hidden = True End If Next myCell Brian wrote: 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 Dave Peterson wrote: 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 Brian wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching A List | Excel Worksheet Functions | |||
searching for a word(s) within a list | Excel Discussion (Misc queries) | |||
Searching for a contact without going through the whole list. | Excel Worksheet Functions | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
searching within list | Excel Discussion (Misc queries) |