Unieke referentie genereren uit 1, 2 of 3 getallen

[JMu]

Goedemorgen,

Graag zou ik een formule willen toepassen in mijn excel werkblad (eventueel VBA formule) die uit 1, 2 of 3 afzonderlijke getallen (Kolommen A, B en C in voorbeeld) een uniek referentie ID genereert. De getallen in de kolommen zijn altijd tussen 0 en 99 dus nooit groter dan 99. De uitkomst of referentie moet in ieder geval kleiner zijn dan de 3 getallen samen. De opmaak van het referentie ID is in dit stadium nog onbelangrijk. Het mogen getallen, tekens of een combinatie hiervan zijn. Alleen niet komma gescheiden. Doel is dus om de getallen efficienter (dus verkleind) op te slaan. Let wel, zoals in het voorbeeld is te zien gebruik ik "toevallig" dezelfde 3 getallen in steeds een andere volgorde. Hier moet dus niet hetzelfde referentie ID uitkomen! Als dit al mogelijk is kan dit dan uberhaupt zonder hulptabel? Is niet noodzakelijk overigens.

Ik heb een voorbeeldbestand bijgevoegd. Helaas ben ik nog niet erg opgeschoten omdat ik mijn hersenen nog loop te kraken hoe hier in vredesnaam invulling aan is te geven...

Ik ben benieuwd of hier iemand een aanzet voor kan geven! Misschien vraag ik ook wel naar iets onmogelijks, excuses dan alvast

 

[Thoralf]

Kijk eens op http://members.chello.nl/g.hendriksen/roostervb.htm onder Permutaties en Combinaties. Misschien helpt het je op weg.

 

[wampier]

Wat mag het antwoord WEL bevatten? alles behalve komma's ?

 

[JMu]

Wat mag het antwoord WEL bevatten? alles behalve komma's ?

Correct, zolang de output maar kleiner is dan de getallen samen en dus efficienter kan worden weggeschreven (minder tekens).

 

[wampier]

Ik heb net even gerekend, binnen standaard ascii is het kleinste haalbaar 4 tekens. Indien extended ascii tekens ook mogen kan het in 3. Echter is extended ascii misschien niet een optie. extended ascii bevat dingen als de "a" met puntjes, accenten en hoedjes.

Wat is je voorkeur?

 

[JMu]

Ik heb net even gerekend, binnen standaard ascii is het kleinste haalbaar 4 tekens. Indien extended ascii tekens ook mogen kan het in 3. Echter is extended ascii misschien niet een optie. extended ascii bevat dingen als de "a" met puntjes, accenten en hoedjes.

Wat is je voorkeur?

Zelf denk ik dat het voor de leesbaarheid beter is om geen uitbreidingen te gebruiken. Wellicht is het handig om een referentietabel te gebruiken zodat daar de tekens gedefinieerd kunnen worden. Hoe meer beschikbare tekens, hoe korter de notatie neem ik aan?

 

[wampier]

Je kunt daar zelf makkelijk aan rekenen:

er zijn een miljoen combinaties (0-999.999). Maximaal 4 tekens geeft : minstens 32 combinaties (4de machtswortel van 1 miljoen). maximaal 3 tekens geeft: minimaal 100 combinaties (3de machtswortel van 1 miljoen)

Daar standaard ascii slechts 94 geldige tekens kent, kan met standaard ascii niet elke combinatie worden afgedekt. tussen 32 en 100 tekens in de set veranderd het maximaal aantal tekens niet. Dat blijft namelijk 4. Wel kunnen relatief meer combinaties korter worden weergegeven.

 

[JMu]

Ik heb zelf ook zitten rekenen met het aantal mogelijke combinaties: Als er 94 tekens beschikbaar zijn kunnen er volgens mijn berekening met 4 karakters (94^4) 78.074.896 combinaties worden gemaakt.

Wat dit probleem lastig maakt is dat ik graag een formule of code wil gebruiken die vanuit die 6 cijfers een uniek ID genereert en het uiteindelijk ook weer andersom kan genereren. Deze informatie was nog niet bekend, omdat ik eerst benieuwd was of het uberhaupt te doen is om een uniek ID te genereren uit de input van de 3 getallen/6 cijfers.

 

[wampier]

Klopt. Ik gaf toch ook aan dat het met 4 characters al met 32 unieke combinaties ging werken? Voor 3 heb je er echter 100 nodig:

100^3 = precies 1.000.000 mogelijkheden.

Nog 1 vraag, wil je altijd 4 characters ook al is de combinatie: 00 00 01 ? of wil je dan een 1 character code?

 

[JMu]

Klopt. Ik gaf toch ook aan dat het met 4 characters al met 32 unieke combinaties ging werken? Voor 3 heb je er echter 100 nodig:

100^3 = precies 1.000.000 mogelijkheden.

Nog 1 vraag, wil je altijd 4 characters ook al is de combinatie: 00 00 01 ? of wil je dan een 1 character code?

Sorry, je hebt helemaal gelijk maar ik had je beschrijving niet goed geinterpreteerd.

In principe wil ik wel vier karakters omdat dit beter voor de gewenste structuur is.

 

[wampier]

Hier een functie die je kunt gebruiken om te converteren naar 4 characters. voeg de code toe aan een lege vba module en je kunt de functie dan in het werkblad aanroepen met: =converteer(a2:c2)

Let op dit is alleen een demo. Voor een finale versie zou ik een kleinere set kiezen. Bijvoorbeeld alle medeklinkers, cijfers en @. je hebt dan 32 characters en dat is voldoende. Ik bouw hier de lijst automatisch op met wat copy/paste, maar is geen aanbeveling voor de uiteindelijke uitvoering. Vooral niet omdat je ook weer terug wilt en dan is een kleine set veel makkelijker.

Function converteer(reeks As Range) As String
    Dim tekenreeks As String
    
    For Each cell In reeks
        tekenreeks = tekenreeks + Format(cell, "00")
    Next cell
    waarde = Val(tekenreeks)
    
    Dim keuzen() As Variant
    ReDim keuzen(0)
    teller = 0
    For i = 1 To 26
        keuzen(teller) = Chr(64 + i)
        ReDim Preserve keuzen(UBound(keuzen) + 1)
        teller = teller + 1
        keuzen(teller) = Chr(96 + i)
        ReDim Preserve keuzen(UBound(keuzen) + 1)
        teller = teller + 1
    Next

    For i = 1 To 10
        keuzen(teller) = Chr(47 + i)
        ReDim Preserve keuzen(UBound(keuzen) + 1)
        teller = teller + 1
    Next

    A = waarde Mod 62
    B = Application.WorksheetFunction.RoundDown(waarde / 62, 0)
    B = B Mod 62
    C = Application.WorksheetFunction.RoundDown(waarde / 3844, 0)
    C = C Mod 62
    D = Application.WorksheetFunction.RoundDown(waarde / 262144, 0)
    D = D Mod 62
    
    
    tekenreeks = keuzen(D) + keuzen(C) + keuzen(B) + keuzen(A)
    tekenreeks = Mid(tekenreeks, 1 + lengte, 4 - lengte)
    converteer = tekenreeks
End Function

 

[JMu]

Bedankt wampier. Ik heb het getest en werkt in basis prima zo!

De tekenset wordt inderdaad automatisch opgebouwd nu. Je schrijft dat dit wellicht niet wenselijk is omdat je met 32 karakters ook uit de voeten komt. Moeten dan alle 32 karaktes worden benoemd in de code of kan dit ook middels automatische opbouw?

 

[wampier]

Gedeeltelijk automatisch. Je wilt de klinkers niet meenemen omdat je een klant geen file wil sturen met referentie f*ck, als je begrijpt wat ik bedoel. Zo zijn er nog wel een paar mogelijkheden te bedenken.

Je kunt dus alle letters automatisch toevoegen en klinkers weer verwijderen oid. Met 32 tekens kun je echter ook een array met de hand bouwen.

reeks = array("b","c","d","f" ... etc

Je kunt ook een volstrekt willekeurige volgorde nemen op die manier, waardoor het bijna onmogelijk wordt om het originele nummer te raden.

 

[sylvester-ponte]

hallo
het werkt prima
toch een vraagje aan de maker waarom 262144 en niet 62^3=238328 in deze code

d = Application.WorksheetFunction.RoundDown(waarde / 262144, 0)

groet sylvster

 

[wampier]

goede catch het hoort inderdaad wel 238328 te zijn. Volgens mij heb ik een vorige versie gecopieerd met nog 64 characters en daarna niet goed aangepast in de copy.