formule gezocht om ontbrekende getallen te zoeken

[mariannevanh]

Ik heb een lange lijst van gegevens die genummerd zijn (meer dan 50.000 items). Door verschuivingen en andere organisatorische redenen zijn er "gaten" ontstaan in de nummering. Ik wil nieuwe gegevens toevoegen aan de lijst en daarbij de nog niet gebruikte nummers gebruiken.
Nu zoek ik dus (bij voorkeur) een formule (of een andere methode) die mij een lijst geeft van ongebruikte nummers.
Wie kan mij vertellen hoe ik zo'n lijst laat genereren?

In het bijgevoegde voorbeeld moeten dus de nummers 3 en 7 opleveren (die heb ik nu "hard" in kolom D gezet, maar die zou door de formule moeten bepaald moeten worden).

Vriendelijke groet,
Marianne

 

[AlexCEL]

Zo wellicht (Excel 365). Geeft alle nummers van 1 t/m 100 die niet gebruikt worden in kolom A.

=FILTER(RIJ(A1:A100);AANTAL.ALS(A1:A100;RIJ(A1:A100))=0;"")

 

[mariannevanh]

Dankjewel.
Ik gebruik echter Excel uit Office 2019. Die kent het commando FILTER niet...

 

[cow18]

zo ?

 

[snb]

Matrix-formule:

=SMALL(IF(COUNTIF($A$1:$A$100;ROW($1:$100))=0;ROW($1:$100));ROW(A1))

 

[mariannevanh]

Allereerst hartelijk dank voor de hulp!
Voordat ik aan dit avontuur begon, had ik een andere omslachtiger methode. Bij die methode bleek dat in mijn overzicht het eerste ontbrekende getal 704 is. Bij geen van de aangeboden formules werd dit getal genoemd. Waardoor dit komt, is mij niet duidelijk...
Mijn kolom met getallen bevindt zich in B6:B85571. Ik heb de beide formules als volgt veranderd:

=KLEINSTE(ALS(ISFOUT(VERGELIJKEN(RIJ(tot50duizend);$B$6:$B$85571;0));RIJ(tot50duizend);99999);RIJ())
=KLEINSTE(ALS(AANTAL.ALS($B$6:$B$85571;RIJ($6:$85571))=0;RIJ($6:$85571));RIJ(B6))

Welke fout heb ik hier gemaakt?

 

[HSV]

Plak de formule, druk F2 en sluit af met Ctrl+Shift+Enter.

 

[cow18]

er is een gedefinieerde naam aangemaakt "tot50duizend" en die staat los van je B6:B85571.
Die naam genereert alle cijfers van 1 tot 50.000 en zoekt die dan op in je B-range.
Wil je niet starten vanaf het cijfer 1 of verder gaan dan die 50.000, dan moet je dat bereik "tot50duizend" aanpassen.

=VERSCHUIVING(Blad1!$G$[COLOR="#FF0000"][SIZE=4]1[/SIZE][/COLOR];;;[COLOR="#FF0000"][SIZE=4]50000[/SIZE][/COLOR];)

@HSV, blijkbaar moet je in excel2019 niet meer aangeven dat het een matrixformule is, dus die opmerking was ik vergeten te maken.

 

[JEC.]

Bij de tweede formule moet je de rijnummers aanpassen zodat ze bij 1 starten

=KLEINSTE(ALS(AANTAL.ALS($B$6:$B$85571;RIJ($1:$85566))=0;RIJ($1:$85566));RIJ(B1))

 

[JEC.]

Ps: Met 85000 rijen gaat je bestand zeer traag worden als je matrix formules gebruikt

Ik zou eerder voor een macro gaan die je 1x of eens in de zo veel tijd runt

Sub jveer()
 Set jv2 = Sheets(1).Range("B6:B85571")
  ReDim ar(jv2.Rows.Count, 1)
   For i = 1 To 85566
     If Application.CountIf(jv2, i) = 0 Then
       ar(j, 0) = i
       ar(j, 1) = ""
       j = j + 1
     End If
   Next
  Sheets(1).Cells(1, 20).Resize(j) = ar
End Sub

of

Sub jvrr()
Set jv = Sheets(1).Range("B6:B85571")
  With CreateObject("scripting.dictionary")
     For i = 1 To 85566
        If Application.CountIf(jv, i) = 0 Then .Item(.Count) = Array(i, "")
     Next
    Sheets(1).Cells(1, 22).Resize(.Count) = Application.Index(.items, 0, 0)
  End With
End Sub

 

[cow18]

je hoeft toch geen tig matrixformules te tonen.
Toon bv. enkel de 3 kleinste ontbrekende cijfers, want je gaat die toch maar 1 per 1 gebruiken.
Dan is dat geen zo'n bezwaar.

Als je bereik van nummers in een tabel zit of een gedefinieerde naam is, dan kan je de voorgestelde formules nog eenvoudiger schrijven.

 

[snb]

Je vraag had voorkomen kunnen worden door wel gegevens van een record te verwijderen, maar niet het volgnummer van het record (=regel).
Maak eenmalig de ontbrekende volgnummers aan en je bent voor de toekomst klaar.

 

[alphamax]

1. Sorteer op kolom A
2. Bereken het verschil van een regel en de voorgaande regel
3. Zet bovenaan een filter
4. Overal waar het verschil niet 1 is, is een "gat"

 

[mariannevanh]

De eerste door @jveer getoonde macro in #10 in doet precies wat ik zocht. Bedankt allemaal voor het meedenken.