PDA

View Full Version : Solved: Unique record ID generator: please help!



jcfields
09-05-2012, 07:14 AM
Hello all,

I'm a very newbish VBAer, and I'm having trouble coming up with a user-defined function that will do what I need. I've written some code (that I will paste below) that will likely highlight my lack of understanding of what I'm doing, but let me explain the setup.

I have a spreadsheet that is going to serve as a log, so that each row corresponds to a unique log entry, and each row is required to have a unique record ID number. Three of the columns of my sheet correspond to the date (mm/dd/yy format), the plant/site (5 possible choices here; let's call them "A, B, C, D, & E"), and the specific building (4 choices here; let's call them "U1, U2, CO, and LI). The format of the unique record ID needs to be like these examples:


12-A-U1-001

12-C-CO-012

13-B-U2-128

Or more generically, YY-site-building-sequential_number

The hard part about this, I'm discovering, is the sequential number on the end. The first record for a given site and building in a given year should be "001", followed by "002", "003", etc. So it starts over at "001" every year. Also, each unique site-building combination needs to have it's own sequentially advancing number. So in other words, I need to be able to have, for example, "12-A-U1-001" and "12-A-U2-001" both be able to exist simultaneously.

I hope this makes sense. If not, I will be happy to clarify, and I appreciate any help anyone can give me.

The code I'm pasting below (which isn't working) was intended to just handle the number part of my ID key, and then I would string the other pieces onto to it in the spreadsheet. But having it all within the confines of the macro would be much cleaner. Anyway, thanks in advance for any help.


Option Base 1
Public Function assignTAPnum(siteName, unitName, dateYear) As Variant

Dim DateArray() As Variant
Dim PlantArray() As Variant
Dim UnitArray() As Variant
Dim TAPArray() As Integer
Dim i As Integer
Dim j As Integer
Dim LastRow As Integer
Dim NCells As Integer
Dim biggestTAPnum As Integer

'calculates the last used row based on the date of the last entry in the spreadsheet
'which should correspond to the new entry that is currently being entered
LastRow = Range("B2").End(xlDown).Row
NCells = LastRow - 1

ReDim DateArray(NCells)
ReDim PlantArray(NCells)
ReDim UnitArray(NCells)
ReDim TAPArray(NCells)

For i = 2 To LastRow
DateArray(i - 1) = Range("B" & i)
PlantArray(i - 1) = Range("D" & i)
UnitArray(i - 1) = Range("E" & i)
TAPArray(i - 1) = Range("F" & i)
Next i

biggestTAPnum = 0

For j = 1 To NCells
If DateArray(j) = Year(dateYear) And PlantArray(j) = siteName And UnitArray(j) = unitName And TAPArray(j) >= biggestTAPnum Then
biggestTAPnum = TAPArray(j)
End If
Next j
MsgBox biggestTAPnum
assignTAPnum = biggestTAPnum + 1

End Function

Bob Phillips
09-05-2012, 07:51 AM
Public Function assignTAPnum(dateYear, siteName, unitName) As Variant
Const FORMULA_TAP As String = _
"TEXT(SUMPRODUCT(--(YEAR(<rngDate>)=YEAR(<date>)),--(<rngSite>=<site>),--(<rngUnit>=<unit>)),""000"")"
Dim thisRow As Long
Dim thisId As Long

thisRow = Application.Caller.Row
With Application.Caller

thisId = .Parent.Evaluate(Replace(Replace(Replace(Replace(Replace(Replace(FORMULA_TA P, _
"<unit>", unitName.Address), _
"<rngUnit>", unitName.Offset(-thisRow + 2).Resize(thisRow - 1).Address), _
"<site>", siteName.Address), _
"<rngSite>", siteName.Offset(-thisRow + 2).Resize(thisRow - 1).Address), _
"<date>", dateYear.Address), _
"<rngDate>", dateYear.Offset(-thisRow + 2).Resize(thisRow - 1).Address))
assignTAPnum = Format(dateYear.Value, "yy-") & siteName & "-" & unitName & Format(thisId, "-000")
End With
End Function

call like so

=assignTAPnum(B2,D2,E2)

jcfields
09-05-2012, 08:21 AM
xld,

First, thanks very much for the help.

When I paste the code into the module and call the function as you indicated, the cell returns "#NAME?". This is what my function was doing too. Any idea what's possibly causing this?

I don't know if it matters or not, but the values in columns D and E are being selected from a drop-down list (via data validation).

Bob Phillips
09-05-2012, 09:57 AM
That suggests that you have pasted it into the wrong place, it should go in a standard code module.

jcfields
09-05-2012, 10:19 AM
That suggests that you have pasted it into the wrong place, it should go in a standard code module.

I have it in the default Module 1. Is this correct?

jcfields
09-05-2012, 11:33 AM
Ok, I figured out he #NAME? problem; the macro security settings were preventing the macro from running.

So the function works now...sort of. For some reason, some of the combinations (for year-site-building) don't start off with "-001", even though there are no other records existing with this name name ID format. I can send you a stripped down version of the spreadsheet if that would make this easier to troubleshoot, if you don't mind helping me a little more. I really do appreciate it.

I'd also love to understand how this code works (and why mine didn't).

Thanks again xld!

Bob Phillips
09-05-2012, 12:07 PM
Yes please post a workbook in this thread that shows the problem.

jcfields
09-05-2012, 12:27 PM
Ok, a dumbed down version is attached. The last entry is the first place that I use the UDF. I should've mentioned this, but all of the record IDs in that column don't follow the new format, but I have to leave them there; the new format will be used from now on. These existing values don't seem to be affecting the UDF though. Putting it (the UDF) in its own new column vs. putting it at the bottom of this existing column didn't seem to make any difference on its output.

jcfields
09-05-2012, 12:41 PM
Ok, a dumbed down version is attached. The last entry is the first place that I use the UDF. I should've mentioned this, but all of the record IDs in that column don't follow the new format, but I have to leave them there; the new format will be used from now on. These existing values don't seem to be affecting the UDF though. Putting it (the UDF) in its own new column vs. putting it at the bottom of this existing column didn't seem to make any difference on its output.

Edit: I should've said "stripped down", not "dumbed down". Dumbed down would be taking all that code that I can't currently comprehend out of it :bug:

And thanks again for your help.

jcfields
09-05-2012, 01:28 PM
xld,

I think I see what the problem is; I just don't know how to fix it. It looks like your code counts the number of instances (above the new entry) where the date, site, and unit are equal, and then it just advances the counter by 1.

However, it needs to discard the instances where the new naming format isn't being used and start the counter at 001 (even though there are already records, using the old naming systems, that match the year-site-unit). Maybe one more exclusion criteria is necessary...

Thanks again.

Teeroy
09-05-2012, 07:40 PM
If you don't mind a hidden column you could use a conditional incrementer to generate the suffix.

Pseudo : =IF(year this row = year previous row, previous row value + 1, 001)

Bob Phillips
09-06-2012, 12:28 AM
What we can do is add another argument to the UDF telling it where to begin its 'look back' from. We can make it optional with a default of 2.

Public Function assignTAPnum( _
ByVal dateYear As Range, _
ByVal siteName As Range, _
ByVal unitName As Range, _
Optional ByVal startAt As Long = 2) As Variant
Const FORMULA_TAP As String = _
"TEXT(SUMPRODUCT(--(YEAR(<rngDate>)=YEAR(<date>)),--(<rngSite>=<site>),--(<rngUnit>=<unit>)),""000"")"
Dim thisRow As Long
Dim thisId As Long

thisRow = Application.Caller.Row
With Application.Caller

thisId = .Parent.Evaluate(Replace(Replace(Replace(Replace(Replace(Replace(FORMULA_TA P, _
"<unit>", unitName.Address), _
"<rngUnit>", unitName.Offset(-thisRow + startAt).Resize(thisRow - 1).Address), _
"<site>", siteName.Address), _
"<rngSite>", siteName.Offset(-thisRow + startAt).Resize(thisRow - 1).Address), _
"<date>", dateYear.Address), _
"<rngDate>", dateYear.Offset(-thisRow + startAt).Resize(thisRow - 1).Address))
assignTAPnum = Format(dateYear.Value, "yy-") & siteName & "-" & unitName & Format(thisId, "-000")
End With
End Function

And use like so

=assignTAPnum(A103,C103,D103,103) - or 100, or 99, or 50, or ...

snb
09-06-2012, 01:31 AM
In cell A104: 12-03-2012
In cell C104: SA
In cell D104: Unit 5
In cell E104

=snb(A104;C104;D104)


Function snb(c01, c02, c03)
snb = Evaluate("max(if(year(A2:A200)=" & Year(c01) & ",if(c2:C200=""" & c02 & """,if(D2:D200=""" & c03 & """,E2:E200,0),0),0))") + 1
End Function

jcfields
09-06-2012, 06:09 AM
What we can do is add another argument to the UDF telling it where to begin its 'look back' from. We can make it optional with a default of 2.

Public Function assignTAPnum( _
ByVal dateYear As Range, _
ByVal siteName As Range, _
ByVal unitName As Range, _
Optional ByVal startAt As Long = 2) As Variant
Const FORMULA_TAP As String = _
"TEXT(SUMPRODUCT(--(YEAR(<rngDate>)=YEAR(<date>)),--(<rngSite>=<site>),--(<rngUnit>=<unit>)),""000"")"
Dim thisRow As Long
Dim thisId As Long

thisRow = Application.Caller.Row
With Application.Caller

thisId = .Parent.Evaluate(Replace(Replace(Replace(Replace(Replace(Replace(FORMULA_TA P, _
"<unit>", unitName.Address), _
"<rngUnit>", unitName.Offset(-thisRow + startAt).Resize(thisRow - 1).Address), _
"<site>", siteName.Address), _
"<rngSite>", siteName.Offset(-thisRow + startAt).Resize(thisRow - 1).Address), _
"<date>", dateYear.Address), _
"<rngDate>", dateYear.Offset(-thisRow + startAt).Resize(thisRow - 1).Address))
assignTAPnum = Format(dateYear.Value, "yy-") & siteName & "-" & unitName & Format(thisId, "-000")
End With
End Function

And use like so

=assignTAPnum(A103,C103,D103,103) - or 100, or 99, or 50, or ...

This works great. Thanks again for your help. Now I just need to go study the code and figure out how the heck it works.

jcfields
09-06-2012, 06:11 AM
Thanks to everyone for your help. I really appreciate it.

jcfields
09-06-2012, 09:05 AM
:banghead: I've discovered one more wrinkle that has me wracking my brain with no solution so far. I played with the different macros, and after some consideration, I think the original macro that xld gave me actually works best (e.g. I don't mind the ID number incrementing for the instances where the old format was used). But I've discovered that when I sort the columns by date, with newest entry on top, the ID number changes. I need it to be static once it is created so that even if the data gets sorted or a row gets inserted above it or something, it always retains the original number that was assigned.

I'm even more newbish with Access than I am with Excel, but the functionality I'm trying to create here already exists in Access (sort of), if I'm not mistaken. I wonder if I should be doing this project there instead...

Bob Phillips
09-13-2012, 01:43 AM
Just copy>PasteValues when you are happy with it. As a UDF it will always reflect the current situation.

jcfields
09-13-2012, 12:55 PM
Since I opened another thread related to this problem, I'm going to link to that thread (http://www.vbaexpress.com/forum/showthread.php?p=276453#post276453) to show my final solution for anyone that's interested.

Thanks again for all the help I received here.

Now I just need to figure out how to mark the subject line as "Solved"...