Locate Duplicate Rows?

[pisto...@hotmail.com]

My coworker has a spreadsheet with 1000 rows and half a dozen text fields and
needs to know if she accidentally entered any duplicate rows. She want to
locate matching rows without scrolling through them individually. I know
this can be done but my Excel '97 Bible wasn't much help and I called the New
Horizons Tech Support (they give classes on MS Office stuff) and they said
this feature is not supported by Excel.

Surely this commonly used feature is available short of devising a macro?
Thanks for any info.

---
"It seems, in fact, as though the second half of a man's life is made up
of nothing but the habits he has accumulated during the first half."
-Fyodor Dostoyevsky

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[JPGi...@rce.ricardo.com]

Im still using Excel 5 so don't know if this works

Dim QryFind
Dim QryItem
Dim QryRange As Range 'range to search
Dim ItemRange As Range 'range of unique items
Dim DelRange As Range 'range of duplicate item to delete
Dim LastItem As Integer 'last unique Item
Dim ItemLoop As Integer
Dim ItemFound As Boolean

'AWorkbook & BWorkbook names of workbooks , replace with appropriate name
'ASheet & BSheet names of worksheets , replace with appropriate name

Sub RemoveDuplicates() LastItem = 0 Set QryRange = _
Range("[AWorkbook]ASheet!A1:A99") For Each QryFind In QryRange ItemFound =
False For ItemLoop = 1 To LastItem 'Check if item exists in column 'A' If
QryFind.Value = _ Range("[BWorkbook]BSheet!" &
Workbooks(BWorkbook).Worksheet(BSheet).Cells(ItemLoop, 1).Address).Value Then
ItemFound = True Exit For End If Next ItemLoop If ItemFound = False And
Trim(QryFind.Value) <> "" Then LastItem = LastItem + 1 'Add item to list
Range("[BWorkbook]BSheet!" &
Workbooks(BWorkbook).Worksheet(BSheet).Cells(ItemLoop, 1).Address).Value = _
QryFind.Value End If Next

'Remove duplicates Set ItemRange = Range("[BWorkbook]BSheet!" &
Workbooks(BWorkbook).Worksheet(BSheet).Cells(ItemLoop, 1).Address) & ":" & _
Range("[BWorkbook]BSheet!" &
Workbooks(BWorkbook).Worksheet(BSheet).Cells(LastItem, 1).Address) For Each
QryItem In ItemRange ItemFound = False For Each QryFind In QryRange If
QryItem.Value = QryFind.Value And ItemFound = False Then ItemFound = True
ElseIf QryItem.Value = QryFind.Value And ItemFound = True Then Set DelRange
= Range("[AWorkBook]ASheet!" &
Workbooks(AWorkBook).Worksheet(ASheet).Cells(ItemLoop, 1).Address) & ":" & _
Range("[AWorkBook]ASheet!" &
Workbooks(AWorkBook).Worksheet(ASheet).Cells(ItemLoop, 99).Address) 'columns
1 to 99 Range("A2").Select DelRange.Delete Shift:=xlUp End If Next Next
End Sub

Hope this helps

In article <79pb9h$233$1...@nnrp1.dejanews.com>,

[Ogilvy, Thomas, W., Mr., ODCSLOG]

Make sure your data has column headers such as

Last First Middle Position Address City

Select all your data
Go to another sheet, then go through the menu

Data=>Filter=>Advanced Filter

Select the copy to another location and designate a location on the
current sheet
Select unique records check box
Select the list range box and then switch to the data worksheet and
select all your data including the headers.

Click OK. Only unique records will appear in the new worksheet. If the
total number of records is the same, she did not enter duplicates. If
there are fewer than the original, you now have a list with duplicates
removed.

HTH,
Tom Ogilvy

[Robert]

How about using Data, Filter, Advanced Filter, Unique records only.
This will filter out rather than highlight any rows that are duplicated.

In article <79pb9h$233$1...@nnrp1.dejanews.com>, pisto...@hotmail.com

writes

>My coworker has a spreadsheet with 1000 rows and half a dozen text fields and
>needs to know if she accidentally entered any duplicate rows. She want to
>locate matching rows without scrolling through them individually. I know
>this can be done but my Excel '97 Bible wasn't much help and I called the New
>Horizons Tech Support (they give classes on MS Office stuff) and they said
>this feature is not supported by Excel.
>
>Surely this commonly used feature is available short of devising a macro?
>Thanks for any info.
>
>---
>"It seems, in fact, as though the second half of a man's life is made up
>of nothing but the habits he has accumulated during the first half."
>-Fyodor Dostoyevsky
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--
Robert

[peter russell]

With 1000 rows and 6 columns it's not worth spending much
time looking for ways other than visual inspection is it?

If the data has all been entered through the keyboard then
the first issue has to be accuracy of the input. Even IF a
row was entered twice then you would be subject to the normal
inaccuracies of typing and any slight difference between the
two entries would prevent it being identified as a duplicate.

Given this constraint, then my suggestion would be as
follows.

Create a seventh column (G1) which has the formula

=trim(a1)&trim(b1)&trim(c1)&trim(d1)&trim(e1)&trim(f1)
This will form a single field which concatenates the others
and removes any leading or following spaces from the input.
Copy this down all rows.

Sort the data on this column.

In cell H2 use the formula

=if(g1=g2, "Duplicate","")

Copy this down.

This will identify EXACT duplicates.

Peter Russell

In article <79pb9h$233$1...@nnrp1.dejanews.com>,

[Michael Zemljanukha]

Hello,

Such a stupid classes! It's a special topic of Excel knowledge. Just look at
http://home.gvi.net/~cpearson/excel.htm , Dublicates sections. Each XL user
should have a look at that place at once :))

------------
Michael Zemljanukha
Freelance Excel Consulting
mi...@nbd.kis.ru, mi...@yahoo.com
Yahoo! Pager ID 'mixaz'

pisto...@hotmail.com wrote in message
<79pb9h$233$1...@nnrp1.dejanews.com>...

>My coworker has a spreadsheet with 1000 rows and half a dozen text fields
and
>needs to know if she accidentally entered any duplicate rows. She want to
>locate matching rows without scrolling through them individually. I know
>this can be done but my Excel '97 Bible wasn't much help and I called the
New
>Horizons Tech Support (they give classes on MS Office stuff) and they said
>this feature is not supported by Excel.
>

>---
>"It seems, in fact, as though the second half of a man's life is made up
>of nothing but the habits he has accumulated during the first half."
>-Fyodor Dostoyevsky

Wow! He seems to by a russian too :))

[John Surducan]

pisto...@hotmail.com wrote:
> What I usually don in these cases is use Microsoft Access. You can import you spreadsheet into Access and then use the "Find Duplicates Query Wizard". The results of the query can then be exported back to a spreadsheet.

> My coworker has a spreadsheet with 1000 rows and half a dozen text fields and
> needs to know if she accidentally entered any duplicate rows. She want to
> locate matching rows without scrolling through them individually. I know
> this can be done but my Excel '97 Bible wasn't much help and I called the New
> Horizons Tech Support (they give classes on MS Office stuff) and they said
> this feature is not supported by Excel.
>

> Surely this commonly used feature is available short of devising a macro?
> Thanks for any info.
>

> ---
> "It seems, in fact, as though the second half of a man's life is made up
> of nothing but the habits he has accumulated during the first half."
> -Fyodor Dostoyevsky
>

[peter russell]

Michael
Read the question before abusing the respondents:))

Chip's site is very good but it doesn't answer THIS
question. (Well, I can't see where it does).

Regards

Peter Russell

In article <79rk9e$atr$1...@news.kis.ru>, "Michael Zemljanukha" (mi...@nbd.kis.ru) writes:
>Hello,
>
>Such a stupid classes! It's a special topic of Excel knowledge. Just look at
>http://home.gvi.net/~cpearson/excel.htm , Dublicates sections. Each XL user
>should have a look at that place at once :))
>
>------------
>Michael Zemljanukha
>Freelance Excel Consulting
>mi...@nbd.kis.ru, mi...@yahoo.com
>Yahoo! Pager ID 'mixaz'
>
>
>pisto...@hotmail.com wrote in message
><79pb9h$233$1...@nnrp1.dejanews.com>...

[DMcRitchie]

Hi Peter, (posted & emailed)

You might be able to accomplish this by making another column consisting of a
concatenation of all the other original cells on the row and then comparing on
that column using one of Chip's comparisons all of which, I believe, only
compare rows of a single column. Anyway it't the best I can think of. Make
sure you select your range or that the macro checks for the lastcell, you do
not want to run a comparison on 16000 or 64000 empty rows unless you test
hardware/software overnight.

=A25&B25&C25&D25&E25&F25&G25 ...
or perhaps a bit better =a25&":"&B25&":" ...
or perhaps =trim(a25)&":"&trim(B25)&":"&trim(b26)& ...
------------------
I'm sure I've seen this answered but I still have not found it, and the above
would probably suffice for most things. The solution I thought I saw probably
involved sorting columns first though Excel only makes sorting on 3 columns
easy. The comparison first compared on the single column as these others do
and then upon finding duplicate in the single column checked the two rows
across all of the columns. But I've not been able to locate it.

Duplicate rows mean different things to different people. :-)

Perhaps someone will remember it.
David McRitchie

[KRCowen]

The way I'd do this is as follows (I am just strarting to get comfortable using
VBA thanks in large part to contributions to this group, hence a non VBA
method)

create a column that is a concatenation of the 6 columns. Column A

create a column that numbers the rows in the original order (to facilitate
restoration of the original sequence) Column B

sort by column A

put a formula in C2 =if(A2=A1,0,1)

copy this down to the last row (don't worry about C1), put a 1 in C1.

convert colum C to values

sort by column C

all the duplicates have 0 in column C, and they come to the top

delete them and sort by column B

delete columns A, B, and C

Good luck
Ken