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!

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

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" wrote in message
...
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!

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

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?

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

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

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

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

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

Thanks a million Max! I'll try this out.

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

You're welcome, Dan !
Thanks for feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DanJanowiak" wrote in message
...
Thanks a million Max! I'll try this out.