Doppelte Namen (Text) in mehreren Zeilen erkennen

josef24

Erfahrenes Mitglied
#1
Hallo und guten Morgen. Ich habe ein Problem mit der Auswertung von gleichen Personen (Namen) in mehreren Zeilen für eine Statistik. In Spalte "D" habe ich 8 Zeilen mit Namen, darin enthalten sind aber nur 4 Personen. Davon 2 Frauen und 2 Männer. Meine Frage ist: Wie kann ich die einzelnen Personengruppen ermitteln, wenn es jeweils 4 Datensätze, aber nur zwei Personen sind.

Mein Versuch:
Code:
=SUMME(WENN(HÄUFIGKEIT(D2:D17;D2:D17)=2;1))
Ich vermute man kann so nur Zahlen auswerten, nicht aber Text. Hier bräuchte ich bitte mal Unterstützung, weil mir gelingt es einfach nicht.
Danke für eure Unterstützung. Gruß Josef
 

Anhänge

#2
Ich sehe deine Daten und dein grundsätzliches Proble. Was ich aber noch nicht verstehe, was willst du genau ausgewertet haben?
Also, was ist das Ziel?
 

josef24

Erfahrenes Mitglied
#3
Danke für dein Interesse. Mein Ziel ist: Die Anzahl "Frau" und "Herr" separat zu zählen. Dabei muss das mehrfach vorkommen in der Spalte "D" der nur einmal berücksichtigt / gezählt werden. Weil hier nur die Anzahl Personen "Frau" zum Nachnamen, oder "Herr" zum Nachnamen relevant sind. Das Ergebnis in der Tabelle Feld D16 wäre 2 für Anzahl "Frau", und Feld D17 wäre 2 für Anzahl "Herr". Hier im Beispiel ist das ja überschaubar, im Original lässt sich das aber natürlich nicht mehr überschauen. Danke und Gruß Josef

Habe folgenden Versuch unternommen. Das Ergebnis ist in der ersten Zeile dann "2" und in der zweiten betroffenen Zeile entweder "WAHR" oder "FALSCH". Mein Ergebnis sollte aber die 1 für die jeweiligen Zeilen
z. B. 2 und 3 sein.

Code:
=WENN(ZÄHLENWENNS(D$1:D2;D3);C3="Herr";ZÄHLENWENNS(D3:D10;D3))
 
Zuletzt bearbeitet:
#4
Nicht einfach. Ich als VBA-Programierer und Datenbankmensch würde das über SQL lösen.
Also mit einem VBA-Modul das über SQL die eindeutigen Datensätze zählt.

Die Methode, ich nenne sie mal myCount() kannst du als Formel verwenden
Als Paramter gibst du den ganzen Datenbereich an, der Eindeutig sein sollt. Inklusive Titelzeile!
Als Zweiten kannst du eine Bedinnung mitgeben. In dem Fall, dass der Nachname nicht leer sein darf
In deinem Beispiel
Visual Basic:
'Zellenformel für alle Eindeutigen
=COUNT_DISTINCT($C$1:$E$13; "not [Nachname] is null")
'Nur die Frauen
=COUNT_DISTINCT($C$1:$E$13; "not [Nachname] is null AND [Anrede] = 'Frau'")

Hier die eigentliche Methode. Im VBA-Editor ein Neues Modul erstellen und die Methode hineinkopieren. Nicht in die Objektmodule hinter den Tabellen schreiben!
Visual Basic:
'/**
' * Zählt eindeutige Datensätze über mehrere Spalten
' * @example    =COUNT_DISTINCT($C$1:$E$13; "not [Nachname] is null")
' * @param      Range, der auf Eindeutigkeit geprüft wird
' * @param      Bedinung
' * @return     Anzahl eindeutiger Datensätze oder Fehler
' */
Public Function COUNT_DISTINCT(ByRef iRange As Range, Optional ByVal iWhere As Variant) As Variant
On Error GoTo Err_Handler

    Dim pConn As Object:    Set pConn = CreateObject("ADODB.Connection")
    Dim rsT As Object:      Set rsT = CreateObject("ADODB.Recordset")
    Dim cmd As Object:      Set cmd = CreateObject("ADODB.Command")
    Dim sql As String
  
    'SQL Statement zusammensetzen
    'SELECT DISTINCT Nummer, Anrede, Nachname, Vorname from [Tabelle1$B1:E9]
    sql = "SELECT DISTINCT * from [" & iRange.Worksheet.Name & "$" & iRange.Address(False, False) & "]"
    If Not IsNull(iWhere) Then sql = sql & " WHERE " & iWhere
  
    'Connection
    'https://www.connectionstrings.com/ace-oledb-12-0/xlsx-files/
    pConn.connectionString = "Provider='Microsoft.ACE.OLEDB.12.0'; Data Source='" & ThisWorkbook.FullName & "'; Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'"
    pConn.Open

    'Command
    Set cmd.ActiveConnection = pConn
    cmd.CommandType = 1         'adCmdText
    cmd.CommandText = sql

    'Recordset
    rsT.CursorLocation = 3      'adUseClient
    rsT.CursorType = 3          'adOpenStatic
    rsT.LockType = 1            'adLockReadOnly

    'open the recordset
    rsT.Open cmd
  
    COUNT_DISTINCT = rsT.RecordCount

Exit_Handler:
On Error Resume Next
    'cleanup
    'disconnect the recordset
    Set rsT.ActiveConnection = Nothing
    pConn.Close

    Exit Function

Err_Handler:
    COUNT_DISTINCT = "#ERROR: [" & Err.Number & "] " & Err.Description
    Debug.Print "[" & Err.Number & "] " & Err.Description
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical
    Resume Exit_Handler
    Resume
End Function
 

josef24

Erfahrenes Mitglied
#5
Hallo Yaslaw, erst mal vielen Dank für deine Antwort. Hatte nicht erwartet, das die Lösung einen solchen Aufwand nötig machte. Dafür nochmals besonderen Dank.
Werde in den nächsten Tagen den Code einmal einbauen. Gruß Josef
 

Zvoni

Erfahrenes Mitglied
#6
Anzahl Personen:

=SUMME(N(HÄUFIGKEIT(ZEILE(2:LetzteZeile);TEILERGEBNIS(3;INDIREKT("D"&ZEILE(2:LetzteZeile)))*VERGLEICH(D2:DLetzteZeile&"";D2:DLetzteZeile&"";))>0))
 

Zvoni

Erfahrenes Mitglied
#7
Grmpf
Der grüne Smiley ist ein : D (Doppelpunkt D)
der gelbe ist ein ; ) (Semicolon Klammer zu)
 

josef24

Erfahrenes Mitglied
#8
Danke für die weitere Unterstützung. Habe mal versucht den Code in der Tabelle zu aktivieren. Leider wird kein Ergebnis angezeigt. Er zeigt keinerlei Reaktion.
Habe den Code mal hierhin kopiert, um vielleicht das Problem besser zu erkennen.

Code:
SUMME(N(HÄUFIGKEIT(ZEILE(2:LetzteZeile);TEILERGEBNIS(3;INDIREKT("D"&ZEILE(2:LetzteZeile)))*VERGLEICH(D2:&"";D2:&"";)>0))
Sorry, würde den Code hier natürlich favorisieren wollen, wäre ja wesentlich einfacher. Schönen Feiertag und Gruß Josef
 

josef24

Erfahrenes Mitglied
#9
Habe es mit Unterstützung so erreicht:
Code:
Anzahl Personen gesamt: =SUMMENPRODUKT(1/ZÄHLENWENN($A$2:$A$9;$A$2:$A$9))
Anzahl Frauen: =SUMMENPRODUKT(($B$2:$B$9="Frau")/ZÄHLENWENN($A$2:$A$9;$A$2:$A$9))
Anzahl Herren: =SUMMENPRODUKT(($B$2:$B$9="Herr")/ZÄHLENWENN($A$2:$A$9;$A$2:$A$9))
 

Zvoni

Erfahrenes Mitglied
#10
Danke für die weitere Unterstützung. Habe mal versucht den Code in der Tabelle zu aktivieren. Leider wird kein Ergebnis angezeigt. Er zeigt keinerlei Reaktion.
Habe den Code mal hierhin kopiert, um vielleicht das Problem besser zu erkennen.

Code:
SUMME(N(HÄUFIGKEIT(ZEILE(2:LetzteZeile);TEILERGEBNIS(3;INDIREKT("D"&ZEILE(2:LetzteZeile)))*VERGLEICH(D2:&"";D2:&"";)>0))
Sorry, würde den Code hier natürlich favorisieren wollen, wäre ja wesentlich einfacher. Schönen Feiertag und Gruß Josef
Du musst auch dass fett geschrieben "LetzteZeile" mit der Zeilennummer ersetzen!
 

Zvoni

Erfahrenes Mitglied
#12
Anbei. Ich hatte noch nen Fehler drin
 

Anhänge

josef24

Erfahrenes Mitglied
#14
Möchte nochmal auf die Formel mit folgender Frage zurück kommen: Kann man mit dieser Formel auch gezielt in Spalten suchen. Ich meine hiermit nach dem Begriff Herr oder Frau. Man wäre damit im Zeilenumfang dann nicht so eingeengt. Danke und Gruß Josef