How to Filter cells and save the file with certain criteria?

Hello.  I have a Excel file that contains a large list of Tracking Numbers.  

The tracking numbers are from two sets of order types- Internet orders and 
Mail Orders.  

Orders that are from the Internet are matched up with a order number such as 
"5678".  Orders from the mail-order side are designated by initials "MO".

So in Excel it'll look something like this:

trackingnumber	orderid

123365656666     5467
152155896345     5468
123365634567     MO
152134567789     5469
152151234563     MO

Is there a way to eliminate the Mail-Order rows and save the Excel file to 
just have the Internet order tracking numbers?  

Thanks for you help!
0
3/28/2006 10:10:02 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
361 Views

Similar Articles

[PageSpeed] 39

> Is there a way to eliminate the Mail-Order rows
> and save the Excel file to
> just have the Internet order tracking numbers?

On a spare copy ..
try an autofilter for "MO" in col B
then delete all the "MO" rows,
then remove autofilter

Data assumed in cols A and B
Insert a new top header row
Click Data > Filter > Autofilter
Select "MO" from the droplist in B1
Select all the filtered rows (select the row headers)
Right click > Delete Row
Remove autofilter

The remainder will be what you're after
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DanJanowiak" <DanJanowiak@discussions.microsoft.com> wrote in message
news:505106CC-B42B-46DE-889D-B0A0EED41007@microsoft.com...
> Hello.  I have a Excel file that contains a large list of Tracking
Numbers.
>
> The tracking numbers are from two sets of order types- Internet orders and
> Mail Orders.
>
> Orders that are from the Internet are matched up with a order number such
as
> "5678".  Orders from the mail-order side are designated by initials "MO".
>
> So in Excel it'll look something like this:
>
> trackingnumber orderid
>
> 123365656666     5467
> 152155896345     5468
> 123365634567     MO
> 152134567789     5469
> 152151234563     MO
>
> Is there a way to eliminate the Mail-Order rows and save the Excel file to
> just have the Internet order tracking numbers?
>
> Thanks for you help!


0
demechanik (4694)
3/28/2006 10:57:11 PM
Thanks, that seems to work!

Is there a Macro that can be used so when I copy the data from one file to 
another I do not always have to use the AutoFilter?
0
3/29/2006 12:04:02 AM
"DanJanowiak" wrote:
> Thanks, that seems to work!
> Is there a Macro that can be used
> so when I copy the data from one file to
> another I do not always have to use the AutoFilter?

Try recording a macro when you do it manually

Perhaps an alternative option to consider if you're doing this frequently is
this non-array formulas approach

A sample construct is available at:
http://www.savefile.com/files/7541958
Auto-Filter to another sheet.xls

Assume the source data is in sheet: X,
cols A and B, from row1 down

123365656666     5467
152155896345     5468
123365634567     MO
etc

In another sheet: Y (say),

Put in A1:
=IF(ISERROR(SMALL($C:$C,ROW())),"",
INDEX(X!A:A,MATCH(SMALL($C:$C,ROW()),$C:$C,0)))
Copy A1 to B1

Put in C1: =IF(X!B1="","",IF(X!B1="MO","",ROW()))

Select A1:C1, fill down to say C200
to cover the max expected extent of data in X

Y will auto-return the required results* from X,
with all lines neatly bunched at the top,
*i.e. lines w/o "MO" in X

To refresh the data in X,
just select the entire sheet, press Delete key
(this clears the entire sheet)
then paste(or use paste special > values) the new data

Y will then auto-update to return the results for the new data

Note: Do not *delete* cols A and B in X
as this will foul up the formulas in Y.
Just *clear* the data in cols A & B with the Delete key
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


0
demechanik (4694)
3/29/2006 1:12:41 AM
And if we wanted to return only the lines with "MO" from X
we would just need to tweak this criteria formula in Y

> Put in C1: =IF(X!B1="","",IF(X!B1="MO","",ROW()))

to

Put in C1: =IF(X!B1="","",IF(X!B1<>"MO","",ROW()))
and then copy C1 down

(No change to formulas in cols A & B in sheet: Y)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


0
demechanik (4694)
3/29/2006 1:23:01 AM
Thanks a million Max!  I'll try this out.
0
3/29/2006 5:08:14 PM
You're welcome, Dan !
Thanks for feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DanJanowiak" <DanJanowiak@discussions.microsoft.com> wrote in message
news:4575C1A3-6FA0-4A1E-9B0A-C451AB1B10CA@microsoft.com...
> Thanks a million Max!  I'll try this out.


0
demechanik (4694)
3/29/2006 10:55:12 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

#Delete Mark in Bound Memo filed
I have form that has bound memo field, sometime, no sure how it happen, the memo filed is filled with #Delete. My application is a stand alone program. Kindly advise what can cause this and how to avoid it from happening. -- TS Lim When was the last time you performed a compact and repair? Is you db split? Does each user have their own copy of the front-end? Please checkout http://www.granite.ab.ca/access/corruptmdbs.htm http://www.granite.ab.ca/access/corruption/symptoms.htm http://office.microsoft.com/en-ca/access/HA011865661033.aspx - No very helpful but directly from MS ...

CSV File Problem
Can any one explain why the following problem occurs please? A colleague has a small list of data which is a csv file exported from SQL. Column One is a serial number from 1 to 29. Column 2 is a number of transactions which have occurred.(it should look like example A below in Excel) 1 475 1,475 2 1732 21,732 3 1670 31,670 4 2176 42,176 5 608 5,608 6 1579 61,579 7 43101 743,101 8 54512 854,512 9 51258 951,258 10 61050 1,061,050 A B If I double click on the csv file, it opens in Excel but c...

Customize Current View: Filter Problem
Hello, Using Outlook 2002, I have created a category called "Hidden" i outlook. In my calendar, I put all the appointments I don't want t appear in this calendar (i.e. daily reminders). I then filter out all these "Hidden" items by selecting -- View--> Current View--> Customize Current View--> Filter--> More Choices--> Categories--> Hidden (the category I created)--> OK--> SQL--> Edit these criteria directly--> And then I change: ("DAV:isfolder" = false AND "DAV:ishidden" = false) AN ("urn:schemas-microsoft...

CFile (delete file)
How can I delete all files that end with ".temp" in some folder? CFile::Remove remove unlink -- cheers, Alok Gupta Blogs: http://wdevs.com/thatsalok "Petar Popara" <my.fake@mail.net> wrote in message news:Op6#URKfFHA.2644@TK2MSFTNGP09.phx.gbl... > > How can I delete all files that end with ".temp" in some folder? > > SHFileOperation() will and it supports wildcards! DeleteFile() will operate on one file at a time. "Petar Popara" <my.fake@mail.net> wrote in message news:Op6%23URKfFHA.2644@TK2MSFTNGP09.phx.gbl... > >...

Opening an *.MSG file with it's respective attachment.
Hello, The file *.MSG is a message file. This can be viewed by any editor, like Notepad. However, when there's an attachment to it, we see garbage. Is there a way to open the attachments present inside the *.MSG files ? Thank you Yes, by opening it with Outlook. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox!...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

import a 123 file
My excel version cannot open a Lotus 123 file. Is there an import feature to move the data from Lotus 123 to Excel? Thanks No, you need to convert the file either from Lotus 123 or get commercial converter -- Regards, Peo Sjoblom "Frank" <Frank@discussions.microsoft.com> wrote in message news:59B55F98-4C79-4917-928B-F1B309660730@microsoft.com... > My excel version cannot open a Lotus 123 file. Is there an import feature > to > move the data from Lotus 123 to Excel? > Thanks > > ...

Where is the cache files folder located for windows media player?
Does windows media player have a cache files folder? Where is the cache files folder located for windows media player? Or how do I find the cache files folder for windows media player? Thank You for your Help. Bob Background info: I have a desktop AMD 4400+ 64X2 4GB memory 320GB HD(way bigger than I need) Acer computer (AM3100-U3201A) I purchased new February 2008 running 32-bit MS Vista Home Premium SP2 box. I use MS auto updates. I think it has onboard shared memory for video and sound. My ISP is Comcast 12mbps with Motorola SURFboard SB5101 Cable Modem direct to...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

Filters in Excel 2003
I am using Filters in Excel 2003. Every cell in sheet B is linked to every cell in sheet A. That way I can alter data in B without corrupting the original data in A. I then highlight the cells in B that I want to assign a filter to and select Advanced Filter. I have no criteria so I do not set that. I click OK. I then select Filter again and this time select Auto Filter. Drop down arrows appear at the top of my columns. Great, no problems so far, I then select from the drop down list the criteria that I want to filter. Again no problems, my list filters correctly. But once I have do...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- 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" butt...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Renaming Partial File Names
Is it possible to rename part of a file name? (almost like a find and replace) My database pulls in the excel file names from a directory, with it's subfolders and contents as well. Every file begins with "Kay Form". Kay no longer works in our department, and I'd like to run a loop that replaces "Kay Form" with "Featured Track". I don't know how to do a partial replace though. Thank you in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201001/1 Checkout the Replace function. I...

Phone Number Filtering
I am trying to sort special numbers in a long list of telephone numbers (7 digits). For example: a. Highlight yellow those numbers which have 3 consecutive digits (eg 2281555). b. Highlightt blue those numbers which have two double numbers (eg 4431122). c. Highlight green those numbers which are in a certain sequence (eg 2281234). I tried to use Left, Right and Mid formulas but the game becomes very complicated and hard to troubleshoot. Any better solution? The only effective way I know how to do this, is to use macros. Your samples a & b, are fairly simple to do. Sample c, could be d...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

unknown .edb file
I have error messages in the log files that references a corrupt database named C:\Program Files\exchsrvr\Exchangeserver_Outlook.edb. "Outlook" is the name of the server. The problem is that this database is not listed in any of the Mail Stores or Public stores. I want to run the repair utilities on it, but since I'm not sure what it is used for, I don't know how I can restore it if the repair goes bad. Does anyone know what this is used for or how I can determine that myself? BTW, the database always has a current time stamp, so I assume that it is used by something....

printing imported gif files in Excel
When printing gif file with transparent background on the grey(25%) background of Excel non-transparent part of the picture has white border around it. Is there any way to prevent it? Thank you in advance... Tim ...

Copying dbx files from a CDR back into Outlook
My computer was recently hijakced and I had to reinstall everything. I did however save all of my folders in Outlook onto a CD Rom and I do not know how to transfer them back so I can use them. Also I had an animated version of outlook where you could choose loads of diffent stationery and I cannot seem to find that upgrade anaywhere. Can you help me? Tee <tsahai33@hotmail.com> wrote: > My computer was recently hijakced and I had to reinstall > everything. I did however save all of my folders in > Outlook onto a CD Rom and I do not know how to transfer > them back so...

Budget Wont Save
I have have Money 2002 for awhile and have never been able to save a budget and start a new one. I decided to start over with a new file, but once I get all my info back in there, it still won't save. MS has no info in the KB about this. Anyone else have this issue? ...