Duplicate addresses

Topic

New Reply

Hello,

I have a spreadsheet with fields such as firstname, surname, address, suburb, city, postcode, country and I am trying to find duplicate addresses. The rest of the fields maybe unique however some of the address have been entered the same as others.

Is there any way of checking? Hope you can help.

Thanks kindly,

Kerrie

Reply | Quote

Replies

If you make a Pivot Table with the addresses in the Rows and Count of addresses in the Data section you will get a count of how many times each address appears.

Reply | Quote

Hi Michael,

Thanks for your help. It is all starting to make sense.
Can you help with a little further. I am looking through
books but I can’t find the answer, How do I creat a “count” in the pivot table.
Do I do some calculation prior to the pivot table or once I
have created it.

Thanks kindly,

Kerrie

Reply | Quote

Kerrie
Make sure your columns all have headings otherwise the PT Wizard will not work. Select the whole of your data, go to Data Menu/Pivot Table Report. At Step 1 select Microsoft Excel list or database, then Next. At Step 2 your data should already be selected, Next. At Step 3 drag Address into the Row area, then drag Address again into the Data area, it should become Count of Address, if not double click on it and select Count, then Ok, Next. At Step 4 decide if you want the result on a new sheet or on an existing sheet (eg beside your data), click Finish.

Reply | Quote

Hi Michael,

Have you seen this error. When I (on another file that has 50,000 records) try and create a pivot table using the same fields(Address) and method, I get the following error “A field in your source data has more unique items than can be used in a pivot table. Excel might not be able to create the pivot table or may create a pivot table without this field.”
I then have to click on OK and it creates the table but I am missing data.

Do you know of any other way of checking for duplicate addresses.

Thanks again

Kerrie:)

Reply | Quote

Possibly a memory problem, look under Troubleshoot PivotTables, in XL Help. Try creating a PT with only the Addresses column as the database, this may use less resources.

Another, rather inelegant, solution is to sort your database by addresses, then use the formula

=IF(TRIM(D1)=TRIM(D2),1,0) in a blank column, Row 2 to the right of your database (assuming your addresses are in Column D), then fill down to the end of your database. You will now have a 1 against each duplicate address. The TRIM function takes out any excess spaces which might confuse XL.(It may be better to make a TRIMed copy of your addesses first and sort on this).

Reply | Quote

Hi Michael,

Thanks for all your advice. I am going to try it tonight.

kerrie

Reply | Quote

Hi Michael,

I tried the TRIM statement and it worked perfectly.
You are fab!

Kerrie:)

Reply | Quote

Hi Michael,

I found it.

Thanks Kerrie:)

Reply | Quote