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

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

Bob,
assuming invalid items are in A1:A10. Full list in B1:B100.
In C1 you can use the following *array* formula (enter with
Ctrl+Shift+Enter):

=INDEX($B$1:$B$100,SMALL(IF(ISNUMBER(MATCH($B$1:$B $100,$A$1:$A$10,0)),FALSE,ROW($B$1:$B$100)),ROW()) )

The last ROW() is if you start in row 1. If you start in any other row
(say in C10) you can use

ROW()-ROW($C$10)+1

HTH
Kostis Vezerides

Bob Ettinger wrote:
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

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

Kostis,

Thanks for the help but it is not working for me. The invalid data is row
A1:A62 and the full list is rows B1:B11629. The formula is in C

Thanks

Bob

"vezerid" wrote:

Bob,
assuming invalid items are in A1:A10. Full list in B1:B100.
In C1 you can use the following *array* formula (enter with
Ctrl+Shift+Enter):

=INDEX($B$1:$B$100,SMALL(IF(ISNUMBER(MATCH($B$1:$B $100,$A$1:$A$10,0)),FALSE,ROW($B$1:$B$100)),ROW()) )

The last ROW() is if you start in row 1. If you start in any other row
(say in C10) you can use

ROW()-ROW($C$10)+1

HTH
Kostis Vezerides

Bob Ettinger wrote:
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

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

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

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

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

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

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

Have a good night and thanks

"Roger Govier" wrote:

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

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

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

Bob,

This is not an ordinary table, it doesn't seem, at all. I don't see how
there can be a meaningful relationship between your columns (vendors, items,
etc), unless you've put both vendor and item data into column B, something
you're going to want to fix before you do another thing by changing the
layout of your table. If that's the case, we can help with that. And I'm
running out of coffee.

You need to give an example of part of the table. Don't put anything in the
example except the exact data in this table. Make sure it's clear what's in
which column. Set your newsreader (Outlook Express?) to a non-proportional
font, like Courier, for consistent horizontal spacing. If you're using a web
interface to this newsgroup where the font may be difficult or impossible to
change, make the table in Notepad, using Courier, then paste it into your
post. Then we can paste it back into Notepad.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Bob Ettinger" wrote in message
...
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

The logic I need to use is as follows. If there is a vendor in column A, then
I want to check all of column B. If there is an appearance of the same vendor
in column B, then I want to delete the entire row and every row where that
vendor appears.
So in this example, AKAL appears in column A and I want to delete rows 1 & 2
because AKAI appears in column B.

Does this make it clear? If not please advise.

Thanks for the effort.

Bob



A B C D E

Invalid
Vendor Vendor Item Desc Price

ACME AKAI PDP4206EM 42 IN PLASMA 1252
AIRT AKAI PDP4216M 42 IN HD PLASMA TUNER 1046
AIWA AMA ABB1921DEW Easy Reach(TM) Refrigerat 676
AKAI AMA ABB1922FEB Easy Reach(TM) Plus Refrige 704
APEX AMA ABB1922FEQ Easy Reach(TM) Plus Refrige 704
ATT AMA ABB1922FEW Easy Reach(TM) Plus Refrige 704
BEMI AMA ABB1927DEB Easy Reach(TM) Refrigerator 882
BIC AMA ABB1927DEW Easy Reach(TM) Refrigerator 882


"Earl Kiosterud" wrote:

Bob,

This is not an ordinary table, it doesn't seem, at all. I don't see how
there can be a meaningful relationship between your columns (vendors, items,
etc), unless you've put both vendor and item data into column B, something
you're going to want to fix before you do another thing by changing the
layout of your table. If that's the case, we can help with that. And I'm
running out of coffee.

You need to give an example of part of the table. Don't put anything in the
example except the exact data in this table. Make sure it's clear what's in
which column. Set your newsreader (Outlook Express?) to a non-proportional
font, like Courier, for consistent horizontal spacing. If you're using a web
interface to this newsgroup where the font may be difficult or impossible to
change, make the table in Notepad, using Courier, then paste it into your
post. Then we can paste it back into Notepad.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Bob,

So since AKAI appears in column A, Rows 1 and 2 should be removed. But
that'd remove ACME and AIRT from the list of discontinued vendors. The
stuff in column A should be in a separate sheet. No doubt about that. Cut
that column, then paste it into another sheet. Change that sheet's name to
something like "dropped vendors." We'll call the original sheet
"Inventory." In sheet Inventory, delete the now empty column A. That will
move the vendor column, currently B, to A, and everything else will move
over also. Now follow my original instructions, except put the helper
column way over to the right somewhere beyond the columns you have stuff in.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Bob Ettinger" wrote in message
...
The logic I need to use is as follows. If there is a vendor in column A,
then
I want to check all of column B. If there is an appearance of the same
vendor
in column B, then I want to delete the entire row and every row where that
vendor appears.
So in this example, AKAL appears in column A and I want to delete rows 1 &
2
because AKAI appears in column B.

Does this make it clear? If not please advise.

Thanks for the effort.

Bob



A B C D E

Invalid
Vendor Vendor Item Desc Price

ACME AKAI PDP4206EM 42 IN PLASMA 1252
AIRT AKAI PDP4216M 42 IN HD PLASMA TUNER 1046
AIWA AMA ABB1921DEW Easy Reach(TM) Refrigerat 676
AKAI AMA ABB1922FEB Easy Reach(TM) Plus Refrige 704
APEX AMA ABB1922FEQ Easy Reach(TM) Plus Refrige 704
ATT AMA ABB1922FEW Easy Reach(TM) Plus Refrige 704
BEMI AMA ABB1927DEB Easy Reach(TM) Refrigerator 882
BIC AMA ABB1927DEW Easy Reach(TM) Refrigerator 882


"Earl Kiosterud" wrote:

Bob,

This is not an ordinary table, it doesn't seem, at all. I don't see how
there can be a meaningful relationship between your columns (vendors,
items,
etc), unless you've put both vendor and item data into column B,
something
you're going to want to fix before you do another thing by changing the
layout of your table. If that's the case, we can help with that. And
I'm
running out of coffee.

You need to give an example of part of the table. Don't put anything in
the
example except the exact data in this table. Make sure it's clear what's
in
which column. Set your newsreader (Outlook Express?) to a
non-proportional
font, like Courier, for consistent horizontal spacing. If you're using a
web
interface to this newsgroup where the font may be difficult or impossible
to
change, make the table in Notepad, using Courier, then paste it into your
post. Then we can paste it back into Notepad.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------