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

[GaryWMn]

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?

 

[Legacy 98055]

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

 

[hiker95]

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

 

[jacinto]

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.

 

[AkaTrouble]

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

=A2&ROW()&COLUMN()