Advanced Filter #8

I am attempting to write a macro that incorporates an advanced filter
to search by ~100 criteria, across a list of ~200 columns x3000 rows.

Is there a way to implement this without running an advanced filter
with the same 100 criteria copied across 200 columns, one to match
each column in my list?

--Chris

0
05track (3)
5/2/2007 1:36:50 PM
excel 39879 articles. 2 followers. Follow

5 Replies
413 Views

Similar Articles

[PageSpeed] 13

I'm not clear on what you are asking. In any case, some columns can
have no criteria.

Hth,
Merjet


0
merjet (4)
5/2/2007 2:12:12 PM
I would like to use advanced filters (or something similar) to
essentially do this:

              Here are my 100 criteria, display every row that
contains one of them in the spreadsheet.



Currently all I know how to do is:

             Here are my 100 criteria, display every instance of them
in a specific column

             and repeat that for every single column, which with 200
columns makes for a very nasty advanced filter



Thanks in advance!

--Chris

0
05track (3)
5/2/2007 2:50:42 PM
Debra Dalgleish has some tips on using Advanced Filter here:

http://www.contextures.com/xladvfilter02.html

Hope this helps.

Pete

On May 2, 3:50 pm, 05tr...@gmail.com wrote:
> I would like to use advanced filters (or something similar) to
> essentially do this:
>
>               Here are my 100 criteria, display every row that
> contains one of them in the spreadsheet.
>
> Currently all I know how to do is:
>
>              Here are my 100 criteria, display every instance of them
> in a specific column
>
>              and repeat that for every single column, which with 200
> columns makes for a very nasty advanced filter
>
> Thanks in advance!
>
> --Chris


0
pashurst (2576)
5/2/2007 3:05:25 PM
That's a little more specific. Alright, suppose A1:E10 is filled with
random numbers between 1 and 100, and you want to display a row only
if it contains one of the numbers in A13:D13. Then the folowing will
do that.

Sub MyFilter()
Dim rngCrit As Range
Dim c As Range
Dim bFound As Boolean
Dim iRow As Integer
Dim iCol As Integer
Dim ws As Worksheet

UndoMyFilter
Set ws = Sheets("Sheet1")
Set rngCrit = ws.Range("A13:D13")
For iRow = 1 To 10
    bFound = False
    For iCol = 1 To 5
        For Each c In rngCrit
            If ws.Cells(iRow, iCol) = c Then
                bFound = True
                Exit For
            End If
        Next c
    Next iCol
    If bFound = False Then ws.Rows(iRow).Hidden = True
Next iRow
End Sub

Sub UndoMyFilter()
Sheets("Sheet1").Rows("1:10").Hidden = False
End Sub

Hth,
Merjet


0
merjet (4)
5/2/2007 3:50:37 PM
That's perfect!!

Thanks for your help!

--Chris

0
05track (3)
5/2/2007 5:47:18 PM
Reply:

Similar Artilces:

error message #8
How do I install Money 2004 and set it up to use Norton Utilities? In microsoft.public.money, bkoch wrote: >How do I install Money 2004 and set it up to use Norton >Utilities? Try it? If no go, read various opinions and experiences that have been posted: http://groups.google.com/groups?as_q=&num=50&as_scoring=d&hl=en&ie=UTF-8&oe=UTF-8&btnG=Google+Search&as_epq=&as_oq=nav+norton&as_eq=google&as_ugroup=microsoft.public.money&as_usubject=&as_uauthors=&as_umsgid=&lr=&as_drrb=q&as_qdr=&as_mind=12&as_minm=5&as_mi...

great palins 8.0 us payroll
can i see a traning demo session on us payroll 8.0 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=64dd06be-2f56-4c41-91cc-e1c977be182a&dg=microsoft.public.greatplains ...

Hyperlink #8
Does anyone know why I cant insert a hyperlink in a shared worksheet? Hi this is a restriction of shared workbooks. Only hyperlinks inserted BEFORE sharing the workbook work, no chance to insert new ones -- Regards Frank Kabel Frankfurt, Germany Nick wrote: > Does anyone know why I cant insert a hyperlink in a shared > worksheet? ...

excel filter
I wont to know if it�s possible to sort or filter by colo -- Message posted from http://www.ExcelForum.com The explanation is a bit long for here..........please check the site below, and if you have more questions, post back........ http://www.cpearson.com/excel/SortByColor.htm Vaya con Dios, Chuck, CABGx3 "shlomop >" <<shlomop.1820m6@excelforum-nospam.com> wrote in message news:shlomop.1820m6@excelforum-nospam.com... > I wont to know if it's possible to sort or filter by color > > > --- > Message posted from http://www.ExcelForum.com/ > ...

Reading and writing UTF-8 files
I have to do some simple text editing to large-ish (2 Mbyte) html files generated by Word. They are, I believe, in UTF-8. It is the !%^&* problem where single apostrophes become sequences of funny characters, some spaces are shown as unprintable characters, etc. The following code just reads and writes a file, and shows the problem. It applies whether I use a String or a StringBuilder, and whether or not I explicitly force UTF-8 encoding. Can somebody just tell me basically how to copy an html file by reading it in and then writing it out, which is all the following meth...

sender filtering on exchange 2003 server
i setup an exchange 2003 server to retreive it's e-mail from a provider's POP3 server for the time being due to the fact that the MX records haven't been changed yet. Now i enabled senderfiltering on my exchange server and it doesn't seem to work Question: Does this have anything to do with the pop3 mailbox of my provider or is there something else going on alltogether?? -- "Computers are useless, they can only give answers...." .. (Pablo Picasso) Obviously no one knows or nobody wants to help "Rik" wrote: > i setup an exchange 2003 server to re...

Percentages #8
Hi there, hope somebody out there in cyberworld can help me with the following probably easy to answer post. I have a number in box A1 lets say 100. This number is what I have dealt with over the month. In box B1 I have 10 and in box C1 I have 10. The numbers in B1 and C1 are figures within the 100 that I have missed for the month. How do I calculate out of 100% what I have achieved. I know how to calculate what percentage I have missed, i.e =sum(B1:C1)/A1*100 = 20%. ACHIEVED A1 = 100 MISSED B1 = 10 + C1 = 10 PERCENTAGE ACHIEVED = ? Thanks in advance for any help given. Terry ...

Table Filters and Empty Records
Hi, I'm quite new to Reporting Services and am having a bit of a confusing afternoon with it. I'm trying to build a report that contains a series of discrete reports for a number of projects that show project data and any milestone tasks associated with each project; if a project has no milesetones, the project data is still to be displayed. So, I build a report that has a series of fields showing project information (name, cost, state, etc.) and then a table below it to contain the milestone tasks for each project. All of these are placed within a list data region ...

Using Filter string with IF function
Is there a way to use a filter string with the IF function? I have a spreadsheet that looks like this: Invoice No. Invoice Date Invoice Currency Invoice Value 1 dd/mm/yyyy USD 1234.56 65 dd/mm/yyyy USD 2345.67 74 dd/mm/yyyy EUR 3456.78 88 dd/mm/yyyy USD 4567.89 when I filter on the currency: is there a function such as if(filter_string="USD";TRUE;FALSE)??? Thanks Aref Maybe this: Assuming row 1 are t...

IE 8 Parsing issues?
Hello, since a few month we're having strange requests in our web server logs. Example: 2010-01-27 18:00:35 W3SVC183691552 194.183.128.104 GET /SysRes/GLOBALSkin/Ski/head><body+onload= - 80 - 194.183.153.12 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+5.1;+Trident/4.0) http://my.domain.at/ 200 0 3 21699 757 46 Sometimes embedded resources like Scripts or CSS files are not loaded correctly. The path to the external resources is broken by adding any HTML code snippet of the page which embeds the resources. It happens on different pages / applications on our serv...

Advanced Find doesn't work
After having updated my Office 2000 version with service release 1a and service pack 3 the Outlook 2000 advanced find function no longer works. It doesn't deliver any results and pretends to continue searching forever. Anyone any ideas about this problem? Thanks, Steve Same problem here....except it doesnt seem to keep searching. It will only find something within the last day or so. "Steve Kraft" <anonymous@discussions.microsoft.com> wrote in message news:00a501c3c472$93d71c90$a601280a@phx.gbl... > After having updated my Office 2000 version with service > rele...

Auto filter on blank lines
When I auto filter there is normally blank lines at the bottom but now when it's filtered it's greyed out and I can't add any new lines. ...

PDC module in Great Plains 8
Hi i just wonder do we have a Post Dated Checks solution as a module in Great Plains 8 regards Nader How do you want it to work? "Nader Yousef" wrote: > Hi > > i just wonder do we have a Post Dated Checks solution as a module in Great > Plains 8 > > regards > Nader When a customer pays for his goods he pays the cashier a cash deposit which should be recorded as a CRV and the balance is to be payed by a Post Dated Checks with different due dates and the customer account is credited and debit the PDC account and when the check is deposited and acce...

IF COUNTIF & COUNTA on Filtered Visible Cells
Hi, Using the Formulae below, I can obtain the required data from the Columns in a non-filtered state. Column T: =IF(COUNTIF($V$10:$V10,$V11),COUNTA(INDIRECT("V"&U11+1&":V"&ROW()))-1,COUNTA($V$10:$V10)) Column U (helper column): =MAX(IF($V$10:$V10=$V11,ROW($V$10:$V10))) Column V: Text Data However, I also need to retrieve filtered data, and so require Formulae that will take into account Filtered Visible Cells and not the Non-Filtered data in the columns. Can the formulae be adapted to work on Filtered Visible Cells. Thanks Tin� -- Tin� --------------...

Labels #8
How do I import data from a data file to be printed onto labels in Microspft publisher. Hi Jlabels (Jlabels@discussions.microsoft.com), in the newsgroups you posted: || How do I import data from a data file to be printed onto labels in || Microspft publisher. Check out the following link: http://www.publishermvps.com/Default.aspx?tabid=92 -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. ...

Zebra stripe format filtered case as well
Hi, I learned in "msexcelraj website (could not see a name to mention here to thank)" how to make zebra stripe formating: Conditional formating, mod(row(),2)=0). My additional question is "how can I have zebra appearence when I make filtering as well ?" When I filter, naturally zebra appearence is losing with the way above. Thanks and regards, Cousin Excel Hi, Select the range then format|Conditional format-Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Pick a colour ADD Formula is =MOD(SUBTOTAL(3,$A1:$A$2),2)=1 Pick another colour OK --...

Copying values/formulas from only visable lines when filters are o
I have huge spreadsheet with dozens of columns, and filters are on most columns. In my last column, I want to systematically post the results only of specific lines while specific (and sometimes multiple) filters are on. Also, the last column is filtered for "blanks" only, so if a line already has a value from copying a prior filter value that value does not get overwritten by a more recent filtered value. I have done this for years in excel 2003, but in 2007 the values from the non-visible lines copy over to my last column as well, which I don't want. How c...

Exporting a filter from outlook 2002
Does anyone have any suggestions on how to create a filter then exporting that filter out of Outlook? Seems simple - but I am having some difficulty thanks Filter for what? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Chris asked: | Does anyone have any suggestions on how to create a | filter then exporting that filter out of Outlook? | | Seems simple - but I am having some difficulty | |...

MapviewofFile
Hi, I am working with an application that deals with large memory map files used as local cache for database. The MapViewofFile function fails when the file is 177 mb of size. But if I restart the application then it does not fail and creates more large file. My application is designed such that it needs all of the file in memory for manipulation chaning which will create a huge impact on the application. I knew that the problem is due of fragmentation of the process memory. I was looking into this hotfix from microsoft which says The Windows XP Low Fragmentation Heap Algorithm Feature Is Ava...

Advanced Sorting (4000 rows) followup Q.
Green John blue cats $50.00 Happy Green 2 John blue cats $50.00 Green 2 blue John cats $50.00 Green 2 blue cats John $50.00 Green 2 blue cats $50.00 John What I want: For example, I want "John" to be sorted to the first column for each row. Problem: In my original table, there are over 20+ Columns and 4,000 rows. Sorting by row, three at at time would take forever, especially since I have over 100 different databases that I need to do this on. Without sorting by rows three at a time, what's the best solution? If there's no way to do this in excel without a t...

filter #4
does anyone know of a program that attaches to Outlook Express that will allow me to select which recipients will receive the attachments and which ones will receive the email body text only? Chuck wrote: > does anyone know of a program that attaches to Outlook Express This newsgroup is for support of Outlook 97/98/2000/2002/2003/2007 from the Office suite of products. Outlook Express is actually a separate program despite the similar name. For help with your OE questions, try an OE newsgroup such as microsoft.public.outlookexpress.general (for OE 6), or an OE help website such a...

filtered cells
hello, I've got in sheet 1 and 2 a autofilter and I want to copy the filter results to sheet 3. Is that possible and does anybody know how to do it? greets, mark Hi You need to copy visible cells only once filtered required data select edit goto special select visible cells ok then copy this to sheet 3 Tina "minostrada" wrote: > hello, > > I've got in sheet 1 and 2 a autofilter and I want to copy the filter results > to sheet 3. Is that possible and does anybody know how to do it? > > greets, > > mark Hi tina, I don't exactly know wh...

Controls Theme in MFC Dialogs (VC7.1 and 8.0)
Hi All, I make one application by VC wizard MDI and Dialog APP, both in VC7.1 and 8. The controls in thease apps don't have WINXP style. Why? How I enbed XP style in buttons, edits etc.. Thanks have a look at the link below..... http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnwxp/html/xptheming.asp Hi, Welcome to MSDN Newsgroup! Making controls XP style is even easier. You don't actually have to do anything because the OS is responsible for drawing the button and if you are running the program on XP then XP will draw it in the XP style. However, there is...

Auto Filter #2
The autofilter locks up on me when trying to change the filtered values. How can I fix this? Autofilter uses lots of memory - like Excel does in general - generally this happens when you run out of memory. You may want to active the Resource Meter system tool before opening your workbook. If it goes red when you activate the filter you don't have enough memory. Try to reduce the size of the workbook - remove colors, macros etc. and then retry the filtering. Good Luck John >-----Original Message----- >The autofilter locks up on me when trying to change the >filtered ...

Intelligent Message Filter- can the junk email feature be turned o
I am wondering if there is a reg hack or other method of running the Intelligent Message Filter but disabling the feature to send stuff to junk email? I have temporarily uninstalled it for customer since there were too many complaints about stuff going into to junk email. The filter capacity for the most part works pretty well and what is going into archieve looks like SPAM with only minor exceptions. I was looking at reinstalling the Intelligent Message Filter and not setting the second setting –Store Junk Email Configuration. Although I intially had the settings SCL lower I raised...