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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DanJanowiak - 28 Mar 2006 23:10 GMT

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!

Reply to this Message

Max - 28 Mar 2006 23:57 GMT

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

> Hello. I have a Excel file that contains a large list of Tracking Numbers.
>
[quoted text clipped - 18 lines]
>
> Thanks for you help!

Reply to this Message

DanJanowiak - 29 Mar 2006 01:04 GMT

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?

Reply to this Message

Max - 29 Mar 2006 02:12 GMT

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

Reply to this Message

Max - 29 Mar 2006 02:23 GMT

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

Reply to this Message

DanJanowiak - 29 Mar 2006 18:08 GMT

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

Reply to this Message

Max - 29 Mar 2006 23:55 GMT

You're welcome, Dan !
Thanks for feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message