duplicate names to extract

[Sherry Kaufman]

I have over 1400 names with addresses in a spreadsheet with man
duplicates. I'm trying to extract just those duplicates into
separate list

 

[Paul Falla]

Dear Sherry

You don't clarify which data is duplicated, so I will
assume for argument sake that it is the name column.

One way to resolve your issue is insert a new column at
column 1,copy and paste the range containing the names
into the new column, and then sort that column in
ascending order.

Create a new macro using the following code

Sub DuplicateFinder()
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1,
ColNum).Value Then
Cells(RowNdx, ColNum).Value = "---------------"
End If
Next RowNdx
End Sub

When run, the macro will replace the duplicate names
with "---------". You can then filter to show just the
dashes, and then copy and paste the filtered data into a
new sheet, then remove the column with the dashes in.

Hope this helps

Paul

 

[Max]

Assume the data are in separate* columns,
e.g. Names in col A, Address in col B, City in col C etc
with data from row2 down

Try the following steps, which uses Advanced Filter
in a *back-up* copy of your workbook (not the original)

Select the Name column
Click Data > Filter > Advanced Filter
(Click OK to the Excel prompt to use the first row as labels)

In the Advanced Filter dialog:
Check Filter the list, in-place
Check Unique values only
Click OK

Select all the "blue colored" filtered rows
(select all the "blue" row headers)

These are the "unique" rows which you do not want

Right-click > Delete rows

Click Data > Filter > Show All

This will reveal the rows with the duplicate Names

-----------------------------

*If the name & address data are not in separate columns
but in uniform groups of say, 5 separate rows each,
try: http://tinyurl.com/wpj0
for a way to shift these to separate columns