3 Replies Latest reply on Feb 14, 2014 5:56 PM by philmodjunk

    Find duplicate values and assign unique ID

    kentanderson

      Title

      Find duplicate values and assign unique ID

      Post

           I have thousands of records, that have a lot of the same data.  I would like to find all duplicate data in a field and assign it a unique id in another field.  Is this possible?

           Example 

           Client Field         New Unique Id Field

           apple                   1

           apple                   1

           apple                   1

           Banana               2

           Banana               2

           Orange                3

           To complicate things I need to assign a unique id also to those that are not duplicate like Orange only appears in one record

           Thank You in advance   If this is possible it will solve all my problems

        • 1. Re: Find duplicate values and assign unique ID
          philmodjunk

               This is not a unique ID in the table shown or you would not have three records with the ID of 1.

               I suggest that you need a related table that is free of duplicates. That can easily be produced with Import records from your existing data. You can define a field in this new table to auto-enter a serial number to generate your IDs (which will be unique in the new table.)

               You can then use two relationships and replace field contents to update your current table. To assign matching IDs to it. Relationship 1 matches by your client field so that replace field contents can copy over the ID from the new table into the original. relationship 2 matches by the newly assigned ID and is what you would use going forward.

          • 2. Re: Find duplicate values and assign unique ID
            kentanderson

                 I trust in you, and i am positive that the answer you provided will work, however I still lack the skills to make this happen.  Could you baby step me through this please.  Maybe a diagram showing the relationships.  I thought I hit a huge road block for sure with this one, but im knew you would have the answer.  

                 P.S. I have been trying to maybe write a macro in excel to do this for me, but im not having any luck with that.  I hope i can get this to work.

            • 3. Re: Find duplicate values and assign unique ID
              philmodjunk

                   I've posted a detailed process in your other thread.