Duplicate addresses

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.

Re: Duplicate addresses

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.

Re: Duplicate addresses

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.

Re: Duplicate addresses

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.

Re: Duplicate addresses

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).