software

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
quot;5678quot;. Orders from the mail-order side are designated by initials quot;MOquot;.

So in Excel it'll look something like this:

trackingnumberorderid

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!

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

On a spare copy ..
try an autofilter for quot;MOquot; in col B
then delete all the quot;MOquot; rows,
then remove autofilter

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

The remainder will be what you're after
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;DanJanowiakquot; gt; wrote in message
...
gt; Hello. I have a Excel file that contains a large list of Tracking
Numbers.
gt;
gt; The tracking numbers are from two sets of order types- Internet orders and
gt; Mail Orders.
gt;
gt; Orders that are from the Internet are matched up with a order number such
as
gt; quot;5678quot;. Orders from the mail-order side are designated by initials quot;MOquot;.
gt;
gt; So in Excel it'll look something like this:
gt;
gt; trackingnumber orderid
gt;
gt; 123365656666 5467
gt; 152155896345 5468
gt; 123365634567 MO
gt; 152134567789 5469
gt; 152151234563 MO
gt;
gt; Is there a way to eliminate the Mail-Order rows and save the Excel file to
gt; just have the Internet order tracking numbers?
gt;
gt; Thanks for you help!
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?

quot;DanJanowiakquot; wrote:
gt; Thanks, that seems to work!
gt; Is there a Macro that can be used
gt; so when I copy the data from one file to
gt; 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:
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())),quot;quot;,
INDEX(X!A:A,MATCH(SMALL($C:$C,ROW()),$C:$C,0)))
Copy A1 to B1

Put in C1: =IF(X!B1=quot;quot;,quot;quot;,IF(X!B1=quot;MOquot;,quot;quot;,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 quot;MOquot; 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 gt; 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 amp; B with the Delete key
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
And if we wanted to return only the lines with quot;MOquot; from X
we would just need to tweak this criteria formula in Y

gt; Put in C1: =IF(X!B1=quot;quot;,quot;quot;,IF(X!B1=quot;MOquot;,quot;quot;,ROW()))

to

Put in C1: =IF(X!B1=quot;quot;,quot;quot;,IF(X!B1lt;gt;quot;MOquot;,quot;quot;,ROW()))
and then copy C1 down

(No change to formulas in cols A amp; B in sheet: Y)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Thanks a million Max! I'll try this out.

You're welcome, Dan !
Thanks for feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;DanJanowiakquot; gt; wrote in message
...
gt; Thanks a million Max! I'll try this out.