I've got a question about a macro. It should function in a Win XP
environment with Office 2000.
I'd like to know how to construct a macro to do what follows:
First of all, I need a macro that will delete any number that appears
more than once (mostly twice) in a very extensive list. I'd rather not
do this manually.

The second question is about comparing two sheets of different
workbooks.
I need to mark numbers that appear on the second sheet, but not on the
first to be marked in a specific color, but only this way, not the
other way around.
The third thing I need is a macro (?) to add numbers that appear in
the first sheet to be added to the second.

This may seem a rather extensive set of questions.
On the other hand it may seem macros that are rather easy to
construct, but I am just a newbie in VBA.

I'd appreciate any help. If it's not possible to do so, that's ok too.
I'd just like to know if it's possible and if so how.

You can do most of what you want with countif and the autofilter under
Data=Filter=Autofilter

to find duplicates,
in B2
=Countif($A$2:A2,A2)1

then drag fill down the list.

in rows where there is a True, then you want to delete (these are duplicates
of earlier numbers)

Select A1, then do Data=Filter=Autofilter

Filter on True using the dropdown in column B.

Select the data and delete entire rows - only the visible rows will be
deleted (so don't select row 1, the header row).

Use countif again to compare

in sheet 2

In B2
=countif(Sheet1!$A:$A,A2)

Then drag down the Sheet.

anything showing 1 or greater is a match.

Use the filter. You can select custom in the dropdown to use a criteria
like Greater than 0 or whatever criteria you want to specify.

To get the list to copy, I assume they are the ones not matching Sheet2, use
the countif formula on Sheet1 and filter on 0.

Copying a filtered range is the same as deleting - only acts on the visible
rows.

If you want a macro, you can use exactly the same technique, having the
macro insert the formulas, apply the filter and take the appropriate action.

--
Regards,
Tom Ogilvy


"Stevie" wrote in message
om...
I've got a question about a macro. It should function in a Win XP
environment with Office 2000.
I'd like to know how to construct a macro to do what follows:
First of all, I need a macro that will delete any number that appears
more than once (mostly twice) in a very extensive list. I'd rather not
do this manually.

The second question is about comparing two sheets of different
workbooks.
I need to mark numbers that appear on the second sheet, but not on the
first to be marked in a specific color, but only this way, not the
other way around.
The third thing I need is a macro (?) to add numbers that appear in
the first sheet to be added to the second.

This may seem a rather extensive set of questions.
On the other hand it may seem macros that are rather easy to
construct, but I am just a newbie in VBA.

I'd appreciate any help. If it's not possible to do so, that's ok too.
I'd just like to know if it's possible and if so how.

Hi Stevie
you may have a look at the following sites
http://www.cpearson.com/excel/deleti...eDuplicateRows
http://www.cpearson.com/excel/duplic...tractingCommon
http://www.cpearson.com/excel/duplic...#InOneNotOther

Frank

Stevie wrote:
I've got a question about a macro. It should function in a Win XP
environment with Office 2000.
I'd like to know how to construct a macro to do what follows:
First of all, I need a macro that will delete any number that appears
more than once (mostly twice) in a very extensive list. I'd rather
not
do this manually.

The second question is about comparing two sheets of different
workbooks.
I need to mark numbers that appear on the second sheet, but not on
the
first to be marked in a specific color, but only this way, not the
other way around.
The third thing I need is a macro (?) to add numbers that appear in
the first sheet to be added to the second.

This may seem a rather extensive set of questions.
On the other hand it may seem macros that are rather easy to
construct, but I am just a newbie in VBA.

I'd appreciate any help. If it's not possible to do so, that's ok
too.
I'd just like to know if it's possible and if so how.

Thanks everybody for your help. I've got the first part to work.
I'll make myself more clear though.
I didn't mean single columns but entire rows, composed of several
columns.
I want to compare cross-workbook two sheets.
The first one contains only unique rows now.
They have to be compared with the rows of the second sheet. Additional
rows that don't appear in the first sheet, and are to be deleted
afterwards
should get a different color, so it will be easy to distinguish them.

An example

Sheet 1, workbook 1: John French 12 2004
Sheet 2, workbook 2: " " " 2003
This second row should be marked with a different color.

Is this possible with a function? Or does this require a macro?

Thanks in advance for any help.

"Frank Kabel" wrote in message ...
Hi Stevie
you may have a look at the following sites
http://www.cpearson.com/excel/deleti...eDuplicateRows
http://www.cpearson.com/excel/duplic...tractingCommon
http://www.cpearson.com/excel/duplic...#InOneNotOther

Frank

Stevie wrote:
I've got a question about a macro. It should function in a Win XP
environment with Office 2000.
I'd like to know how to construct a macro to do what follows:
First of all, I need a macro that will delete any number that appears
more than once (mostly twice) in a very extensive list. I'd rather
not
do this manually.

The second question is about comparing two sheets of different
workbooks.
I need to mark numbers that appear on the second sheet, but not on
the
first to be marked in a specific color, but only this way, not the
other way around.
The third thing I need is a macro (?) to add numbers that appear in
the first sheet to be added to the second.

This may seem a rather extensive set of questions.
On the other hand it may seem macros that are rather easy to
construct, but I am just a newbie in VBA.

I'd appreciate any help. If it's not possible to do so, that's ok
too.
I'd just like to know if it's possible and if so how.

It depends on your situation.

If you are willing to make a composite column or use slow array formulas, it
should be possible with functions and manual manipulation.

A macro might be easier, but that would be your call.

Frank has given your references - all you need to do is expand the
techniques to work in your environment.

--
Regards,
Tom Ogilvy

"Stevie" wrote in message
om...
Thanks everybody for your help. I've got the first part to work.
I'll make myself more clear though.
I didn't mean single columns but entire rows, composed of several
columns.
I want to compare cross-workbook two sheets.
The first one contains only unique rows now.
They have to be compared with the rows of the second sheet. Additional
rows that don't appear in the first sheet, and are to be deleted
afterwards
should get a different color, so it will be easy to distinguish them.

An example

Sheet 1, workbook 1: John French 12 2004
Sheet 2, workbook 2: " " " 2003
This second row should be marked with a different color.

Is this possible with a function? Or does this require a macro?

Thanks in advance for any help.

"Frank Kabel" wrote in message
...
Hi Stevie
you may have a look at the following sites
http://www.cpearson.com/excel/deleti...eDuplicateRows
http://www.cpearson.com/excel/duplic...tractingCommon
http://www.cpearson.com/excel/duplic...#InOneNotOther

Frank

Stevie wrote:
I've got a question about a macro. It should function in a Win XP
environment with Office 2000.
I'd like to know how to construct a macro to do what follows:
First of all, I need a macro that will delete any number that appears
more than once (mostly twice) in a very extensive list. I'd rather
not
do this manually.

The second question is about comparing two sheets of different
workbooks.
I need to mark numbers that appear on the second sheet, but not on
the
first to be marked in a specific color, but only this way, not the
other way around.
The third thing I need is a macro (?) to add numbers that appear in
the first sheet to be added to the second.

This may seem a rather extensive set of questions.
On the other hand it may seem macros that are rather easy to
construct, but I am just a newbie in VBA.

I'd appreciate any help. If it's not possible to do so, that's ok
too.
I'd just like to know if it's possible and if so how.