Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How do you create a unique key based on existing data?

  1. #1
    Board Regular GaryWMn's Avatar
    Join Date
    Sep 2009
    Location
    Plymouth, MN
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How do you create a unique key based on existing data?

    I just worked with a data set where I needed to create a unique key based on existing information where no unique key was set up as a field. I accomplished it by concatenating (using the & [concatenation] operator) several fields based on what I hypothesized and observed about their content.

    I'm thinking there has to be a more automated way to discover the best unique key of existing fields and their data. I thought I'd post this as a challenge because I don't know the "right" (or better) answer.

    Anyone have any ideas?
    Gary Whiteford
    http://www.linkedin.com/in/garywhiteford

    Fructus diem! (Have a great day!)

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you create a unique key based on existing data?

    Just add a field. Use a number. I don't understand the limitation of existing fields only when you can just add one.

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,588
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How do you create a unique key based on existing data?

    GaryWMn,

    Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
    http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  4. #4
    New Member
    Join Date
    Nov 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you create a unique key based on existing data?

    Hi,

    I know this is an old thread, however I believe I have an answer for this of sorts, however my current method for actioning this is somewhat slow (I will make another post about that anyway).

    The easy way is as identified above pick a number and simply drag it down. The issue is if you want to use this data as a source to update/upload to a database, perhaps this isn't the best method.

    So your data doesn't have its own unique key but does have some numbers like a customer number but may have more entries which cant be removed.

    The answer is this:

    As above mentioned concatenate the data as shown below:

    Row 2
    60000 Company 1 Detailsabcdefg 60000Company1Detailsabcdefg

    Row 3
    60000 Company 1 Detailsabcdefg 60000Company1Detailsabcdefg

    Now if you used a concatenation of these 3 columns fields it would give you the same result, meaning it could not be added to a database as a primary key using the concatenated field as a basis. What you can do is use a formula to check the rows for duplicates of the primary key and assign another section to the end of the string.

    By adding a 5 th column using the formula if(row(2:2)=match(D2,D:D,0),D2,D2&"v1") you end up with something like below:

    Row 2
    60000 Company 1 Detailsabcdefg 60000Company1Detailsabcdefg 60000Company1Detailsabcdefg

    Row 3
    60000 Company 1 Detailsabcdefg 60000Company1Detailsabcdefg 60000Company1Detailsabcdefgv1

    This is useful if you are likely to only end up with a few duplicates as you can then add more columns looking at the next key looking for duplicates etc. What this does mean is that you end up with a unique primary key. If your likely to have many duplicates then this method wont really be feasible.

  5. #5
    Board Regular
    Join Date
    Dec 2014
    Location
    little ole England
    Posts
    1,542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How do you create a unique key based on existing data?

    just my idea based on all of the above but allowing for reference to original source spread sheet

    i would select the field(s) with what appears to be relevant key then add row and column to it this will always be a unique identifier

    Code:
    =A2&ROW()&COLUMN()
    ◄ŦяμвŁ►
    By Name and By Nature

    i know nothing but i know everything about it
    please take everything i type with a pinch of salt i am only learning this stuff as i go along. Please make a post helping someone else
    Want to post a small screen shot? Try MrExcel HTML Maker
    How To Install MrExcel HTML Maker: https://www.youtube.com/watch?v=Jycv...ature=youtu.be

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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