Creating a unique id

[Beatrix]

Hi Everyone ,

I have 3 different data set to match. All comes from 3 different databases. I need to create a unique ID to be able to match the records and I'll combine a date and surname to do that. Full name is in one column and I need to split the surname into another column- from the right after the space.

Also I need to convert the date into a number.

Any suggestions?

Cheers
B.

[GarysStudent]

With the full name in A1 and a date in B1, use:


=MID(A1,FIND(" ",A1)+1,9999) & TEXT(YEAR(B1),"0000") &TEXT( MONTH(B1),"00") &TEXT( DAY(B1),"00")

[snb]

You might use:


Sub M_snb()
MsgBox CreateObject("scripting.filesystemobject").GetTempName
End Sub

[Beatrix]

Hi ,

Thanks very much for all replies.

@Gary - The formula is perfect but didn't work on my case because of the poor data quality- date format etc issues ..but it did work on sample data..

@snb - this is a great code to produce unique id but can we use it based on specific criteria? For Example, create a unique ID for each record on the worksheet where name, surname and dob were different??

Cheers
B.

[snb]

Did you test ?


Sub M_snb()
MsgBox replace(CreateObject("scripting.filesystemobject").GetTempName,"Rad","name" & "_" & "surname" & "_" & "dob")
End Sub

of course name, etc should be variables

or

Sub M_snb()
MsgBox name & "_" & surname & "_" & dob & CreateObject("scripting.filesystemobject").GetTempName
End Sub

[Beatrix]

snb ,

Would you mind to tell me how to use this script to create a unique ID in my case? I tested the script by using surname and dob but I don't know how it applies to 4 different data sets in separate worksheets in one workbook. I am trying to merge data sets from 4 different databases by creating a unique ID which is based on surname and dob.

Please see my sample spreadsheet attached.
Cheers
B.

[snb]

Sub M_snb()
For j = 0 To 3
sn = Sheets(Choose(j + 1, "p", "x", "y", "z")).Cells(1, 3).CurrentRegion.Offset(1)
For jj = 1 To UBound(sn) - 1
sn(jj, 1) = Replace(Replace(CreateObject("scripting.filesystemobject").GetTempName, "rad", sn(jj, 3) & "_" & sn(jj, 4) & "_"), ".tmp", "")
Next
Sheets("master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(sn), UBound(sn, 2)) = sn
Next
End Sub

[Beatrix]

Thanks very much for your quick response snb:bow:I really appreciate it but the thing is this script creates a unique ID for each row. If the surname and dob are the same then the unique id should be the same as they are same person.

I also need to put all data together from x y z p to master. There are 32 headings except the unique id and I coded each heading. Each data set has different fields except some demographics. If I add the Unique ID in each data set then I would run the data into master from each column in each worksheet. I don't know how to do that but I was going to go for a new thread for that if you could help me to finalise this bit?

Cheers
B.

[Krishna Kumar]

Hi

May be


Sub kTest()

Dim k, e, v, fso As Object, i As Long, u As String

Set fso = CreateObject("scripting.filesystemobject")

With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In Array("x", "y", "z", "p")
k = Worksheets(e).Range("a1").CurrentRegion.Resize(, 4).Value2
For i = 2 To UBound(k, 1)
v = .Item(k(i, 3) & "|" & k(i, 4))
If Not LenB(v) Then
u = Replace(Replace(fso.gettempname, "rad", vbNullString), ".tmp", vbNullString)
u = k(i, 3) & "_" & k(i, 4) & "_" & u
.Item(k(i, 3) & "|" & k(i, 4)) = u
End If
Next
Next
k = .items
End With
With Worksheets("master")
.Range("a" & .Rows.Count).End(3).Offset(1).Resize(UBound(k) + 1) = Application.Transpose(k)
End With

End Sub

[snb]

removing duplicates isn't that complicated in Excel is it ?

[Beatrix]

Krisss thank you so much it's exactly what I was looking for:thumbI appreciate for your time..Many many thanks again...............
Cheers
B.

@snb - I am not trying to remove duplicates. I didn't understand why you said it but yes it's not diificult to remove duplicates in Excel :) My point was when surname and dob are same the unique id should be the same but that script creates different id for the same person when it repeats in the list.

Cheers
B.

[Beatrix]

Hi Kris ,

This code is perfect. I was wondering is it possible to modify the script to be able to repeat the same unique code for the same person in master tab ? For example there are 27 records in tab x but 4 different people so I need 4 different id but each id should repeat in column A in master tab for 27 records.





Hi

May be


Sub kTest()

Dim k, e, v, fso As Object, i As Long, u As String

Set fso = CreateObject("scripting.filesystemobject")

With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In Array("x", "y", "z", "p")
k = Worksheets(e).Range("a1").CurrentRegion.Resize(, 4).Value2
For i = 2 To UBound(k, 1)
v = .Item(k(i, 3) & "|" & k(i, 4))
If Not LenB(v) Then
u = Replace(Replace(fso.gettempname, "rad", vbNullString), ".tmp", vbNullString)
u = k(i, 3) & "_" & k(i, 4) & "_" & u
.Item(k(i, 3) & "|" & k(i, 4)) = u
End If
Next
Next
k = .items
End With
With Worksheets("master")
.Range("a" & .Rows.Count).End(3).Offset(1).Resize(UBound(k) + 1) = Application.Transpose(k)
End With

End Sub

[Krishna Kumar]

Hi

You mean like this ?

Sub kTest()

Dim k, e, v, ka(), n As Long
Dim fso As Object, i As Long, u As String

Set fso = CreateObject("scripting.filesystemobject")

ReDim ka(1 To 10000, 1 To 1) '<<< replace 10000 with possible max records

With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In Array("x", "y", "z", "p")
k = Worksheets(e).Range("a1").CurrentRegion.Resize(, 4).Value2
For i = 2 To UBound(k, 1)
If Len(k(i, 3)) * Len(k(i, 4)) Then
If Not .exists(k(i, 3) & "|" & k(i, 4)) Then
n = n + 1
u = Replace(Replace(fso.gettempname, "rad", vbNullString), ".tmp", vbNullString)
u = k(i, 3) & "_" & k(i, 4) & "_" & u
.Item(k(i, 3) & "|" & k(i, 4)) = u
ka(n, 1) = u
Else
n = n + 1
ka(n, 1) = .Item(k(i, 3) & "|" & k(i, 4))
End If
End If
Next
Next
End With
If n Then
With Worksheets("master")
.Range("a" & .Rows.Count).End(3).Offset(1).Resize(n) = ka
End With
End If

End Sub

[Beatrix]

Hi ,

Yes exactly like this:thumbThanks a million. VBA is an amazing programming language!

Cheers
B.