Identifying duplicates/unique values

A

ali

Hi,

Would anyone be able to help me with the following please.

I want to select a range of rows use a macro to identify which of thos
entries are duplicates - ie the entire row matches another row in th
selection?

In an ideal situation the duplicates will be moved to a new sheet
called duplicates - with the unique rows being moved to a new shee
called uniques.

The original data will remain in the original sheet.

Many many thanks for any help you can offe
 
F

Frank Kabel

Hi
without macros
- select your range
- goto 'Data - Filter - Advanced Filter'
- check 'Unique values' and choose your other sheet as target location
- finish the wizard
 
T

Tom Ogilvy

Although the right thought, that will evoke an error message (you can only
copy data to the active sheet). To get this to work going to another sheet,
you have to start from the destination. You should select a cell on the
destination sheet, then do Data=>Filter=>Advance Filter. You may get a
message that it can't find your data, but just click OK and continue. You
can then select in the resulting dialog and specify copy to another
location, then click in the List Range box and navigate to and highlight
your data range, (leave criteria blank), and click the unique values
checkbox, then click OK.
 
F

Frank Kabel

Hi Tom
thanks for the addition - though this seems to be a strange Excel
behaviour
Also in my Excel version (Excel 2003,German) the other way (starting
with the source sheet) works!
 
T

Tom Ogilvy

They may have changed the behavior (which is great), but I assure you it
doesn't work in xl97 and xl2000.
 
F

Frank Kabel

Tom said:
They may have changed the behavior (which is great), but I assure you
it doesn't work in xl97 and xl2000.
Click to expand...

No doubt in that :)
So maybe MS does some bugfixing/feature enhancement at least
 
Top