Zufallsgenerator Namensliste auf 2 Spalten verteilen

[el-rettev]

Hallo zusammen,

ich möchte gerne Personennamen zufällig auf 2 Spalten anordnen ohne Duplikate:
In Spalte A ist eine Namensliste hinterlegt.
In Spalte B und in Spalte C sollen nun zufällige Namen stehen, die aus Spalte A gezogen werden.
Dabei soll in Spalte B und Spalte C, sowie in den einzelnen Zeilen kein doppelter Name vorkommen.
Gerne mit VBA, sodass man nur einen Button klickt und dann eine zufällige Zuordnung erfolgt.
Sollte ein Name in Spalte A hinzugefügt oder entfernt werden, dann soll die zufällige Zuordnung dynamisch auf die vorhandenen Namen zugreifen.

Ausprobiert habe ich mich bereits, aber bei meinem Makro doppeln sich die Namen. 
Für eine eventuelle Formellösung der Hinweis, dass die Formel Excel 2016 kompatibel sein müsste.

Hilfsspalten mit Zahlen können eingefügt werden.

Ich bin über jeden Tipp dankbar. 

Vielen Dank.

[PIVPQ]

Gelöscht wegen Ignorliste

[LCohen]

E2#: =SORTIERENNACH(tab_Partner[Name];ZUFALLSMATRIX(ZEILEN(tab_Partner[Name])))
F2#: dito
G1: =SUMME(--(E2#=F2#))

G1 muss 0 ergeben; dann passt das Ergebnis. Entsprechend so lange F9 drücken, bis das der Fall ist.

Nur Excel >=2021, 365 oder ExcelWeb

[rate mit]

Hallo,

hier ein Vorschlag für Excel 2016...

[el-rettev]

Vielen Dank @ rate mit und LCohen!!!

Habe noch in der gestrigen Nacht getüftelt und habe folgende Idee versucht:
Formel in Spalte C

Code:

=SVERWEIS(WENN((REST(ZEILE()-1;ANZAHL2([Nummer]))+J$1)>ANZAHL2([Nummer]);REST(ZEILE()-1;ANZAHL2([Nummer]))+J$1-ANZAHL2([Nummer]);REST(ZEILE()-1;ANZAHL2([Nummer]))+J$1);tab_Prüfung[[Nummer]:[Name]];2;FALSCH)

Formel in Spalte D:

Code:

=SVERWEIS(WENN((REST(ZEILE()-1;ANZAHL2([Nummer]))+K$1)>ANZAHL2([Nummer]);REST(ZEILE()-1;ANZAHL2([Nummer]))+K$1-ANZAHL2([Nummer]);REST(ZEILE()-1;ANZAHL2([Nummer]))+K$1);tab_Prüfung[[Nummer]:[Name]];2;FALSCH)

So werden in den Spalten fortlaufende Nummerierungen erstellt. Bei 19 angelangt, startet die Nummerierung wieder mit 1.
Damit aber z.B. bei Mitarbeiter mit der Nummer 1 nicht die 1 vorkommt, addiere ich eine zufällige Zahl zwischen 1-18.
In Spalte D die gleiche Formel nur das eine andere Zahl addiert wird. Somit wird verhindert das in beiden Spalten z.B. mit 1 addiert wird und Duplikate vorkommen.
Die zu addierenden Zahlen sind in J1 und K1 hinterlegt und werden per VBA-Code zufällig generiert.
Bedingung:
Die Zahl muss zwischen 1 und der Anzahl an Personen - 1 sein.
Die Zahlen in J1 und K1 dürfen nicht identisch sein.

Code:

Sub Zufallszahlen()
    Dim rngJ As Range, rngK As Range
    Dim zufallszahlJ As Integer, zufallszahlK As Integer
    Dim anzahlen As Integer
   
    ' Definiere den Bereich für Zellen J1 und K1
    Set rngJ = ThisWorkbook.Sheets("Namensliste").Range("J1")
    Set rngK = ThisWorkbook.Sheets("Namensliste").Range("K1")
   
    ' Bestimme die Anzahl der Zahlen in deinem Bereich
    anzahlen = Application.WorksheetFunction.count(ThisWorkbook.Sheets("Namensliste").ListObjects("tab_Prüfung").ListColumns("Nummer").DataBodyRange) - 1
   
    ' Generiere eine Zufallszahl für Zelle J1
    zufallszahlJ = Int((anzahlen - 1 + 1) * Rnd + 1)
    rngJ.Value = zufallszahlJ
   
    ' Generiere eine Zufallszahl für Zelle K1, die sich von der Zufallszahl in Zelle J1 unterscheidet
    Do
        zufallszahlK = Int((anzahlen - 1 + 1) * Rnd + 1)
    Loop While zufallszahlK = zufallszahlJ
   
    rngK.Value = zufallszahlK
End Sub

Mit einem Sverweis suche ich nun die Zahl, die sich aus der Summe Formel + Zufallszahl ergibt und lasse mir den Namen zurückgeben.
Hatte am Anfang noch einige Zählenwenn-Formeln zur Prüfung hinterlegt, aber scheint zu klappen.
Das Hinzufügen und Entfernen von Personen ist auch benutzerfreundlich.
Ich hoffe ich habe nichts übersehen oder einen Gedankenfehler.
Falls jemand noch einen Optimierungsvorschlag hat, bin ich gerne offen dafür.

Vielen Dank an alle für die Unterstützung.

[HKindler]

Hi,

in Anlehnung an LCohen (also nur für aktuelles Excel) ohne dass man F9 drücken muss:

E2: =SORTIERENNACH(tab_Partner[Name];ZUFALLSMATRIX(ZEILEN(tab_Partner[Name])))
F2: =LET(
a;tab_Partner[Name];
b;WENN(ZEILE(E2)=2;E2;F1:F$2);
c;VSTAPELN(a;b;E2);
d;EINDEUTIG(c;;WAHR);
e;ZUFALLSBEREICH(1;ZEILEN(d));
f;INDEX(d;e);
f)
runter ziehen.

Besser - also mit einer einzigen Spillformel - habe ich es jetzt nicht hinbekommen. Vielleicht hat ja noch jemand eine Idee.

[LCohen]

Man könnte F:F auch fest mit E:E verdrahten. 

Entweder F3: =E2 runterkopiert (mit F2: =E20) oder nach einem fixen Durcheinanderschema.

[Gast 123]

Hallo

Verständnisfrage zur Formel:   Anzahl2(tab_Prüfung[Nummer]  handelt es sich dabei um ein Sheet??
Ich kann xlsx und xlsm Dateien nur mit LibreOffice öffnen, und sehe diesen obigen Formelteil.
Beim Konvertieren in Excel 2003 erhalte ich aber nur #NV als Formelwert. tab-Prüfung unbekannt!
Ich würde aber aus reiner Neugier gerne verstehen was diese Tab_Prüfung bewirkt.

mfg Gast 123

[LCohen]

Das ist Excels "intelligente-Tabellen"-Schreibweise mit den eckigen Klammern. Du kannst daraus auch normale Bezüge machen.

Es ist kein Sheet, sondern ein Table im Sheet. Auf Deutsch ist es leider eine Tabelle auf einem Tabellenblatt.

[HKindler]

Hi,

tabPrüfung ist hier der Name einer "intelligenten Tabelle". [Name] ist eine bestimmte Spalte dieser Tabelle. Solche Tabellen gibt es in XL2003 noch nicht. Statt dessen kannst du hier auch einfach $A$2:$A$20 verwenden.