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

GaryWMn

Board Regular
Joined
Sep 22, 2009
Messages
97
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Just add a field. Use a number. I don't understand the limitation of existing fields only when you can just add one.
 
Upvote 0
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.
 
Upvote 0
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()
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top