Hi Bob

I guess our posts crossed, as I was asking what constitutes valid and
what constitutes invalid?
I need to get some "shuteye" now (11:00pm UK), but if you haven't had a
result by the morning I will take another look then.

--
Regards

Roger Govier


"Bob Ettinger" wrote in message
...
This is a partial result of the sort. ACME and AIRT are invalid andso
are
AMER in Col A. When I run the formula


A B C D
1 ACME
2 AIRT
3 AIWA
4 AKAI PDP4206EM 42 IN PLASMA 1252
5 AKAI PDP4216M 42 IN HD PLASMA MONITORNO TUNER 1046
6 AMA ABB1921DEW Easy Reach(TM) Refrigerator5 676
7 AMER 2645 ENT CENTER 9
8 AMER


"Roger Govier" wrote:

Hi Bob

Did you carry out Earl's instruction to copy the second set of data
below the first and do the sort before entering the formula?

--
Regards

Roger Govier


"Bob Ettinger" wrote in
message
...
Earl,

First, thanks for the help. I seem to be having a problem getting
this part
to work

Put the following formula in a helper column. This presumes the
original
column is A. This one goes in row 2
=IF(OR(A2=A1,A2=A3),"invalid",0)

It does not put "invalid" in the cell.

Thanks again

Bob

"Earl Kiosterud" wrote:

Bob,

Here's a non-macro way. If you'll be doing this regularly, you
might
want a
macro that does it.

Paste the invalid column directly under the valid one, then sort
on
that
column. If there is data in other columns that's part of the
table,
BE
CAREFUL!. Excel will gladly perform the "sort of disaster" if
you're
not
familiar with its sorting. See "Sorting in Excel" at
http://www.smokeylake.com/excel/excel_truths.htm.

Put the following formula in a helper column. This presumes the
original
column is A. This one goes in row 2
=IF(OR(A2=A1,A2=A3),"invalid",0)

Select the column
Edit - Go to - Special - Formulas
Select only "Text," not Numbers, Logicals or Errors.
OK
Now all the duplicate cells (from either list) of the invalids
are
selected

Edit - Delete - Entire Row.
This should remove the invalids and their duplicates

Remove the helper column

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Bob Ettinger" <Bob wrote in
message
...
One spreadsheet has a list of invalid items. The second
spreadsheet
contains
a list of all items.

I want to take the 2 and make a new list of only Valid items.

Thanks