Bob,
This ain't working at all.
The formula should have yielded "invalid" only where there are two identical
items, one above the other. This is after you've copied one column under
the other, then sorted the column. So the formula is not working. But it
also appears you don't have identical items (one from each list), one under
the other. Kiosterud's law of difficulty states that it's 4 times more
difficult to solve a problem that has two problems embedded in it. :)
I think you need to describe your two lists more fully. Is all that stuff
in a single column in each original list? Show what's in each list (valid
items and invalid items), indicating exactly what is in which column(s). Be
sure to have set your newsreader to a non-proportional font, like Courier,
for consistent spacing. Don't leave anything to assumption on our parts.
Leave out the helper column.
Then show what you got when you put one list under the other (same column),
then sorted it.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Bob Ettinger" wrote in message
...
First on the spreadsheet all the invalid is in Col C. Col A is the item,
ACME, AIRT, VORN are all "invalid, the others are all valid.
I am sure that I am missing something but not sure what.
Thanks for your help
A B C
1 ACME
2 AIRT invalid
3 AIWA invalid
4 AKAI PDP4206EM invalid
5 AKAI PDP4216M invalid
6 AMA ABB1921DEW invalid
7 AMA ABB1922FEB invalid
8 AMA ABB1922FEQ invalid
10 AMA ABB1922FEW invalid
11 AMA ABB1927DEB invalid
12 AMA ABB1927DEW invalid
13 AMA ABB2221FEB invalid
14 AMA ABB2221FEW invalid
15 VORN FA1000730 invalid
16 VORN FA1000731 invalid
17 VORN HU1001211 invalid
18 VORN HU1001311 invalid
19 VORN HU1001614 invalid
20 VORN MD10002 invalid
21 VORN MD10006 invalid
22 VORN MD10008 invalid
23 VORN MD10010 invalid
24 VORN PCZ150 invalid
25 VORN invalid
26 WAST 1032 invalid
27 WAST 1032HE invalid
28 WAST 3140 invalid
"Roger Govier" wrote:
Hi Bob
Let's go back tot he beginning.
What constitutes valid in List A and Invalid in List B?
Can you give some examples?
--
Regards
Roger Govier
"Bob Ettinger" wrote in message
...
Yes I did. Tried it several times
"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