Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Finding non duplicates (97/2000/XP)

    Hi All
    I have had a look through the search files but can't quite find what I am looking for!

    A colleague has an spreadsheet that contains 8000+ which has about 60-100 fields <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> . The worksheet has a property reference (of a building) and then a series of 1's or 0's after it stating if they have a certain service provided (i.e. Satellite TV, Gas, Water etc). This was originally a download but has been adapted and changed by users.
    Unfortunately, some of the data has been entered in wrong and my colleague wants to find out which ones have been entered in wrong. It is best described in the table below:


    <table border=1><td>Property A</td><td1[/td><td>1</td><td>1</td><td>1</td><td>Property A</td><td>1</td><td>1</td><td>1</td><td>Property A</td><td>1</td><td>1</td><td>1</td><td>Property A</td><td>0</td><td>1</td><td>1</td>
    </table>

    Now, we can see that the first 3 records are the same and but the fourth one is different. I would like to find a method that can check out the sheet and view variations between sub sets of data( Remember I have a lot of properties that are grouped)

    I have been trying to do it through Access, using duplicate queries but it is very cumbersome doing the SQL. Is there an Excel method?

    Jerry
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding non duplicates (97/2000/XP)

    If you want to weed out the duplicates, and just keep a single instance of each unique combination, you can use Data | Filter | Advanced Filter..., with the "Copy to another location" and "Unique records only" options. In your example, the result would be

    <table border=1><td>Property A</td><td1[/td><td>1</td><td>1</td><td>1</td><td>Property A</td><td>0</td><td>1</td><td>1</td></table>
    Is that what you want?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding non duplicates (97/2000/XP)

    Hi Hans

    Yep, that's a good method. The problem is that with the amount of data my colleague has, and in her own words " It's a bit manual still, Jerry!!!!!" is there another option? which just shows the non duplicates as a results list which would reduce the data extract? I am trying not to go down the VBA road if possible as this is a bi-annual event that the colleague has to have to do and not a repetitive exercise.

    Jerry
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding non duplicates (97/2000/XP)

    Well, if you select "Filter the list, in place" and leave "Unique records only" ticked, you will see the unique records in the list itself. Other than that, what would you like?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding non duplicates (97/2000/XP)

    Hans

    Thanks for your time. Personally, I think this option is perfectly suitable for the purpose and does highlight the anomalies in the data very well. We have had a rather heated discussion over this and frankly I was not going to spend 4 hours writing some code for a job that would take 2 hours to do manually. It was a User versus Techy discussion where she wanted Champagne for Beer prices and I was all out of beer.

    Cheers

    Jerry
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding non duplicates (97/2000/XP)

    OK, have a beer on me <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding non duplicates (97/2000/XP)

    Another option:
    After you create the "subset" of "unique items",
    copy the list to a new place and then use the AUTOFILTER (data - filter - autofilter). you could filter on a a particular property and only view the records with that "property" then you could look at the filter list for each of the other columns to check for how many they have and what they are. If I understand correctly, if should only be 1 item, it not you can select the "bad ones" and then edit them.

    I have found the autofilter a good way to look at the items entered into a list that could have "typos" or just slightly different entries to "quickly" get rid of these "extra ones"

    It can take some time, but for a "one-time fixing" that is non-repetitive, I think the manual way works better so you can ensure that you fix it right. There are too many things that can go wrong when you run code like this unless it is debugged completely - and who wants to spend the time writing and debugging for a task that will be done once.

    Steve

Posting Permissions

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