Namensliste prüfen

Christian_c60b37 · 23. Oktober 2011 um 23:39

Einen schönen guten Abend an alle Excel-Füchse,

ich habe eine Liste von Kursteilnehmern (rund 180) und die Namen sind jeweils in Vorname und Nachname getrennt (also zwei unterschiedlichen Zellen). Die Teilnehmer müssen jetzt eine Aufgabe erfüllen. Dies erfolgt in Zweiergruppen und an 8 unterschiedlichen Tagen. Ich habe daher eine zweite Tabelle in welcher die 8 Tage nebeneinander angeordnet sind. Innerhalb dieser Tage habe ich 11 Gruppen mit jeweils zwei Namen, Vor- und Nachname sind nach wie vor getrennt, die beiden Teilnehmer einer Gruppe stehen jeweils nebeneinander.

Das Problem: nicht alle Teilnehmer haben sich in die Gruppen eingetragen; ich habe nur rund 100 Einträge. Ich muß also irgendwie möglichst effizient herausbekommen welche Namen aus Tabelle 1 nicht in Tabelle 2 vorkommen. Fällt jemandem dazu ein einfacher Weg ein? Erschwerend kommt hinzu daß einige der Nachnamen doppelt vorkommen. Es reicht also nicht einfach nur nach den Nachnamen zu suchen, sondern die Vornamen müssen zusätzlich auch noch abgefragt werden. Ich brauche also also im Prinzip ein Syntax, die abfragt ob ein Name, welcher in beiden Tabellen über zwei Zellen verteilt ist, in einer zweiten Tabelle **irgendwo** vorkommt. Ist dies nicht der Fall, dann soll dieser Name in der ersten Liste ein Markierung erhalten (was problemlos in einer Spalte neben den Namen erfolgen kann).

Im Moment kriege ich das nicht ordentlich hin. Ist verständlich wie ich das mein? Hat einer von Euch eine Idee zur Lösung?

Dank im Voraus für alle Hilfestellungen,

Christian

abgemeldet5 · 24. Oktober 2011 um 00:03

Hallo Christian,
du könntest zähen wie oft jeder Kursteilnehmer in den jeweiligen Kursen eingetragen ist.
Ist das Ergebnis Null fehlt er/sie noch
Ist das Ergebnis >1 gibt es den Teilnehmer min. doppelt,
schau mal:

gruß Holger

Christian_c60b37 · 24. Oktober 2011 um 00:42

Hallo Holger,

interessanter Ansatz; darüber muß ich mal nachdenken. Einen Schönheitsfehler hat Dein Ansatz: in der zweiten Tabelle stehen eben die Teilnehmer ungünstigerweise nicht alle untereinander sondern auch nebeneinander. Das läßt sich durch einfaches umkopieren lösen, ist daher also kein großes Problem! Eleganter wäre es allerdings wenn sich das umkopieren vermeiden ließe. Es ist aber eine realisierbare Lösung die mir weiterhilft!

Christian

Reinhard_fb8cb7 · 24. Oktober 2011 um 07:55

interessanter Ansatz; darüber muß ich mal nachdenken. Einen
Schönheitsfehler hat Dein Ansatz: in der zweiten Tabelle
stehen eben die Teilnehmer ungünstigerweise nicht alle
untereinander sondern auch nebeneinander. Das läßt sich durch
einfaches umkopieren lösen, ist daher also kein großes
Problem! Eleganter wäre es allerdings wenn sich das umkopieren
vermeiden ließe. Es ist aber eine realisierbare Lösung die mir
weiterhilft!

Moin Christian,

wenn deine Tabelle2 so aussieht:

Tabellenblatt: [Mappe1]!Tabelle2
 │ A │ B │ C │ D │ E │ F │ G │ H │ I │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 1 │ │ Tag1 │ │ │ │ Tag2 │ │ │ │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 2 │ │ Name │ Vorn │ Name │ Vorn │ Name │ Vorn │ Name │ Vorn │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 3 │ G1 │ N17 │ V17 │ N4 │ V4 │ N34 │ V34 │ N47 │ V47 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 4 │ G2 │ N25 │ V25 │ N43 │ V43 │ N17 │ V17 │ N7 │ V7 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 5 │ G3 │ N15 │ V15 │ N31 │ V31 │ N33 │ V33 │ N3 │ V3 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 6 │ G4 │ N10 │ V10 │ N38 │ V38 │ N13 │ V13 │ N40 │ V40 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 7 │ G5 │ N4 │ V4 │ N34 │ V34 │ N47 │ V47 │ N18 │ V18 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 8 │ G6 │ N43 │ V43 │ N44 │ V44 │ N7 │ V7 │ N17 │ V17 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
 9 │ G7 │ N31 │ V31 │ │ │ N3 │ V3 │ N37 │ V37 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
10 │ G8 │ N38 │ V38 │ N13 │ V13 │ N40 │ V40 │ N36 │ V36 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
11 │ G9 │ N34 │ V34 │ N47 │ V47 │ N18 │ V18 │ N50 │ V50 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
12 │ G10 │ N44 │ V44 │ N7 │ V7 │ │ │ N20 │ V20 │
───┼─────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┼──────┤
13 │ G11 │ N33 │ V33 │ N3 │ V3 │ N37 │ V37 │ N29 │ V29 │
───┴─────┴──────┴──────┴──────┴──────┴──────┴──────┴──────┴──────┘
A1:I13
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363
(„Tag1“ steht mittig über B1:E1, „Tag2“ mittig über F1:I1 usw.)

und deine Tabelle1 sieht so aus:

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │
───┼──────┼──────┼─────────┤
 1 │ Name │ Vorn │ Gruppen │
───┼──────┼──────┼─────────┤
 2 │ N1 │ V1 │ │
───┼──────┼──────┼─────────┤
 3 │ N2 │ V2 │ │
───┼──────┼──────┼─────────┤
 4 │ N3 │ V3 │ │
───┼──────┼──────┼─────────┤
 5 │ N4 │ V4 │ │
───┼──────┼──────┼─────────┤
 6 │ N5 │ V5 │ │
───┼──────┼──────┼─────────┤
 7 │ N6 │ V6 │ │
───┼──────┼──────┼─────────┤
 8 │ N7 │ V7 │ │
───┼──────┼──────┼─────────┤
 9 │ N8 │ V8 │ │
───┼──────┼──────┼─────────┤
10 │ N9 │ V9 │ │
───┼──────┼──────┼─────────┤
11 │ N10 │ V10 │ │
───┴──────┴──────┴─────────┘
A1:C11
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Dann drücke Alt+F11, Einfügen Modul, dortrein kopierst du nachstehenden Code, schließt den Editor.
In Excel läßt ud dann über Alt+F8 das Makro „Gruppen“ ausführen.

Gruß
Reinhard

Sub Gruppen()
Dim Zei As Long, Such As Range, Gef As String, Gruppen As String
Dim wks2 As Worksheet
Set wks2 = Worksheets("Tabelle2")
With Worksheets("Tabelle1")
 For Zei = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
 Gruppen = ""
 Set Such = wks2.Range("B3:AG13").Find(.Cells(Zei, 1).Value)
 If Not Such Is Nothing Then
 Gef = Such.Address
 Do
 If Such.Column Mod 2 = 0 Then
 If Such.Offset(0, 1).Value = .Cells(Zei, 2).Value Then
 Gruppen = Gruppen & wks2.Cells(Such.Row, 1).Value & ","
 End If
 End If
 Set Such = wks2.Range("B3:AG13").FindNext(Such)
 Loop While Not Such Is Nothing And Such.Address Gef
 End If
 If Len(Gruppen) \> 0 Then Gruppen = Left(Gruppen, Len(Gruppen) - 1)
 .Cells(Zei, 3).Value = Gruppen
 Next Zei
End With
End Sub

abgemeldet5 · 24. Oktober 2011 um 13:13

Hallo Christian,
warum erweiterst du nicht einfach den Suchbereich wo gezählt werden soll?

=SUMMENPRODUKT(($E$1:blush:J$50=A2)*($F$1:blush:K$50=B2))

$E$1 J$50=A2)
$F$1 K$50=B2)
Beachte, das die beiden Bereiche in dehnen nach Name bzw. Vorname gesucht wird, um eine Spalte versetzt ist, genau wie es die Suchkriterien in Spalte A und B sind!!

Gezählt wird dann immer wenn sich Name und Vorname nebeneinander in zwei Spalten befinden.

Gruß Holger

Christian_c60b37 · 24. Oktober 2011 um 14:38

Hallo Holger,

ich hatte gestern abend nur kurz mit Deinem Vorschlag gespielt und dieser hatte mit meinem erweiterten Bereich nicht funktioniert, was ein dummer Fehler meinerseits war. Die sekundäre Liste wurde über die vlookup-Funktion erstellt. Es wurden die Nachnamen eingegeben und über vlookup wurde automatisch nachgesehen welches der entsprechende Vorname ist. Wenn Zellen leer bleiben, dann liefert vlookup allerdings automatisch »#N/A«. Ich hatte die Liste zwar kopiert und nur die Werte eingefügt, ich habe allerdings erst jetzt geschnallt daß die Funktion »sumproduct« grundsätzlich »#N/A« als Antwort liefert wenn sich im Daten-Array **irgendwo** ein »#N/A« befindet. Nachdem ich alle #N/A-Einträge gelöscht habe funktioniert Dein Vorschlag völlig einwandfrei. Herzlichen Dank für Deine Mühe! Sumproduct scheint eine der Funktionen zu sein, die sehr mächtig sind, allerdings auch nicht selbsterklärend funktionieren.

Grüße aus London,

Christian

Christian_c60b37 · 24. Oktober 2011 um 14:41

Hallo Reinhard,

herzlichen Dank für Deine Mühe. Dein Beispiel gibt die Tabelle im Prinzip schon genau richtig wieder, allerdings sind einige Details anders, welche das Makro nicht funktionieren lassen. Ich muß mich dringend etwas ausführlicher mit Makros auseinandersetzen und werde das bei Gelegenheit anpassen. Im Moment ist das Problem relativ eilig und Holger hat eine funktionierende Lösung geliefert, die meinen Zwecken genügt. Ich werde aber wie gesagt auf Dein Makro mit Sicherheit zurück kommen.

Herzliche Grüße,

Christian

abgemeldet5 · 24. Oktober 2011 um 16:15

Hallo Holger,

…Es
wurden die Nachnamen eingegeben und über vlookup wurde
automatisch nachgesehen welches der entsprechende Vorname ist.
Wenn Zellen leer bleiben, dann liefert vlookup allerdings
automatisch »#N/A«.

Hallo Christian,
diese Variante ist bei 180 Teilnehmern aber nicht ohne Tücken.
Da vlookup [deutsch_Sverweis()] immer den ersten Treffer liefert bleiben alle 2. und weiteren Vornamen mit gleichen Nachnamen unberücksichtigt!!

Die Fehlermeldung könntest du umgehen in dem die die Formel etwas erweiterst: =if(iserror(deine_Formel)=0;deine_Formel;„nicht gefunden“)
Dann hast du auch keine Probleme mit dem Zählen.

Gruß Holger

Reinhard_fb8cb7 · 24. Oktober 2011 um 17:11

Dein Beispiel gibt die Tabelle
im Prinzip schon genau richtig wieder, allerdings sind einige
Details anders, welche das Makro nicht funktionieren lassen.

Hallo Christian,

meines und das von Holger konstruierte geht von zwei Tabellen aus.
Tab1 ist die zweispaltige Liste der Teilnehmer (TN).
Tab2 hat als Spaltentitel die 8 Tage und als zeilentitel die 11 Gruppennamen.
TN sind dort zweispaltig eingetragen.

In Tab1 holst du mit der Formel von Holger o.ä. in die dritte Spalte die Infos welcher TN in Tab2 eingetragen ist.

Wo also und warum stehen da Vlookup-Befehle? In den VornamenZellen um sich die Vornamen aufgrund des Nachnamens aus Tab1 zu holen?
Macht keinen Sinn, denn wenn da Müller steht und der Vorname fehlt so weißt du doch gar nicht welcher Müller sich da angemeldet hat wenn es den dreimal in Tab1 gibt.
Und wüßtest du es bräuchtest du Vlookup nicht mehr

Existiert noch eine dritte Tab, datenquelle?

Da dies mit Vlookup noch unklar ist, vielleicht ist es hilfreich du lädst eine Beispielmappe hoch mit http://www.uploadagent.de/ o.ä., s. FAQ:2606

Gruß
Reinhard

Christian_c60b37 · 24. Oktober 2011 um 23:29

Hallo Reinhard,

ich glaube ich habe mich da etwas mißverständlich ausgedrückt. Also, es gibt ein globale Teilnehmerliste, die man vom Sekretariat bekommen kann. Dort sind alle Teilnehmer verzeichnet, direkt untereinander. Für die Aufgabe sollten Zweiergruppen gebildet werden und es wurden Listen ausgehängt, in welcher sich die Gruppen selber eintragen mußten. Diese Listen mußten übertragen werden. Um hier die Arbeit zu erleichtern und Fehler zu vermeiden hat ein Kollege jeweils den Nachnamen eingegeben und sich dann über vlookup den Vornamen ausgeben lassen. Den Vornamen kann man dann direkt mit der handgeschriebenen Liste vergleichen. Dies gewährleistet daß beide Namen korrekt geschrieben wurden, daß der Teilnehmer auch tatsächlich eingeschrieben ist etc. Es spielt hier also keine Rolle daß manche Namen doppelt oder sogar dreifach vorkommen, man erkennt sofort daß der von Excel gegebene Vorname nicht mit der handschriftlichen Liste übereinstimmt und kann dann ggf. manuell nachschlagen (insgesamt waren glaube ich vier Namen von 180 doppelt, das ist kein großes Problem). Ich sollte nun in dieser bereits erstellten Datei abgleichen wer sich nicht in die aushängenden Listen eingetragen hatte.

Das sollte erklären woher die vlookup-Befehle gekommen sind. Die Liste ist in jedem Fall Dank Eurer Hilfe vervollständigt.

Herzliche Grüße,

Christian