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.
Sub Learning VBA()
Do
Practice Most Useful VBA Examples
Loop Until Become an Expert in VBA
End Sub
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")
Have a Great Day!
You might use:
Sub M_snb() MsgBox CreateObject("scripting.filesystemobject").GetTempName End Sub
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.
Sub Learning VBA()
Do
Practice Most Useful VBA Examples
Loop Until Become an Expert in VBA
End Sub
Did you test ?
of course name, etc should be variablesSub M_snb() MsgBox replace(CreateObject("scripting.filesystemobject").GetTempName,"Rad","name" & "_" & "surname" & "_" & "dob") End Sub
or
Sub M_snb() MsgBox name & "_" & surname & "_" & dob & CreateObject("scripting.filesystemobject").GetTempName End Sub
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.
Sub Learning VBA()
Do
Practice Most Useful VBA Examples
Loop Until Become an Expert in VBA
End Sub
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
Thanks very much for your quick response snbI 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.
Sub Learning VBA()
Do
Practice Most Useful VBA Examples
Loop Until Become an Expert in VBA
End Sub
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
________________
Kris
removing duplicates isn't that complicated in Excel is it ?
Krisss thank you so much it's exactly what I was looking forI 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 ExcelMy 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.
Sub Learning VBA()
Do
Practice Most Useful VBA Examples
Loop Until Become an Expert in VBA
End Sub
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.
Originally Posted by Krishna Kumar
Sub Learning VBA()
Do
Practice Most Useful VBA Examples
Loop Until Become an Expert in VBA
End Sub
Hi
You mean like this ?
[vb]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[/vb]
________________
Kris
Hi ,
Yes exactly like this
Cheers
B.
Sub Learning VBA()
Do
Practice Most Useful VBA Examples
Loop Until Become an Expert in VBA
End Sub
Posting Permissions
Reply With Quote
