Creating an 11 digit number

[Aussiebear]

I am currently undertaking a task to identify all physical structures within the Great Barrier Reef Marine Park. The listing from the body charged with the responsibility to govern the GBRMPA has kindly provided a list of their recognised structures however it has become so what compromised when converting into Excel. In the attached file you will see that Column A contains the Structure ID, Column B contains the type of Structure. The 11 digit number they use and the one I need to replicate if possible is derived from the following set of rules;

As supplied by the Manager of the Geospacial section of the GBRMPA

"The UNIQUE_ID number is an automatically generated attribute, only used internally to allow the Oracle database to identify each feature. The number is a combination of the GBR_ID, SUB_NO and FEATURE_C attributes.

The GBR_ID is the feature identification number - e.g. Hamilton Island is 20-057.

The SUB_NO is a three-digit number that identifies multiple features of the same type in a group, e.g. multiple reefs surrounding an island are labelled a, b, c, d etc. - these will be numbered 101, 102, 103, 104 etc. If it refers to a single feature it will be given the number 100. So for example Marquis Island which has an ID of 22-063b will have a corresponding sub number of 102.

The FEATURE_C is an automatically generated three-digit number representing the type of feature eg mainland, island, cay etc. (100, 102, 103)

• Mainland features = 100
• Islands = 102
• Cays = 103
• Reefs = 104
• Rocks = 106 and 108"."

The first five numbers of the 11 digit number is the GBR-id (Column A minus the hyphen and or an alphabetical letter if more than one), then the Sub_No number, then the Feature-C number (Column B). If I use the example of Hamilton Island 20-057 then the unique number should be 20057101102. To add to the complexity, ( and for reasons I don't understand), if you search for 20-041, you will find a nightmare...

20-041 U/N Rock
20-041 Fitzaland Island Reef
20-041 Whitsunday Island
20-041 Whitsunday Island Reef (N0 1) and so on.

20-041 U/N Rock should be 20041101106. Reasoning: 20-041 without the hyphen, 101 for the first of it type, and 106 because its a rock,
20-041 Fitzaland Island Reef should be 20041101104. Reasoning: 20-041 without the hyphen, 101 for the first of its type, and 104 because its a reef,
20-041a Whitsunday Island should be 20041101102. Reasoning: 20-041 without the hyphen and the "a", 101 for the first of its type, and 102 because its an island,
20-041a Whitsunday Island Reef (No 1) should be 20041102104. Reasoning: 20-041 without the hyphen and the "a", 102 because its the second of its type, and 104 because its a reef,
20-041b Whitsunday Island Reef (N0 2) should be 20041103104. Reasoning: 20-041 without the hyphen and the "b", 103 because its the third of its type, and 104 because its a reef,
20-041b Fitzaland Island should be 20041102102. Reasoning: 20041 without the hyphen and the "b", 102 beause its the second of it type, and 102 because its an island.

Can anyone assist me in developing a method the create this unique 11 digit number so I can use it to implement a database where the Structures are clearly identified despite carrying an "complicated" GBR_No?

[Bob Phillips]

Does this formula do it Ted?

=LEFT(SUBSTITUTE(A1,"-",""),5)&100+COUNTIF($A$1:$A1,$A1)&INDEX({101,102,103,104,106},MATCH(C1,{"M ainland feature","Island","Cay","Reef","Rock"},0))

[snb]

In VBA:

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion.Resize(, 6)
    
  For j = 1 To UBound(sn)
    y = 0
    If Len(sn(j, 1)) > 6 Then y = Asc(Mid(sn(j, 1), 7, 1)) - 96
    sn(j, 6) = Replace(Left(sn(j, 1), 6), "-", "") & 100 + y & 100 + InStr(" maiscarero", Left(LCase(sn(j, 3)), 2)) \ 2
  Next
    
  Sheet1.Cells(1).CurrentRegion.Resize(, 6) = sn
End Sub

[Aussiebear]

Sorry Bob, but it doesn't increase the count for the set of characters 6,7 and 8. If the initial number remains the same (use 20-003 for example) then every time the structure type repeats itself then it need to increase by 1. Looking at the attached workbook rows 5 to 12 for example, from row 6 onwards its should be showing 102, 103, 104 etc.

[Aussiebear]

@ snb, Can we do this as a formula first please?

[Paul_Hossler]

I'd do a user defined function because I like user defined functions

but the logic could be incorporated into a Sub just as easily

SepChar is optional - I just put it in so I could see the pieces

Option Explicit


'The UNIQUE_ID number is an automatically generated attribute, only used internally to allow the Oracle database to identify each feature.
'   The number is a combination of the GBR_ID, SUB_NO and FEATURE_C attributes.
'The GBR_ID is the feature identification number - e.g. Hamilton Island is 20-057.
'The SUB_NO is a three-digit number that identifies multiple features of the same type in a group,
'   e.g. multiple reefs surrounding an island are labelled a, b, c, d etc. - these will be numbered 101, 102, 103, 104 etc.
'   If it refers to a single feature it will be given the number 100. So for example Marquis Island which has an ID of 22-063b will have a corresponding sub number of 102.
'The FEATURE_C is an automatically generated three-digit number representing the type of feature eg mainland, island, cay etc. (100, 102, 103)
'   Mainland features = 100
'   Islands = 102
'   Cays = 103
'   Reefs = 104
'   Rocks = 106 and 108"


Function AussieBear(GBR_ID As String, FEATURE As String, Optional SepChar As String = vbNullString) As String
    Dim sGBR As String, sSUB As String, sFEATURE As String
    
    sGBR = Left(GBR_ID, 2) & Mid(GBR_ID, 4, 3)
    
    If Len(GBR_ID) = 6 Then
        sSUB = "100"
    Else
        sSUB = Asc(UCase(Right(GBR_ID, 1))) + 36
    End If
    
    Select Case FEATURE
        Case "Mainland"
            sFEATURE = "100"
        Case "Island"
            sFEATURE = "102"
        Case "Cay"
            sFEATURE = "103"
        Case "Reef"
            sFEATURE = "104"
        Case "Rock"
            sFEATURE = "106"        '???? and 108"
    End Select


    AussieBear = sGBR & SepChar & sSUB & SepChar & sFEATURE
End Function

[snb]

@ snb, Can we do this as a formula first please?

Can you elaborate why ?
I thought: if the result matches your expectation why doing it otherwise ?
It has to be done only once, so it's no use recalculating all those formulae over and over again.

A. In the form of a UDF:

Function F_snb(c00, c01)
   If Len(c00) > 6 Then y = Asc(Mid(c00, 7, 1)) - 96
   F_snb = Replace(Left(c00, 6), "-", "") & 100 + y & 100 + InStr(" maiscarero", Left(LCase(c01), 2)) \ 2
End Function

In F1:

=F_snb(A1,C1) 

B. A 'normal' formula:

=LEFT(SUBSTITUTE(A1;"-";"");5)&IFERROR(100+CODE(MID(A1;7;1))-96;100)&100+INT(FIND(LEFT(LOWER(C1);2);" maiscarero")/2) 

[Aussiebear]

@snb. Thank you for your formula, and once I converted the semi colons into commas, and then changed the values "100" into "101" , it now appears to work as required I believe. I have as yet to find anything that tells me what the word "maiscareo" is supposed to mean. As to the reasoning for a formula, once calculated I will then convert the formula results into a value only.

@Paul. Thank you for your function. I haven't yet had to try it but should I find issues with snb's formula then I will give it a go

[Aussiebear]

On further review I should have said "changed the first value 100 to 101". At this point there is only one error that I have noticed and that's because someone within the GBRMPA has named Callum Shoal as 20-1271 ( for reasons unknown). Errors like this I can overcome without it becoming time consuming.

[Paul_Hossler]

Be advised that there are some rows with an entry in Col L

Looks like a bad parsing

Capture.JPG

[snb]

I have as yet to find anything that tells me what the word "maiscarero" is supposed to mean.

Well, to be honest ( ) it's a 'Dutch' word, meaning "why don't you compose a character combination of the friist 2 lowercase characters of each of the english words mainland, island, cay reef, rock ? to check the existence of a word an at the same time giving it an ordinal number insteda of applying a match in an array, or an overcomplicated select case construction in VBA"
As you might see the dutch language is very flexible in creating simple words that describe a complex programming process.
I hope the Great Barrier Reef may prosper using them.

As to the reasoning for a formula, once calculated I will then convert the formula results into a value only.

I understand these 2 steps, but that is exactly what the VBA macro is doing in 1 step.

[Paul_Hossler]

@Paul. Thank you for your function. I haven't yet had to try it but should I find issues with snb's formula then I will give it a go

1. "maiscareo" had me scratching my head for awhile also

2. That's a nifty function. Too complicated for my style, but still pretty nifty

3. If it were me, I'd just do a simple Sub() to format the 11 characters and put the .Value into the WS (using my function of course ). Need to re-run if things change or get added, but that's what computers are for

[Aussiebear]

Actually this is a small step in a much larger project. Currently no one has ever produced a listing of all the structures (5600 defined to date) within the GBRMPA (Covers more than 344,400 Square kilometers) which details their individual physical locations , Distances to nearest port, type of Marine Park (7 types) that the structures lies within, and any Navigational Aids that lie thereon. This task was undertaken in early March as private research and whilst progress has been slowed by covid, poor definition of data, and my lack of knowledge, its starting to come together nicely at the moment.

Older Apple Mac's dont seem to like to run long streams of mathematical calculations, whereas until recently when I purchased a Microsoft Laptop to complete these tasks, the accuracy was not always with 100% confidence. Then trying to find methods to make the two different file systems communicate has also been trying.... Never the less I am still marching on with the task.

[Paul_Hossler]

I'm retired and I like to do (and have the time to do) fun little projects

With approx 2700 lines in your sample, the sub runs as fast as you release the Enter key

My gut feel is that worksheet formulas would be slower, plus you'd have the maintenance issue of making sure that all the cells had the formula, etc.

Of course, if you needed to squeeze every CPU cycle, you could load it into a memory array, crunch it, and put it back

But the complexity and inability to manipulate the worksheet cells could be a factor

This little piece of code (or snb's of course) could be incorporated into your larger project easier than lots of formulas

Just MHO ...

Capture.JPG

Option Explicit


Const colGBR As Long = 1
Const colFeature As Long = 3
Const colUnique As Long = 14




Sub AussieBear()
    Dim rData As Range, rDataRow As Range
    Dim sUnique As String, sGBR As String, sFeature As String
    
    For Each rDataRow In ActiveSheet.Cells(1, 1).CurrentRegion.Rows
        With rDataRow.EntireRow
            sGBR = .Cells(colGBR).Value
            sFeature = .Cells(colFeature).Value
            
            sUnique = Left(sGBR, 2) & Mid(sGBR, 4, 3)
    
        If Len(sGBR) = 6 Then
            sUnique = sUnique & "100"
        Else
            sUnique = sUnique & Asc(UCase(Right(sGBR, 1))) + 36
        End If
    
        Select Case sFeature
            Case "Mainland"
                .Cells(colUnique).Value = sUnique & "100"
                .Cells(colUnique).Interior.Color = vbGreen
            Case "Island"
                .Cells(colUnique).Value = sUnique & "102"
                .Cells(colUnique).Interior.Color = vbCyan
            Case "Cay"
                .Cells(colUnique).Value = sUnique & "103"
                .Cells(colUnique).Interior.Color = vbBlue
            Case "Reef"
                .Cells(colUnique).Value = sUnique & "104"
                .Cells(colUnique).Interior.Color = vbYellow
            Case "Rock"
                .Cells(colUnique).Value = sUnique & "106"
                .Cells(colUnique).Interior.Color = vbMagenta
            End Select
        End With
    Next

End Sub

[snb]

@PH

I had the impression you were in your mid thirties