Filtering question.

[Fixed2bbroken]

Hi, i am running excel 2007 and had a some questions about setting up easy filtering. I'm relatively good at using the basics of excel but not so much the VBA part of it. Ok, so onto the question...

I have a database of information about groupons/gift cards that are redeemed that my staff use to make sure there are no duplicates and such. We have been doing this manually using paper copies but i would really like to do it on the computer. I know how to use advance filter and such but unfortunately my staff are not excel savvy and telling them to filter would lead to more problems then its worth.

So my question is, is there an easy way to make something like a search engine or macro in excel where my staff can type a name or number into a certain cell and have excel auto filter the list of data to show just the information pertaining to the search?

Sorry about the long story but its really important that using it would be super simple. Thanks for your help.

 

[HRIS]

Below is a macro that you might be able to adjust to suit your needs. When run, it pops up boxes where the user inputs the data by which they want to filter the spreadsheet [based on column F (field 6)]. This specific macro filters the data found between the specified dates. However you could adjust to your criteria and spreadsheet.

To make it simple for your users to run, you could attach the macro to a button which would initiate it upon being clicked. The user would then be prompted for the criteria. Upon inputting the dates, the macro filters, copies and pastes the data to an inserted tab. Again, you would probably want to adjust some of this to fit your needs. Without seeing your specific data or knowing what you might want to do with it, I can't tell you exactly how you want to adjust.

I'm sure others with more macro experience could definitely make some improvements to this as well!

Sub Extract_Data()
 
'Variables used by the macro
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String
'Get the current file's name
CurrentFileName = ActiveWorkbook.Name
'Selects my range
'(note you can change this to meet your requirements)
Range("A5:G65000").Select
'Apply Autofilter
Selection.AutoFilter
'Get the filter's criteria from the user
FilterCriteria1 = InputBox("Begin Date")
FilterCriteria2 = InputBox("End Date")
'Filter the data based on the user's input
'NOTE - this filter is on column F (field:=6), to change
'to a different column you need to change the field number
Selection.AutoFilter Field:=6, Criteria1:=">=" & FilterCriteria1, Criteria2:="<=" & FilterCriteria2
'Select the visible cells (the filtered data)
Selection.Copy
'Inserts a new sheet, pastes the copied data, formats it
Sheets.Add
Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Cells.Select
    Selection.ColumnWidth = 13.71
    Cells.EntireColumn.AutoFit
'Inputs a formula into cell I1 - location and formula can be changed as necessary
Range("I1").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:R[196]C[-2])"
    Sheets("Sheet1").Select
    Selection.AutoFilter Field:=6
    Range("A1").Select
End Sub

 

[yytsunamiyy]

It certainly is possible, but without knowing what your data looks like and what you want to filter on it's impossible to do. Please provide a snapshot of your data (Simply put borders around cells and copy paste here for starters) and explain what you would expect to see as the result.

 

[Fixed2bbroken]

Thank you for the quick replies. I will try out the code you posted and let you know if it works. Here is a small sample of what the data looks like...

<table border="0" cellpadding="0" cellspacing="0" width="789"><col style="width: 116pt;" width="154"> <col style="width: 152pt;" width="202"> <col style="width: 89pt;" width="119"> <col style="width: 48pt;" width="64"> <col style="width: 108pt;" width="144"> <col style="width: 42pt;" width="56"> <col style="width: 38pt;" width="50"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 116pt;" height="17" width="154">Coupon No.</td> <td style="width: 152pt;" width="202">Customer Name</td> <td style="width: 89pt;" width="119">Redemption Date</td> <td style="width: 48pt;" width="64">Zip Code</td> <td style="width: 108pt;" width="144">Email Address</td> <td class="xl66" style="width: 42pt;" width="56">Entry #</td> <td class="xl66" style="width: 38pt;" width="50">Staff</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">9776630-0-1</td> <td>Neilly bergman</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">9776678-0-1</td> <td>Bonnie Stetz</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">9776750-0-1</td> <td>Tamie Yost</td> <td class="xl64" align="right">11/6/2010</td> <td align="right">60128</td> <td class="xl65">T.Yost@gmail.com</td> <td class="xl66">3</td> <td class="xl66">MZ</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">9776966-0-1</td> <td>Diana Lemos</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">9776966-0-2</td> <td>Diana Lemos</td> <td class="xl64" align="right">3/1/2011</td> <td align="right">66142</td> <td class="xl65">Turtle22@Hotmail.com</td> <td class="xl66">4</td> <td class="xl66">DT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">9777017-0-1</td> <td>Kumar</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">9777144-0-1</td> <td>Robyn Haynes</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">9777185-0-1</td> <td>MOHAMMED KHAN</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> </tbody></table>

As you can see some of the spaces are blank. So I'm hoping that they would be able to type in some part of the information whether its the coupon code or name or email address, it would filter for just that line, so they can fill out the rest of the information as it is given to them by the customer.

Thanks again.

 

[yytsunamiyy]

Why dont you just tell them about the Find functionality? Simply press CTRL+f, enter the value you are looking for and Excel takes you to the correct line.

 

[HRIS]

yytsunamiyy is probably right, if you are just trying to find one line it would be easier to show them how to use CTRL+F to search for the data they want.

If you are needing to filter down to several lines with similar data types, it might be easiest to turn on autofilter and show them how to use that. This would allow you to filter based upon whatever column you want and would probably be easier than adjusting the macro to fit your needs.

Here's a link with some info on autofilters: http://www.ozgrid.com/Excel/AutoFilters.htm.

 

[Fixed2bbroken]

Thank you for the advice. HRIS, i was able to take a part of your code and adapt it to what i need so thank you. I know Ctrl F is easier but it doesn't hide the rest of the entries so chances for an entry being made on the wrong line is much greater...

I just have one more question. For some reason the filter will not work when i put in a partial entry. for example under customer name if i type in "Diana" it will give me 0 entries. It only seems to work if i type the full entry. Is there a way to remedy that? Here is the code i used.

Selection.AutoFilter Field:=2, Criteria1:="=**" & FilterCriteria1

Thanks again. Seriously you guys have opened up my eyes to a whole new part of excel that i didn't know existed until recently.

 

[HRIS]

Thank you for the advice. HRIS, i was able to take a part of your code and adapt it to what i need so thank you. I know Ctrl F is easier but it doesn't hide the rest of the entries so chances for an entry being made on the wrong line is much greater...

I just have one more question. For some reason the filter will not work when i put in a partial entry. for example under customer name if i type in "Diana" it will give me 0 entries. It only seems to work if i type the full entry. Is there a way to remedy that? Here is the code i used.

Selection.AutoFilter Field:=2, Criteria1:="=**" & FilterCriteria1

Thanks again. Seriously you guys have opened up my eyes to a whole new part of excel that i didn't know existed until recently.

Try changing it to:

Selection.AutoFilter Field:=2, Criteria1:="**" & FilterCriteria1

Notice the = not in the quotation marks. Let me know if this works!

 

[HRIS]

Try changing it to:

Selection.AutoFilter Field:=2, Criteria1:="**" & FilterCriteria1

Notice the = not in the quotation marks. Let me know if this works!

Disregard, didn't work quite like I thought. Still messing with it...

 

[HRIS]

Try:
Criteria1:="=*" & FilterCriteria1 & "*"

Worked for mine! Let me know how it goes!!