automatisch nummer met cijfers en letter

[gertvn]

Goedemiddag Excel specialisten..

Ik ben bezig met een Excelsheet maar het lukt me niet deze te laten doornummer als ik bij het laatste getal ben, ik hoop dat er iemand is die kan helpen.
Deze lijst begint bij.
BAA000
en moet dan doorlopen tot
BAA999
daarna moeten de cijfer weer terug naar 000 en wordt de A een B
dus dit zou het dan worden
BAA000
t/m
BAA999
dan verder met
BAB000
t/m
BAB999
ect
BAC000
t/m
BAC999

Het allermooiste zou zijn als ik bijvoorbeeld op blad 1 het start nummer opgeef bijvoorbeeld BAB000 en dan het aantal bijvoorbeeld 1010
en dat er dan op blad 2 - 1010 nummer komen te staan zoals hierboven omschreven.
Ik hoop dat iemand hier mee kan helpen

Grt Gert

 

[LucHeyndrickx]

Maak deze functie en gebruik die met de volgende formule: =Doorvoeren(A1)

Function Doorvoeren(Rng As Range) As String
    Application.Volatile
    Dim intCijfer As Integer
    Dim strLetter1 As String
    Dim strLetter2 As String
    Dim strLetter3 As String
    Dim intLetter1 As Integer
    Dim intLetter2 As Integer
    Dim intLetter3 As Integer
    Dim intLetter As Integer
    Dim str As String
    Dim intPlusLetter As Integer
    
    
    intPlusLetter = 64
    str = Rng.Value
    
    intCijfer = Right(str, 3)
    
    intLetter = Fix((intCijfer + 1) / 1000)
    
    intCijfer = (intCijfer + 1 Mod 1000)
    If intCijfer = 1000 Then
        intCijfer = 0
    End If
    
    strLetter1 = Left(str, 1)
    strLetter2 = Mid(str, 2, 1)
    strLetter3 = Mid(str, 3, 1)
    intLetter1 = Asc(strLetter1) - intPlusLetter
    intLetter2 = Asc(strLetter2) - intPlusLetter
    intLetter3 = Asc(strLetter3) - intPlusLetter
    intLetter3 = intLetter3 + intLetter
    If intLetter3 = 27 Then
        intLetter2 = intLetter2 + 1
        intLetter3 = 1
    End If
    
    If intLetter2 = 27 Then
        intLetter1 = intLetter1 + 1
        intLetter2 = 1
    End If
    
    
    
    Doorvoeren = Chr(intLetter1 + intPlusLetter) & Chr(intLetter2 + intPlusLetter) + Chr(intLetter3 + intPlusLetter) & Format(intCijfer, "000")
    
End Function

 

[JEC.]

Of met formule (met de functie van Luc merk ik dat het bestand traag wordt, weet niet of het aan mij ligt)

Zet BAA000 in A1 en onderstaande formule in A2, trek vervolgens door.

="BA"&ALS(--RECHTS(A1;3)=999;TEKEN(CODE(DEEL(A1;3;1))+1);DEEL(A1;3;1))&ALS(LENGTE(--RECHTS(A1;3)+1)=1;"00";ALS(LENGTE(--RECHTS(A1;3)+1)=2;"0";""))&ALS(--RECHTS(A1;3)=999;"000";--RECHTS(A1;3)+1)

 

[LucHeyndrickx]

Zou inderdaad wel 's kunnen dat een UDF (eigen gemaakte functie) het bestand trager maakt, als het te ver doorgetrokken wordt.
Maar jouw formule stopt wel te vroeg:
BBC999 zal niet automatisch naar BBD000 gaan .
Wil je dat allemaal opvangen met een formule, dan vrees ik dat ze bijna onleesbaar gaat worden.
Hier zou ik er voor opteren, om geen formule te gebruiken, maar door middel van VBA een vaste waarde in de cel te zetten.
Met de nodige problemen uiteraard als je een rij zou deleten.

 

[VenA]

Ik zie nergens een bestand. Het mooiste zou zijn dat je en een voorbeeld plaatst en aanduidt wat waar moet komen. Dan zijn de speculaties zoals hierboven overbodig.

Volgens mij valt jouw vraag onder de noemer 'Permutaties' Als je hier eens op zoekt dan vind je vast wel een oplossing.

 

[cow18]

ik heb even de uitdaging aanvaard om het via een formule te doen, een beetje hexadecimaal en octaal denken, niet rechttoe-rechtaan

 

[LucHeyndrickx]

@Cow, vul in je eerste cel 's dit in:
BAZ999

Overigens denk ik dat het onwaarschijnlijk is dat er zo'n lange lijst ID's (ik veronderstel dat het dat is) zal komen.
Bij de eerste Z zit je immers al aan een lijst van 26000 unieke tekens. De tweede Z komt dan uit op 26 x 26000, en de derde Z op 26 x 26 x 26000.

 

[cow18]

@Luc, mijn fout, ik dacht hexadecimaal en na BAF volgde bij mij BBA, dus een Z kwam niet in het spel voor.
Bon, even kijken wat TS voor ogen had, ik denk eerder jouw insteek.
Eigenlijk is dat dan nog te realiseren met een lichte aanpassing.
Ik zat anders te denken aan een UDF binnen een formule, dat heb ik nog nooit gedaan, eventjes proberen, just for the fun.

 

[LucHeyndrickx]

zelfs een UDF binnen een formule zou tijd kosten.
Bedenk gewoon dat de miljoenste rij al afhankelijk is van de eerste rij.
Dus zal die berekening telkens 1 miljoen keer uitgevoerd moeten worden, en met de resultaten van de eerste uitkomst wordt verder gewerkt voor de volgende.
Onbegonnen werk dus.

Je kan wel mijn functie aanpassen door de Range als argument te vervangen door een waarde (strWaarde as String).
En vervolgens roep je die functie aan bij de Change-Event van je werkblad, om de volgende waarde te bepalen. Dan heb je als voordeel dat de berekening slechts éénmaal wordt uitgevoerd.

Wanneer het echter om zulke uitgebreide tabellen gaat, vraag ik me af of Access geen betere optie zou zijn?
Ook daarin zal je trouwens zelf een functie moeten ontwerpen, maar in Access heb je het wat makkelijker als je met databases werkt. Bovendien zal Access minder te lijden hebben met het onvrijwillig om zeep helpen van werkbladen.

Mijn functie kan trouwens ook wel een stukje korter. Maar de tijdswinst die je dan krijgt is toch nog onvoldoende hoor.

 

[cow18]

eventjes met een macro >1.000.000 combinaties berekend in 0.75 sec, wegschrijven duurde wel 3.5 sec.
Macro moet je wel zelf aanroepen.

 

[gertvn]

Update
Ik had de formule van JVeer ook nog even getest met een kleine aanpassing omdat mijn opgegeven data niet helemaal klopte en dat werkt ook (dan kan ik max. 26.000 codes maken omdat hij niet doorgaat van BAAZ999 naar BABA000 het zijn dan wat meer bestanden maar dit is wel een oplossing mocht het doornummeren lastig zijn

eventjes met een macro >1.000.000 combinaties berekend in 0.75 sec, wegschrijven duurde wel 3.5 sec.
Macro moet je wel zelf aanroepen.

Alleerst wil ik iedereen bedanken voor zijn reactie, ik heb gekeken welke reactie voor mijn als leek het beste te begrijpen is en dat was die van cow18, wat niet wil zeggen dat de andere niet goed zijn maar dat ligt echt aan mijn kennis niveau

Beste Cow18,

Hartelijk dank voor het voorbeeld bestand, de alfanumerieke reeks is inderdaad een lange reeks met bijna 500.000 nummers die ik kan verdelen over meerdere Excel bestanden bijvoorbeeld 50.000 nummers per bestand want deze moet nog door een barcode programma heen (leest CSV bestand) en die kan niet meer aan dan ca 50.000 nummer in 1 keer

Ik ben echt een beginner wat Excel betreft en heb in jou voorbeeld bestand gekeken de opzet klopt redelijk met wat ik zoek, (al snap ik niet helemaal wat ik met de linker "string" en "plus" kan doen?)
Maar als ik de alfanumerieke reeks doortrek in kolom D doet hij wel wat ik nodig heb en gaat hij netjes door van BAAA999 naar BAAB000 dus dat is goed .
Alleen als ik de code in cel D1 aanpas (want ik had een foutje gemaakt want het is BAAA (3xA)) dan werkt hij tot BAAA999 en daarna staat er in cel C1001 "getal"? en ik wil ook graag als dat kan een reeks maken waarbij de eerste letter begint met een C en een D en een M dat hoeft uiteraard niet in hetzelfde bestand mogen ook 4 bestanden zijn bijvoorbeeld

Ik weet niet of dit mogelijk is Excel maar je bent al goed op weg als ik het zo bekijk :d ?

Iedereen alvast bedankt voor het meedenken

 

[cow18]

zie bijlage.
Sla deze bijlage eerst ergens op je computer op.
Vul daarna een reeks in A1 in, het belangrijkste is dat de laatste 3 karakters cijfers zijn (is aanpasbaar binnen de macro) en de rest (aantal onbelangrijk) (hoofd)letters.
Daaronder vul je het gewenste aantal in (50.000 in jouw geval)
En je drukt op de knop.
Resultaat, je krijgt 50.000 opeenvolgende combinaties en je krijgt die bovendien ook al in een CSV-bestand in hetzelfde path als je werkmap.

 

[pixcel]

Andere benadering, met een stuurtabel en Power Query. Na wijzigen van de stuurtabel de groene tabel Vernieuwen (rechts klik in tabel).
De functie Combineer is recursief. Rijen toevoegen/verwijderen aan de stuurtabel kan. De kolommen Teken en Aantal zijn er voor het gemak maar zijn niet nodig.

 

[cow18]

Die benadering is wel heel flexibel en gebruiksvriendelijk.
Ik heb wel nog maar 2013 en dan krijg ik een waarschuwing dat er functionaliteiten verloren gaan als ik kijk hoe het gemaakt is.
Dus spijtig ...

 

[pixcel]

Wat raar, die melding. Maar het werkt wel als je refresht?

Dit is de m-code van Combineer:

(TableIn as table, optional Rownr as number) as table =>
let
    Rownr = if Rownr = null then 1 else Rownr,
    AddedListColumn = Table.AddColumn(TableIn, "List", each {Bereiken{Rownr}[Min]..Bereiken{Rownr}[Max]}),
    ExpandedListColumn = Table.ExpandListColumn(AddedListColumn, "List"),
    MergedColumns = Table.CombineColumns(ExpandedListColumn,{"Column1", "List"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column1"),
    Resultaat = if Rownr < Table.RowCount(Bereiken) - 1 then Combineer(MergedColumns, Rownr + 1) else MergedColumns
in
    Resultaat

En dit is de query die wordt aangeroepen:

let
    Source = Table.FromList({Bereiken{0}[Min]..Bereiken{0}[Max]}),
    Resultaat = Combineer(Source)
in
    Resultaat

Bereiken is de stuurtabel.

 

[snb]

Of

Sub M_snb()
  c00 = "BAAAZAAZA987"
  ReDim sn(49999, 0)
  y = --Right(c00, 3)
  sp = Array(Asc(Mid(c00, 7, 1)) - 65, Asc(Mid(c00, 8, 1)) - 65, Asc(Mid(c00, 9, 1)) - 65)
   
  For j = 0 To UBound(sn)
    sn(j, 0) = Left(c00, 6) & Chr(65 + (sp(0) + (y + j) \ 26000) Mod 26) & Chr(65 + (sp(1) + (y + j) \ 26000) Mod 26) & Chr(65 + (sp(2) + (y + j) \ 1000) Mod 26) & Format((y + j) Mod 1000, "000")
  Next

  Cells(1, 8).Resize(UBound(sn) + 1) = sn
End Sub

 

[gertvn]

Super bedankt dit is precies wat ik zoek.. werkt perfect zo

zie bijlage.
Sla deze bijlage eerst ergens op je computer op.

 

[gertvn]

Zo zie je maar er zijn meer wegen die naar Rome leiden deze doet ook wat ik wil. bedankt

Andere benadering, met een stuurtabel en Power Query. Na wijzigen van de stuurtabel de groene tabel Vernieuwen (rechts klik in tabel).
De functie Combineer is recursief. Rijen toevoegen/verwijderen aan de stuurtabel kan. De kolommen Teken en Aantal zijn er voor het gemak maar zijn niet nodig.

 

[gertvn]

Deze vraag is voor mijn opgelost, iedereen heel hartelijk dank voor het meedenken, ik sluit hiermee de vraag..:thumb: