How to filter rows with commom N�'s in any column

Hello,
How is it posible to filter the common N� that may appear in an
coloumn

Examlpe:

A    B     C    D    E    F    
2    6     8   10  15  20
1    2     5   16  25  30
4    8   12   19  45  48
2    4   15   20  25  28
6    9   19   29  39  49
8   19  25   28  41  45

In this example I want to filter all the rows that have the commo
N�8
The filtered Result Should be shown as below:

A    B     C    D    E    F    
2    6     8   10  15  20
4    8   12   19  45  48
8   19  25   28  41  45

Thank You
Moti

--
Message posted from http://www.ExcelForum.com

0
6/4/2004 6:44:10 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
452 Views

Similar Articles

[PageSpeed] 29

Add a new column to the table, and use it to find the number.

For example, in cell J1, type the number you want to find
in cell G1, enter a heading, e.g. Found
In G2, type the formula:  =COUNTIF(A2:F2,$J$1)
Copy the formula down to the last row of data

Or, you could use conditional formatting to highlight the cells that 
contain specific numbers. There are instructions here:

   http://www.contextures.com/xlCondFormat03.html#Lottery

Moti < wrote:
> Hello,
> How is it posible to filter the common N� that may appear in any
> coloumn
> 
> Examlpe:
> 
> A    B     C    D    E    F    
> 2    6     8   10  15  20
> 1    2     5   16  25  30
> 4    8   12   19  45  48
> 2    4   15   20  25  28
> 6    9   19   29  39  49
> 8   19  25   28  41  45
> 
> In this example I want to filter all the rows that have the common
> N�8
> The filtered Result Should be shown as below:
> 
> A    B     C    D    E    F    
> 2    6     8   10  15  20
> 4    8   12   19  45  48
> 8   19  25   28  41  45
> 
> Thank You
> Moti.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
6/4/2004 6:59:00 PM
One way. Select a range in a column like a2:a200 then.
Sub filter8()
For Each c In Range("a2:a200")    'Selection
x = c.Row
If Application.CountIf(Range("a" & x & ":f" & x), 8) < 1 Then _
c.EntireRow.Hidden = True
Next
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Moti >" <<Moti.17cafk@excelforum-nospam.com> wrote in message
news:Moti.17cafk@excelforum-nospam.com...
> Hello,
> How is it posible to filter the common N� that may appear in any
> coloumn
>
> Examlpe:
>
> A    B     C    D    E    F
> 2    6     8   10  15  20
> 1    2     5   16  25  30
> 4    8   12   19  45  48
> 2    4   15   20  25  28
> 6    9   19   29  39  49
> 8   19  25   28  41  45
>
> In this example I want to filter all the rows that have the common
> N�8
> The filtered Result Should be shown as below:
>
> A    B     C    D    E    F
> 2    6     8   10  15  20
> 4    8   12   19  45  48
> 8   19  25   28  41  45
>
> Thank You
> Moti.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Don
6/4/2004 7:04:43 PM
Hi Moti,
There may be a better answer from someone, but if not,=20
using your example, do the following:
1.  Select an empty cell near but not in the range of your=20
data.  Place the test value in that cell (e.g. 8).
2.  Use column G to hold a counter for each row.  In each=20
G cell,  insert the countif function where the range is=20
the data in that row and the criteria is the test value=20
cell in absolute reference.
3.  Apply autofilter to the data.  Filter column G for any=20
value greater than 0 (i.e. there's at least one occurence=20
of the test value in that row)

Hope this helps you.  hcj

>-----Original Message-----
>Hello,
>How is it posible to filter the common N=BA that may appear=20
in any
>coloumn
>
>Examlpe:
>
>A    B     C    D    E    F   =20
>2    6     8   10  15  20
>1    2     5   16  25  30
>4    8   12   19  45  48
>2    4   15   20  25  28
>6    9   19   29  39  49
>8   19  25   28  41  45
>
>In this example I want to filter all the rows that have=20
the common
>N=BA8
>The filtered Result Should be shown as below:
>
>A    B     C    D    E    F   =20
>2    6     8   10  15  20
>4    8   12   19  45  48
>8   19  25   28  41  45
>
>Thank You
>Moti.
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
6/4/2004 7:25:23 PM
Hi Moti,

Here's another way,

1) leave cell 14 blank
2) cell 15 =COUNTIF(A2:F2,8)>0
3) select the range of cells you want sorted
4) Data > Filter > Advanced Filter
5) use $A$14:$A$15 as your criteria
6) click OK

Hope this helps!

In article <Moti.17cafk@excelforum-nospam.com>,
 Moti <<Moti.17cafk@excelforum-nospam.com>> wrote:

> Hello,
> How is it posible to filter the common N� that may appear in any
> coloumn
> 
> Examlpe:
> 
> A    B     C    D    E    F    
> 2    6     8   10  15  20
> 1    2     5   16  25  30
> 4    8   12   19  45  48
> 2    4   15   20  25  28
> 6    9   19   29  39  49
> 8   19  25   28  41  45
> 
> In this example I want to filter all the rows that have the common
> N�8
> The filtered Result Should be shown as below:
> 
> A    B     C    D    E    F    
> 2    6     8   10  15  20
> 4    8   12   19  45  48
> 8   19  25   28  41  45
> 
> Thank You
> Moti.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
0
domenic22 (716)
6/4/2004 7:39:56 PM
Reply:

Similar Artilces:

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...

columns in RMSSO
Hi There, Is it possible to change the columns on the forms ?? For example: Receiving po contains: Type, Item Lookup Code, Reorder Number, Description, OtyOrd, QtyRTD, QtyRcv, Cost, Price, NewPrice, Extended We need: Item Lookup Code, Description, MPQ, Price, BinLocation We need to modify few other forms as well. I think I've seen that in one of the add-ons while ago, but I cant remember which one was it and I'm not 100% sure if I really did. Thanks, Arthur ...

Deleting Non-Duplicate Rows
Have done tihs is the past, but can't remember how: Have a sheet with 9500+ rows. Column C contains a storage bin number. Want to delete all rows that DO NOT have a duplicate (trying to resolve items that have a duplicate bin number.) Have sorted the sheet on Column C. THX. . . -- BillW ------------------------------------------------------------------------ BillW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27516 View this thread: http://www.excelforum.com/showthread.php?threadid=470299 You could use a helper column of formulas: =countif(c:c,c1)...

Junk email filter
I did an upgrade from 2002 office. The Junk email filter works great only on the Inbox. I check three different eamil accounts and have rules to move the messages to their respective folders that I created for each email account. The Junk email filter does not work on these folders. the rules are as follows; Apply this rule after the message arrives through the "Specified" account, move it to the "Specified" folder. Thanks Tom Check the order of your filters. (tools-rules wizard). Move the rules up or down to satisfy your needs. If your rules to move to specifi...

Completely Turning off Junk Email Filtering in Outlook 2003
Does anyone know how to completely turn off Junk Email filtering Outlook 2003? We want all emails, regardless if spam, to go directly to every users' Inbox. Thanks. That's a first, but from the end user standpoint Tools | Options | Preferences | Junk E-mail. Only other way I can think of is when the profile is generated (assuming CIW is in use) and Exchange is in use, is to turn off cached mode. "Chris H" <ChrisH@discussions.microsoft.com> wrote in message news:C250A388-DEFA-4402-8449-80FF3FC91B4C@microsoft.com... > Does anyone know how to completely turn o...

anyway to make outlook to execute some vbs or js script for complex filtering
or would I have to make use of .net? at this place there is no .net implemented Maybe the outlook vba group might be a better place to post "MSDN newsgroup" <NoOne@allMailSunken.net> wrote in message news:%23Mp53SYEKHA.4184@TK2MSFTNGP02.phx.gbl... > or would I have to make use of .net? > > at this place there is no .net implemented > "MSDN newsgroup" <NoOne@allMailSunken.net> wrote in message news:%23Mp53SYEKHA.4184@TK2MSFTNGP02.phx.gbl... > or would I have to make use of .net? > > at this place there is no .net implemented mic...

Reference column question
Setting up a tabulation speadsheet,has 40 rows and 10 columns.Question is when I do my formula which is a subtraction for each column,I want to use a refernce # from the first row of each column =Sum(F1-D3)=SUM (G1-D3)and so to=Sum(P1-D3)Rather than enter each manually,because cut and paste adds 1 to each column how can I get to use f1 as reference for that column G1 for that column.Thanx If I understand correctly, try putting a $ in front of the row reference. Also, sum is not necessary for what you are doing. =F$1-D3 as you copy that down it becomes =F$1-D4 copied across to the ri...

Compare and Highlight Rows
I have an excel file with two worksheets. is there a way to programatically compare the two worksheets to find matches and highlight those matches on each worksheet. They both have the same columns. I want to compare the InspectionID column. The Inspection ID column may have duplicates in both of the worksheets. I have never done anything in excel above the beginner level. However, I am a very skilled VBA programmer (programmed in ACCESS for over 10 years). Thank you You may find it easier to use Conditional formatting instead: For instance, with Sheet1 column A selected, and cel...

Autofit Row Height #3
Hello, I have a column on sheet 1 that is set to wrap text so that the row height increases and decreases as more text is entered into the cell. This works fine. Users enter a number on sheet two where there is a lookup function that returns the appropriate text from sheet 1. The problem is that when the text is returned by the function, the row height does not adjust to fully display all of the text in the cell. Is there a way of automating this? Any help would be appreciated. -- Thanks, MarkN ...

Filtering, Counting and Denying
Sounds like a strange subject name, but here's what I want to do. I have a table called BOOKINGS. In it are all the daily car bookings - past, present and future. Each booking is identified by DEPARTMENT (1=DA, 2=Finance, 3=Exec, etc), and BOOKINGDATE. I want to be able to deny any department - other than 1-DA - the opportunity to book out one of our vehicles if the total external department count of bookings exceeds two on any given day. Not sure how to go about this, but I think I need to filter out the daily bookings by date, then count all records that are not department 1, and t...

All Columns are not to move
How can I stop the columns from moving to the left of the Table? -- Roger On Sun, 3 Feb 2008 17:28:39 -0800, Roger <Roger@discussions.microsoft.com> wrote: >How can I stop the columns from moving to the left of the Table? You'll have to explain what on Earth you're talking about, Roger. What columns are "moving"? How? John W. Vinson [MVP] Good evening John, I found my own answer:by bringing the cursor to the first cell, the first column will not scroll to the left anymore; however, when I bring the cursor to any other cell and scroll to the rig...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

Sort column with first and last name by last name
Hello, I have a mailing list with 10,000 names. The first cell has first an last name in the same cell. First name is listed first. Is there formula to sort and/or separate text in a cell. I would like to sort o separate the first name from the last in the cell to allow for a mai merge by name. Using Excel 2002. Thank you -- Message posted from http://www.ExcelForum.com If you have just first and last names separated by a space, Data>Text to Columns would be the easiest method to split into two columns. If more than that, like names with van or von or de etc. you may need a different m...

conditional formating: ifs and highlighting rows
my spreadsheet documents error incidents, with each row showing the date the incident was discovered (column A) and the date it was resolved (column I). it also calculates networkdays (column J) -- unless column I=0 -- and references an array of holiday dates on another sheet. i would like to create a conditional format that will identify rows with an incident, but no resolution date, then highlight the row and possibly even show "unresolved" in column J cell of that row. Thanks! Well, you have to decide which way you want to go with this. If you leave column J ...

How do I find duplicate rows in a list in Excel, and not delete it
I have a long list of data in Excel that is 3 columns wide. I need to find and save only the duplicate rows but don't want to delete them. Instead, I could delete the unique rows and keep the duplicate rows. In the customer assistance, I found out how to delete duplicate rows and save the unique rows, but this is exactly opposite of what I want to do. This is one option: =COUNTIF($A$4:$A$18,A4)>1 copy down, and use a Autofilter to find all True HTH Ola Sandstr�m Picture encl.: http://www.excelforum.com/attachment.php?attachmentid=3498&stc=1 +---------------------------...

Filter suddenly limited
I have an excell file that is about 2000 rows. I use the filter t locate rows with same names, The filter now only works thru row 1256. Any suggestions what is causing this -- Neil Hanawal ----------------------------------------------------------------------- Neil Hanawalt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1589 View this thread: http://www.excelforum.com/showthread.php?threadid=27380 Hi see: http://www.contextures.com/xlautofilter02.html#Limits -- Regards Frank Kabel Frankfurt, Germany Neil Hanawalt wrote: > I have an excell file that is a...

Finding unique numbers in a column
Is there any way to find the number of unique values among a set o values in a column in an excel sheet. I would also like to know th number of times each value appears in the column. Thank -- coolkid39 ----------------------------------------------------------------------- coolkid397's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2431 View this thread: http://www.excelforum.com/showthread.php?threadid=37924 There was also a similar thread under Excel Worksheet functions titled "Delete duplicates". Solution provided was to use: Data>Filter&g...

Stacking columns!!!
I have columns with headings like: NAME - AGE - EXPERIENCE. I have many sheets like this and I have to import all of them into on sheet -- where all the entries are stacked one below the other. There was already an enquiry about this in this forum -- but there wa no reply. If any has a solution, it would be appreciated. thanks. Subbu :confused -- kmsubb ----------------------------------------------------------------------- kmsubbu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=844 View this thread: http://www.excelforum.com/showthread.php?threadid=276...

Setting up a filter
We are using Exchange 2000 on a Windows 2000 server. Users are getting inappropriate emails and the problem is growing. How do I go about setting up a filter so that it catches these emails before they get to the users mailbox? you can try this http://support.microsoft.com/default.aspx?scid=kb;en-us;319356 but for something more effective, you could look into a third-party product for spam filtering there are many of them out there, one that i've found very effective is SPS from TrendMicro; has many options to configure Seth in Boston "Jim in Cleveland" <JiminCleve...

Help with column
I have a datafeed that comes to me everyday i need to format one column of around 1000 rows so it multiples the total column but a 1000 so getting rid of the decimal point i.e =A3100.00 becoming 10000 , i also need to do this using a macro. can anyone help=20 Regards=20 Paul Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow Cells(i, "A").Value = Cells(i, "A").Value * 100 Next i -- HTH Bob Phillips "lennymos123" <paul@palo.co.uk> wrote in message news:1115651206....

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

How do I pivot a range of cells, i.e. from column to row?
How do I pivot/transpose the data from a range of cells from, say, a column to a row? I imagine it should be very simple, but I haven't found out how yet. For instance: row# 1 data 1 2 data 2 3 data 3 4 data 4 to column# 1 2 3 4 data1 data2 data3 data4 ...

Column, Row Font Size
For some reason the column letters and the row numbers - at the very top and far left on any worksheet - have become so small on a particular worksheet as to be illegible. What have I done, and how do I undo it? If it only happens on one worksheet, maybe you've set the zoom too small. From the menu bar: View|Zoom... (and resize to look nice) There's a Zoom icon on the Standard toolbar that you could use, too. (My icon is at the far right of that toolbar.) Old Red One wrote: > > For some reason the column letters and the row numbers - at the very top and > far left ...

Address labels to columns
I have an employee who has saved a mailing list as the actual address labels within Word. We have pulled this listing over to Excel wanting to change it from the current layout to a more practical database style listing The format is currently Name Company Address City, State Zip We would like to transpose each "label" into the following format without having to physically cut an paste. Name Company Address City State Zip Temporary solutions we have started trying is to use the Array Forumla Transpose. This at leasts pulls 1 label into a row, but is there a macro or oth...