Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

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

    Thanks kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate addresses

    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.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thanks kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate addresses

    Hi Michael,

    I found it.

    Thanks Kerrie

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

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

    Thanks again

    Kerrie

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  8. #8
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate addresses

    Hi Michael,

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

    kerrie [img]/forums/images/smilies/smile.gif[/img]

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate addresses

    Hi Michael,

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

    Kerrie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •