Duplicate Find not finding all dupes
 

Hi
I am using Excel 2007 and have used previous versions to manage large
address data type spreadsheets.
In col B ins the street number and street name, in col C the suburb and
postcode.
I dedupe on col B&C as sometimes you get the same street name but different
suburbs hence no dupe.
The majority of my data is cut and paste from and email body.
I have just introduced another 100 records from the results of a website
query and they are in exactly the same format and font.
When I ran the dedupe it found none, but a manual check revealed 12 dupes
within the 1009 line spreadsheet.
Any ideas if I am missing some type of formatting that Excel is missing when
checking?
Spaces have been eliminated and the dupe text when compared to the original
entry is identical.
Examples of dupes include:
14/8 OTTELIA DRIVE
16 PINON CLOSE
27 TUSSOCK CRESCENT
2A/1501 GOLD COAST HWY

Many thanks
Stephen

Duplicate Find not finding all dupes
 

Coming from a website your data may have the html non-breaking spaces which
TRIM and CLEAN won't find.

Try editreplace

what: Alt + 0160 (typed on the numpad)

with: nothing.

replace all.

Or could have linefeed chars like Alt + Enter

Editreplace

CTRL + j

Nothing


Gord Dibben MS Excel MVP


On Sun, 14 Mar 2010 00:10:31 GMT, "Father John" wrote:

Hi
I am using Excel 2007 and have used previous versions to manage large
address data type spreadsheets.
In col B ins the street number and street name, in col C the suburb and
postcode.
I dedupe on col B&C as sometimes you get the same street name but different
suburbs hence no dupe.
The majority of my data is cut and paste from and email body.
I have just introduced another 100 records from the results of a website
query and they are in exactly the same format and font.
When I ran the dedupe it found none, but a manual check revealed 12 dupes
within the 1009 line spreadsheet.
Any ideas if I am missing some type of formatting that Excel is missing when
checking?
Spaces have been eliminated and the dupe text when compared to the original
entry is identical.
Examples of dupes include:
14/8 OTTELIA DRIVE
16 PINON CLOSE
27 TUSSOCK CRESCENT
2A/1501 GOLD COAST HWY

Many thanks
Stephen

Duplicate Find not finding all dupes
 

Hi Gord

The alt 160 found 10 occurances, the ctrl+j none
I ran the dedupe but still not finding the 12 noted dupes still in there
Thanks
Stephen


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Coming from a website your data may have the html non-breaking spaces
which
TRIM and CLEAN won't find.

Try editreplace

what: Alt + 0160 (typed on the numpad)

with: nothing.

replace all.

Or could have linefeed chars like Alt + Enter

Editreplace

CTRL + j

Nothing


Gord Dibben MS Excel MVP


On Sun, 14 Mar 2010 00:10:31 GMT, "Father John" wrote:

Hi
I am using Excel 2007 and have used previous versions to manage large
address data type spreadsheets.
In col B ins the street number and street name, in col C the suburb and
postcode.
I dedupe on col B&C as sometimes you get the same street name but
different
suburbs hence no dupe.
The majority of my data is cut and paste from and email body.
I have just introduced another 100 records from the results of a website
query and they are in exactly the same format and font.
When I ran the dedupe it found none, but a manual check revealed 12 dupes
within the 1009 line spreadsheet.
Any ideas if I am missing some type of formatting that Excel is missing
when
checking?
Spaces have been eliminated and the dupe text when compared to the
original
entry is identical.
Examples of dupes include:
14/8 OTTELIA DRIVE
16 PINON CLOSE
27 TUSSOCK CRESCENT
2A/1501 GOLD COAST HWY

Many thanks
Stephen

Duplicate Find not finding all dupes
 

Download Chip Pearson's CellView add-in.

Will show you what is in the cells.

http://www.cpearson.com/excel/CellView.aspx


Gord

On Sun, 14 Mar 2010 00:48:15 GMT, "Father John" wrote:

Hi Gord

The alt 160 found 10 occurances, the ctrl+j none
I ran the dedupe but still not finding the 12 noted dupes still in there
Thanks
Stephen


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Coming from a website your data may have the html non-breaking spaces
which
TRIM and CLEAN won't find.

Try editreplace

what: Alt + 0160 (typed on the numpad)

with: nothing.

replace all.

Or could have linefeed chars like Alt + Enter

Editreplace

CTRL + j

Nothing


Gord Dibben MS Excel MVP


On Sun, 14 Mar 2010 00:10:31 GMT, "Father John" wrote:

Hi
I am using Excel 2007 and have used previous versions to manage large
address data type spreadsheets.
In col B ins the street number and street name, in col C the suburb and
postcode.
I dedupe on col B&C as sometimes you get the same street name but
different
suburbs hence no dupe.
The majority of my data is cut and paste from and email body.
I have just introduced another 100 records from the results of a website
query and they are in exactly the same format and font.
When I ran the dedupe it found none, but a manual check revealed 12 dupes
within the 1009 line spreadsheet.
Any ideas if I am missing some type of formatting that Excel is missing
when
checking?
Spaces have been eliminated and the dupe text when compared to the
original
entry is identical.
Examples of dupes include:
14/8 OTTELIA DRIVE
16 PINON CLOSE
27 TUSSOCK CRESCENT
2A/1501 GOLD COAST HWY

Many thanks
Stephen