Auto filter custom filter

Hi,

I have applied an autofilter to my data. I want to do a custom filter from 
there.

My data is as follows:
100
110
120
200
210
220
300
....
900
910
920

The 100, 200, etc levels are the total categories and i need to do a custom 
filter based on if the number from the list is a "hundred" number. E.g. 100, 
200, 300 ... , 900 I.e. the middle digit is only 0, or the whole number is a 
round 100, 200, 300, etc level number...

Thanks in advance,
stevefromnaki
0
Utf
12/29/2009 3:25:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
822 Views

Similar Articles

[PageSpeed] 19

Assume that you are having the values in ColumnA like the below:-

A Col
100
110
120
200
210
220
300
…
900
910
920

In B1 cell paste the below formula

=IF(ISNUMBER(A1),IF(AND(A1>=100,A1<=900,VALUE(RIGHT(A1,2))=0),"YES","NO"),"")

Now copy the B1 cell and paste it to the remaining cells of B Column depends 
upon the A Column data.

Now apply the filter for B Column and select Yes.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"stevefromnaki" wrote:

> Hi,
> 
> I have applied an autofilter to my data. I want to do a custom filter from 
> there.
> 
> My data is as follows:
> 100
> 110
> 120
> 200
> 210
> 220
> 300
> ...
> 900
> 910
> 920
> 
> The 100, 200, etc levels are the total categories and i need to do a custom 
> filter based on if the number from the list is a "hundred" number. E.g. 100, 
> 200, 300 ... , 900 I.e. the middle digit is only 0, or the whole number is a 
> round 100, 200, 300, etc level number...
> 
> Thanks in advance,
> stevefromnaki
0
Utf
12/29/2009 5:54:01 AM
That will work, but is there a way to do it without adding in an extra 
column, i already have about 30 columns ;)

"Ms-Exl-Learner" wrote:

> Assume that you are having the values in ColumnA like the below:-
> 
> A Col
> 100
> 110
> 120
> 200
> 210
> 220
> 300
> …
> 900
> 910
> 920
> 
> In B1 cell paste the below formula
> 
> =IF(ISNUMBER(A1),IF(AND(A1>=100,A1<=900,VALUE(RIGHT(A1,2))=0),"YES","NO"),"")
> 
> Now copy the B1 cell and paste it to the remaining cells of B Column depends 
> upon the A Column data.
> 
> Now apply the filter for B Column and select Yes.
> 
> Remember to Click Yes, if this post helps!
> 
> --------------------
> (Ms-Exl-Learner)
> --------------------
> 
> 
> "stevefromnaki" wrote:
> 
> > Hi,
> > 
> > I have applied an autofilter to my data. I want to do a custom filter from 
> > there.
> > 
> > My data is as follows:
> > 100
> > 110
> > 120
> > 200
> > 210
> > 220
> > 300
> > ...
> > 900
> > 910
> > 920
> > 
> > The 100, 200, etc levels are the total categories and i need to do a custom 
> > filter based on if the number from the list is a "hundred" number. E.g. 100, 
> > 200, 300 ... , 900 I.e. the middle digit is only 0, or the whole number is a 
> > round 100, 200, 300, etc level number...
> > 
> > Thanks in advance,
> > stevefromnaki
0
Utf
12/30/2009 6:23:01 AM
Reply:

Similar Artilces:

How to customize the Office / Excel 2007 Theme Colors
Hi NG, Any idea how to customize the Office / Excel 2007 Theme Colors - Color Graduations? When you define Theme Colors you can define the base color for color Graduations, BUT there is not way to find out (a) how the color graduations are calculated, and (b) how to customize the color graduations Thanks, Andreas ...

Is Outlook auto responder security problem?
I am using Outlook 2003 SP2 for inter office mail as well as outside contacts with trusted clients. When I post an "out of office" auto response is there anyway to prevent that from sending spammers and phishers my contact info (MAC, IP, e-mail addresses) so that they now have a valid target and info to spread to others like them? I don't think so. The goal is to filter the spam well enough that it never gets to your box. However consider that almost all of the return addresses are "fake",...so it isn't going back to the spammer anyway. Most of the time it...

#ERROR in Custom Field
I wrote a simple equation Additional Costs = ([Cost]) * 0.08 It was accepted into the equation but in the column it show "#ERROR" Does anyone have any suggestions or know what I did wrong? Hit the F9 key to recalculate. What type of field is the Additional Costs field? -Jack Dahlgren "Erin" <Erin@discussions.microsoft.com> wrote in message news:EE0A8BFF-7179-4168-80EC-DE1881F8E284@microsoft.com... > I wrote a simple equation > > Additional Costs = ([Cost]) * 0.08 > > It was accepted into the equation but in the column it show...

Sorting the "My Work" or "Customers"
Hi, The default setting for the My Work is: Activities Calendar Queues Articles Reports Announcements The default setting for Customers: Accounts Contacts Is there a way to change order of these? Thanks Hello katrinathompson, yes you can change it in the sitemap.xml regards Mario > Hi, > > The default setting for the My Work is: > Activities > Calendar > Queues > Articles > Reports > Announcements > The default setting for Customers: > Accounts > Contacts > Is there a way to change order of these? > > Thanks > ...

Full Down List That Filters
I need to present a workbook to a group of non excel users to fill out various information for their corresponding name. I need to do this in a way other than using autofilter. I was looking around for some way that when the user opens the workbook they can choose their name from a pull down list and choose their name therefore displaying only their corresponding data. For example, Kevin opens up the file and chooses his name from a pull down and it displays only his 10 entries from a list of many other names. Could someone send me in the right direction for this? Autofilter is not an ...

custom cells format
I want to format a cell so i can transform 1256453 in 125.56. How can i format a cell so i can view first number as 125.56? Thanks for your help. You have a couple of replies to your other posts. puiuluipui wrote: > > I want to format a cell so i can transform 1256453 in 125.56. How can i > format a cell so i can view first number as 125.56? > Thanks for your help. -- Dave Peterson Go to Format -->Cell -->Number-->Custom There you type 0.00, You get the results as desire Sanjeev Agarwal "puiuluipui" wrote: > I want to format a cell so i can tra...

Auto-fill?
I'm new to Office 2007, and when using Excel in the past, when typing someting in a cell, if it is already on the page, it auto-fills in so that I know I'm not having duplicates on my spreadsheets. How do I set that up on this new version? Can anyone tell me? Is it a basic setting or something? Thank you. Sorry, this newsgroup is for questions about Access, the database product that's part of Office Professional. You'd be best off reposting your question to a newsgroup related to Excel. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!...

Merge Customer Accounts
Does anyone have a way to merge customer accounts? Hi Butch - there's an article in the knowledgebase on customersource that explains how to do this by running queries against the database. Make sure you first make a backup of your database before attempting anything however. hth kevin "Butch" wrote: > Does anyone have a way to merge customer accounts? > > > Butch, If you have access to CustomerSource; http://tinyurl.com/hdt9u -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see...

Custom Animation
I have tried to use the custom animation in Powerpoint 2007 to create the effect of dissolving the top photo upon exiting, while a second photo (underneath) appears upon entrance. Unfortunately, the top photo reappears almost immediately. This defeats the whole purpose!! Can anybody tell me how to prevent this from happening? I would much appreciate your help! Thank you! First photo desappears with previous effect with, let say a delay of 6" Second photo above first one appears with previous effect Second photo desappears after previous effect with delay of 6"...

NET DISPLAY CUSTOMIZING
Our default NET DISPLAY is very difficult to see during the day - can anyone tell me an easy way to customize the colors and the font sizes? ...

Customizing Views in OL2003 across a "Personal Folders" folder set
My company has migrated to Outlook 2003 and I would like to make some changes to the new Default Views. I have read the tips at outlook-tips.net and gone through this group in some detail but have not been able to accomplish my goals I have a bunch of folders saved on my local drive under "Personal Folders" which is on the same tree level as the main mailbox. I would like to 1) Disable Grouping 2) Disable the Reading Pane For 1) I tried View->Define Views->... per outlook-tips.net but it did not work across the subfolders in the Personal Folders directory. I also tried dis...

Applying Credit to a Customers Invoice
In Money Plus, I have a customer that has a credit due to an overpayment. On the next invoice, I tried to credit the customer and clicked on apply credit, but it doubled the credit and posted the invoice total to my checking account. How do I apply a credit to a customers invoice without it posting to my checking account? =?Utf-8?B?RWxpemFiZXRo?= <Elizabeth@discussions.microsoft.com> wrote on 01 Apr 2008 in group microsoft.public.money: > In Money Plus, I have a customer that has a credit due to an > overpayment. On the next invoice, I tried to credit the customer and >...

Custom Header #2
Is it possible to reference just part of the file name or use a formula in the custom header field? I want to make part of the Custom Header so that it references the Month from the file name. For example: File Name - InvoiceUploadDec04Roch Custom Header: Invoice Upload Dec 04 Only through a macro. Record one to get the syntax for setting a header through code. -- Jim Rech Excel MVP "Josh O." <JoshO@discussions.microsoft.com> wrote in message news:52CC8863-18F7-4BF0-A20E-DED8ADC163D7@microsoft.com... | Is it possible to reference just part of...

Report-Custom
Hi all, Could anyone tells me how to active this HQ Manager-Reports-( CUSTOM ) <--- I am trying to add modified report to my existing reports. Look in the .../Headquarters/Reports directory make a copy of one of the files and rename it using this convention: Custom - My Report Name.qrp Note the spaces around the hyphen - they have to be there! Restart HQ Manager and the Custom menu will now be active and you will see a report listed under it. ..qrp files are just text files - you can customize them by editing them in notepad. You should always make sure you have a backup copy o...

show me the path of auto archive save at in the computer
show me the path of auto archive save at in the computer On Mon, 24 Jul 2006 06:21:02 -0700, pat <pat@discussions.microsoft.com> wrote: >show me the path of auto archive save at in the computer details plze: outlook 3002 ? 5001 ? express ? backup add-in ? Youve checked properties of your pst or a folder? By Default its the same location as your default pst "pat" <pat@discussions.microsoft.com> wrote in message news:8959EA3C-A4FF-4D43-8168-47E448A4F96A@microsoft.com... > show me the path of auto archive save at in the computer ...

Custom Format : Gb/Mb/kb/bytes : Need help debugging command
Hi Everyone, I'm trying to format a few series of bytes as something that is a little more legible, and truncating the number of decimal points. While the link 'Local Tips on Custom Formatting eg.' (http://www.exceltip.com/st/Formatting_numbers_according_to_criteria_in_Microsoft_Excel/71.html) has been a great aid in getting the general drift of how to write the custom format strings, it doesn't aid debugging something that errors. I have the following line: > [<1099511627776]((#,##0.000)/1099511627776)" > TB";[<1073741824]((#,##0.000)/1073741824)"...

Printing or Emailing a Quotation to a Customer
I have created Customers, Accounts, Price Lists, Product Catalog in CRM 3.0. I create and activate a Quotation. Now, how do I get this quotation to the customer? Does Microsoft CRM allow me to define standard quotation templates, and send them off to the customer? I have gone through the online documentation, and my CRM books, and I can't find any reference discussing how a customer gets the quote generated in Microsoft CRM. Surely there must be some way of automating the quotation layout & sending. Thanks, Andrew. You could create an email template using the quote, but thi...

How to Unset Customer
Hi Does anyone know how can i revert back to walk-in customer if I mistakenly select a customer in the POS screen regards, Joie Have you tried using the clear customer button or function "Joie" wrote: > Hi Does anyone know how can i revert back to walk-in customer if I > mistakenly select a customer in the POS screen > > regards, > Joie > > > Should be Ctrl-Shift-7 Marc "Paul Houston / Eastern National" <PaulHoustonEasternNational@discussions.microsoft.com> wrote in message news:E9C52238-1110-48B6-8D61-2AF6FEB51FEE@microsoft....

Customizing Menu (Right Click Menu)
Being on a worksheet on a right click I get a menu: -cut -copy -paste -paste special .... I would like to add some command or macro to this menu, but I don't know how to edit this menu in project view. Could some one give me a piece of advice of how to get there? Tomek Works lovely. There is no better way out than newsgroups. I feel it every day. Thanks Bernie Tomek "Bernie Deitrick" <deitbe@consumer.org> wrote in message news:%23vYfIutRDHA.2892@TK2MSFTNGP10.phx.gbl... > Tomek, > > Use the first macro below to add a button that calls the second macro b...

setting auto archive on multilple machines
Is there a way to setup auto archiving on multiple machines without going to each one to do it. Something that we can do at a central location Thanks On Tue, 28 Jun 2005 11:05:01 -0700, "DCSLICK" <DCSLICK@discussions.microsoft.com> wrote: >Is there a way to setup auto archiving on multiple machines without going to >each one to do it. Something that we can do at a central location > >Thanks Do it from the Recipient Policies in Exchange System Manager. Set the mailbox management to delete or otherwise deal with old mail. If you do want to do autoarchive to P...

Customize investment categories?
Hi, I'd like to customize the investment categories for the index funds I'm in. Some simply don't have any category, others do but it's not granular enough. How can I do that? Thanks! JD INVESTMENT OPPORTUNITY! Fork It Over! a Ground Floor Unique Dimension & Concept In the Children's Entertainment Market... For More information http://www.kidseyeview.us Anyone??? > I'd like to customize the investment categories for the index funds I'm in. > Some simply don't have any category, others do but it's not granular enough. > How can I do ...

Exchang 2K7 Custom Receive Connector
Hi, I'm using the instructions found at http://msexchangeteam.com/archive/2006/12/28/432013.aspx and am trying to create a receive connector on my Edge server to allow relaying for some hosts. My problem is that every time I create the connector my traffic starts to queue. Once I disable the connector and have only the default running all is fine. Can there only be one at a time on an edge? Am I missing the bigger picture? Thanks, Dan You can have more than 1 Receive Connectors on the Edge & the Hub as long as you're using a different IP address or the same IP addre...

List customer of a 1 company
Hi I have a multicompany setup in GP 9.0. I need to list all the customers that belong to a single company. Can please someone tell me how can this be achieved? Thanks Regards List them how? via a SQL query? via a SmartLists? a report? in Excel? When you say multicompany, do you have many company databases or do you have one database with customers for all the companies represented by your GL segments? Some hint to these questions will get us started. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "Riz"...

Macro to auto save file using the format YYMM
Hi, I recorded a macro that automatically saves a file then change the formula in cell A1 to today's date, ie =today(), then save the file. I have a problem with the last part...is there any way I can set the macro such that the file will be saved as "c:\YYMM ABC" where 'YYMM' is the format of the date (probably can be taken from cell A1) and 'ABC' is my own file name? Thanks! You can get the date right in the code: dim myFileName as string myfilename = "C:\" & format(date,"yymm") & " ABC.xls" activeworkbook.saveas fi...

HELP: Auto Populate Excel Userform fields from access query result
Hi All, How do I populate Access query results in excel userform text fields? For eg; I have a Query: "SELECT Student_ID, Course, Grade, Qtr_ID" & _ " FROM Student_Info WHERE Student_ID = " & _ Me.StudentId.Value Here Me.StudentId.Value is a text box in excel userform And here is what the query results look like: Student_ID Course Grade Quarter_ID JP1124 Math A SP01 SP1164 Phy B SP01 Here "Student_ID, Course, Grade, Quarter_ID" are the column headers ...