Filtering question

jimellem

New Member
Joined
Sep 8, 2006
Messages
8
I know this is probably covered somewhere else, so bear with me please. . . . or just don't answer this and link me somewhere else. . . .

I have nice ~4000 row database with information. Each row can be identified by a text code. Say some jerk in another department wants me to find 100 rows of data based on these 100 text codes. The text codes are totally random, so you need a way to filter to just get that data.

I know to use Excel's advanced filter capabilities you need to have the cells in this format ="TEXTCODE" I also know its a pain in the rear end (if not impossible?) to insert quotation marks using a simple macro into a text string -- since Macros use the quotation marks to define the text string.

So is advanced filter not the way to go about this??? Or Macro, or. . . .well I don't really know.

Much obliged for the help and have a good weekend. .. ..
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,927
Office Version
  1. 365
Platform
  1. Windows
Eh, you don't need quotes or = for advanced filter for text.:)

What's your actual question?
 

jimellem

New Member
Joined
Sep 8, 2006
Messages
8
Duh!!!!

Absolutely no question at all. I just got hit by a stupid-bomb.

I realized what I was doing wrong -- pretty much everything. I could blame Mircosoft's lame Excel help file, but I'll take full credit here.

All apologies. And I got the filter to work.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Make a list of the wanted ID Codes and put this list on a Sheet in your DataBase named "List"
Insert another sheet and name it "Found"

In the code below the Sheet named: "Data" is the name of the sheet containing the Data Table to be searched, you can change this name!

The code assumes that the Data Table ID Codes are in Column "A" or 1.


Sub myFindFromLst()
'Standard module code [VBA editor (Alt-11), Insert - Module]!
'Find all the rows that have your ID Codes in the Data Table on Sheet: "Data"
' [the sheet name of the sheet that has your Data Table].
'Sheet Name: List has the list of all the ID Codes you want filtered.
'Then copy part of the found row to the next blank row on Sheet Named: Found, for a filtered list.

Dim myIDCode As Variant
Dim Cell As Object, r As Object
Dim myIDs As Range
Dim n&

'Get ID Codes from list .
Sheets("List").Select
Set myIDs = Sheets("List").Range("A1:A100")

Application.ScreenUpdating = False

For Each Cell In myIDs
myIDCode = Cell.Value

'Check DB for each ID Code on wanted list!
For Each r In Sheets("Data").UsedRange.Rows
n = r.Row

'Look at "ID Code" Column of DB for "myIDCode" [Column "A" or 1, in this case]!
If Sheets("Data").Cells(n, 1).Value = myIDCode Then
x = x + 1
'Copy the found row/record,
'to the sheet named [Found] in its next empty row.

Sheets("Data").Select
r.EntireRow.Copy _
Destination:=Sheets("Found").Range("A65536").End(xlUp).Offset(1, 0)
Else
End If
Next r
Next Cell

Application.CutCopyMode = True
Application.ScreenUpdating = True
GoTo myEnd

myEnd:
'How many found?
If x <> 0 Then
MsgBox " Found: " & x & ", ID Codes!"
End If

Sheets("Found").Select
End Sub
 

jimellem

New Member
Joined
Sep 8, 2006
Messages
8
Thanks Joe

I tried your vB code and that works really well.

I love multiple ways to skin a cat. . .
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Yes most applications now days includes many ways to do things Hot-Keys, Buttons, Pull-Downs, Menus... The same with code and utilities. We all think differently and have different levels of experiance, so we see different solutions.

I am found of saying: If you cannot go through the door, go through a window.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,381
Messages
5,547,613
Members
410,803
Latest member
lsweeney
Top