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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Is there a way to eliminate the Mail-Order rows
and save the Excel file to
just have the Internet order tracking numbers?
Click to expand...

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

---
 
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?
 
DanJanowiak said:
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?
Click to expand...

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

---
 
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()))
Click to expand...

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)

---
 
Back
Top