How do I delete duplicate entries in excel?

I have been given a large database in excel and need to distill it by
removing duplicate entries. I would like the results to be displayed in a
new worksheet so I won't lose the original data. I have a basic working
knowledge of excel but I can learn.

Here's an approach to try (it might seem a bit long, but it's really pretty
easy):

Example Assumptions:
Sheet1 contains your data in cells A1:Z100
Sheet2 is where you want the extracted data to be displayed
(of course, you'll need to adjust the range references to suit your situation)

Using Sheet2:

Insert>Name>Define
Names in workbook: Sheet2!rngDest
Refers to: =Sheet2!$A$1:$Z$1

Next...still on Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!rngSource
Refers to: =Sheet1!$A$1:$Z$100

(Notice: you are on Sheet2, and creating a Sheet2 level range name, but
the referenced range is on Sheet1)

The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.

Now...set up the Advanced Data Filter:
Data>Filter>Advanced Data Filter
Select: Copy to another location
Select: Unique Values
List Range: (press F3 and select rngSource)
Criteria Range: (leave this blank)
Copy To: (press the [F3] key and select rngDest)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select rngSource each time
.....OR...if you're feeling a bit ambitious...

You can build a simple macro to automatically re-run the filter:
Press [Alt]+[F11] to open the VBA editor
Right click on the VBA Project folder for your workbook
Select: Insert>Module

Then, copy/paste this code into that module:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!rngSource").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Sheet2!rngDest"), _
Unique:=True
End Sub
'---Start of Code-------

To run the code:
Tools>Macro>Macros (or [Alt]+[F8])
Select and run: PullMatchingData


Does that help?

***********
Regards,
Ron


"antieal" wrote:

> I have been given a large database in excel and need to distill it by
> removing duplicate entries. I would like the results to be displayed in a
> new worksheet so I won't lose the original data. I have a basic working
> knowledge of excel but I can learn.