Earl,
You are right. Let me start from scratch. Column A has 63 rows.Each
contains one vendor which is not repeated, ie no duplicates. These are
vendors we no longer buy from.
Column B is a list of every item sold by the vendor and has 11623 rows. In
colum B there are duplicates which is fine. Each vndor sells us multiple
items So we can have "n" number of repeats in column B. The idea is that we
do not deal with some of the vendors in column B and want to delete the
vendor and all the items from column B.
Column C contains the item number, subsequent columns contain description
and price etc.
So in conclusion, the idea is to use column A to remove the vendors and
items from columns B by deleting the entire row.
Thanks again for the time and I am sorry if I was originally confusing.
"Earl Kiosterud" wrote:
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