Nächste ungenutzte Zahl finden / doppelte Zahlen verhindern

[Foley]

Hallo,

ich habe eine Tabelle in der in Spalte A sich mehrfach wiederholende Namen stehen. Den Namen sollen in Spalte B Zahlen aus einem bestimmten Nummernbereich zugeordnet werden. Für "NameA" sollen zB. Zahlen von 1 bis 100 vergeben werden können. Für "NameB" Zahlen von 101 bis 200 usw... Die bereits vergebenen Zahlen erfüllen diese Bedingungen, sind aber teilweise durcheinander und teilweise wurden Zahlen gelöscht. Ich würde gerne für jeden Namen in Spalte A die jeweils nächste, freie Zahl des zugewiesenen Zahlenbereichs angezeigt bekommen. Oder noch besser wäre, wenn in Spalte B automatisch die jeweils nächste, freie Zahl des zugehörigen Zahlenbereichs erscheinen würde, sobald man in Spalte A einen der vorgegebenen Namen einträgt. Eine "Doppelnummerierung" sollte unbedingt verhindert werden. Jede Zahl darf nur ein mal vergeben werden.

Kennt da vielleicht jemand eine Lösung?

Schonmal vielen Dank im Voraus :)

[Ego]

Hallo F...,

ich nehme an, dass die Nummer sich nicht mehr ändern soll, auch wenn Zeilen mit dem gleichen Namen verschoben oder gelöscht werden. Ist das richtig?

Dann kommt meines Erachtens nur eine VBA-Lösung in Frage.

Wenn das für dich in Ordnung ist, lade doch bitte eine Beispieldatei hoch.

Sollen Lücken, die beim Löschen entstehen wieder gefüllt sein, bevor der Zähler hochgezählt wird?

[Foley]

Hi,

das ist absolut richtig. Wenn eine Nummer einmal zugeordnet ist, soll sie sich nicht mehr ändern.
Meine Tabelle benutzt bereits VBA um eine Mehrfachauswahl per Dropdown zu ermöglichen. Ich bin also offen für eine VBA Lösung.

Ich habe mal eine Beispieldatei angehangen. In Spalte A des Beispiels steht 10 mal der Wert "OrtA" in Spalte D sind zu diesen Orten Nummern vergeben.
Bei "OrtA" habe ich die Nummer 5 in der Reihe fehlen lassen. Die Nummerierung für "OrtA" geht also von 1 bis 11 und die 5 fehlt. Perfekt wäre, wenn in Spalte D
automatisch die 5 als nächste, freie Nummer erscheinen würde, sobald man in Spalte A "OrtA" eingibt. Danach wären in Spalte D für den Wert "OrtA" die
Nummern 1 bis 11 durchgehend vergeben. Würde man ein weiteres mal "OrtA" in Spalte A eintragen, sollte in Spalte D automatisch die 12 als nächste, freie
Nummer erscheinen.

Bis hier hin hätte ich eine Lösung in einem anderen Forum gefunden, die ich an meine Bedürfnisse anpassen kann.

Code:

{=MIN(WENN(ISTFEHLER(VERGLEICH(ZEILE(1:500);KKLEINSTE(D5:D505;ZEILE(1:500));0));ZEILE(1:500)))}

Allerdings soll die Nummerierung für "OrtB" erst bei 101 und für "OrtC" bei 201 usw. beginnen. Und ab hier scheitere ich. Zusätzlich wäre es toll, wenn eine Nummer wieder als frei betrachtet
wird, wenn in Spalte E meiner Beispieldatei der Wert "Entfernt" steht.

Ist sowas mit Excel möglich?

P.S.: @Ego Ich hoffe das klärt auch deine Frage wie der Zähler hochzählen soll.

[Käpt'n Blaubär]

Hallo,

Ist sowas mit Excel möglich?

mit Excel meiner bescheidenen Meinung nach eher nicht. Mit VBA schon.
Mal sehen, was die Formelmenschen dazu zu sagen haben.

[Elex]

Hi 

das mit den freien Nummer könnte man mit der Formel erledigen.

in Tabelle2
B2   =WENNFEHLER(VERGLEICH(1;1*(WENNFEHLER(VERGLEICH(ZEILE($A$1:$A$100)+100*(ZEILE(A1)-1);WENN((Tabelle1!$A$2:$A$50=A2)*(Tabelle1!$E$2:$E$50<>"Entfernt");Tabelle1!$D$2:$D$50);0);0)=0);0)+100*(ZEILE(A1)-1);"alle Vergeben")        'mit strg, shift, Enter abschliessen

Ich würde die Nummern dann lieber von Hand eintragen. Per VBA geht es auch, aber aufpassen das man nicht bei einer Zelle in Spalte A die man nicht ändern möchte in die Bearbeitungsleiste klickt. Sonst wird für die Zeile eine Nummer vergeben.
Oder du machst dir eine Bestätigungsabfrage dazu. Das überlasse ich aber dir.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Range

If Target.Column = 1 Then
With Sheets("Tabelle2").Range("a2:a100")
    Set n = .Find(Target.Value, LookIn:=xlValues)
     If Not n Is Nothing Then
        Target.Cells.Offset(, 3) = n.Offset(, 1)
     Else
     MsgBox "Ort nicht gefunden."
     End If
End With
End If

End Sub

[Foley]

Hi Elex,

danke für deinen Lösungsansatz. Ich kann deine Formel in meinem Beispiel für Tabelle2 B2 benutzen. Ich kann sie auch auf B3 und B4 anpassen. Allerdings verstehe ich nicht was ich tun muss damit sie für B3 nur freie Zahlen im Bereich zwischen 101 und 200 und für B4 nur freie Zahlen im Bereich von 201 bis 300 sucht. Für mich sucht sie immer nur freie Zahlen zwischen 1 und 100 oder, wenn ich (ZEILE($A$1:$A$100) auf  (ZEILE($A$1:$A$1000) abändere, Zahlen zwischen 1 und  1000. Aber das ist so sicher nicht im Sinne des Erfinders. Kannst du mir erklären wie ich sie anpassen muss? 

Nochmal danke und viele Grüße

Foley

[Elex]

Hi

Nichts anpassen sondern einfach nach unten kopieren.

Gruß Elex

[Foley]

Ach so geht das :D

Genial. Vielen Dank. Jetzt klappt es. Ich sehe allerdings nur, das sich dann der Bezug auf OrtA, OrtB und OrtC ändert. Bzw auf die Zellen in denen das steht. Kannst du mir erklären wie sich der Zahlenbereich ergibt? In meiner fertigen Tabelle muss ich den später wahrscheinlich noch anpassen. Ich weiß aber jetzt noch nicht wie groß er für die jeweiligen Orte werden wird.

Verstehe ich das richtig? $A$1:$A$100 deffiniert irgendwie die Größe des ersten Bereichs. Es werden Zahlen bis 100 akzeptiert (obwohl ich bis A100 nur bis 99 komme) Die +100 die zwei mal in der Formel vor kommen, deffinieren das der folgende Zahlenbereich um 100 größer sein kann als der erste. Darum ist die nächste, freie Zahl 101 auch wenn vorher noch nicht alle Zahlen bis 100 genutzt wurden? Wenn ich das richtig durchschaut habe, müsste ich sie so auch später immer anpassen können.

Nochmal danke :)

[Elex]

Hi Foley,

die Bereiche müssen an deine Tabelle 1 angepasst werden wenn sie mehr als 50 Zeilen aufweist.
 
=WENNFEHLER(VERGLEICH(1;1*(WENNFEHLER(VERGLEICH(ZEILE($A$1:$A$100)+100*(ZEILE(A1)-1);WENN((Tabelle1!$A$2:$A$50=A2)*(Tabelle1!$E$2:$E$50<>"Entfernt");Tabelle1!$D$2:$D$50);0);0)=0);0)+100*(ZEILE(A1)-1);"alle Vergeben")
 
 
Wenn du den Bereich von 1-100  / 101-200  usw.    in 1-20 / 21-40 ändern möchtest, dann die roten Werte
 
=WENNFEHLER(VERGLEICH(1;1*(WENNFEHLER(VERGLEICH(ZEILE($A$1:$A$100)+100*(ZEILE(A1)-1);WENN((Tabelle1!$A$2:$A$50=A2)*(Tabelle1!$E$2:$E$50<>"Entfernt");Tabelle1!$D$2:$D$50);0);0)=0);0)+100*(ZEILE(A1)-1);"alle Vergeben")
 
ändern in 20. 
Mit der Formel ergeben sich aber für jeden Ort gleich große Bereiche.
 
 
Wenn du unterschiedliche Bereiche brauchst musst du diese Formel nehmen und in Tabelle 2 C und D den Bereich eintragen. D1 muss leer sein.
 
____|___A__|__________B__________|__C_|__D_|_E_|_F_|
   1|Ort   |Nächste frei Nummer  |    |    |   |   |
   2|OrtA  |                     |   1|  18|   |   |
   3|OrtB  |                     |  19|  25|   |   |
   4|OrtC  |                     |  26|  33|   |   |
 
B2     =WENNFEHLER(VERGLEICH(1;1*(WENNFEHLER(VERGLEICH(ZEILE(INDEX(A:A;C2;):INDEX(A:A;D2;));WENN((Tabelle1!$A$2:$A$50=A2)*(Tabelle1!$E$2:$E$50<>"Entfernt");Tabelle1!$D$2:$D$50);0);0)=0);0)+D1;"alle Vergeben")       ‚strg, shift, Enter

Gruß Elex

[Foley]

Hi Elex,

die neue Formel ist ja noch viel besser. Ich konnte sie sofort an meine bestehende Tabelle angleichen. Für zwei Orte habe ich sie noch um ein "wenn oder" erweitert, da sie sich leider einen großen Zahlenbereich teilen müssen. Da ist leider bei der Vergabe geschlampt worden. Aber deine Formel ist wirklich genial. Vielen vielen Dank für deine Hilfe. 

Könnte man jetzt die leeren Zellen in Spalte D nicht in etwas so einrichten: "wenn in z.B. A50 "OrtA" eingetragen wird, nimm den Wert aus Tabelle2!$B$2 . Dabei dürfte aber nur der derzeitige Wert aus Tabelle2!$B$2 genommen werden und kein dauerhafter Bezug enstehen.  Geht sowas?

Nochmal vielen Dank für die tolle Hilfe :)

Gruß Foley