Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1888to1892
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Prüfung auf unterscheidliche Werte

Prüfung auf unterscheidliche Werte
15.07.2022 12:36:28
Jens
Hallo Ihr!
Ich benötige Zwecks Umsetzung eines Projektes und Nachvollziehbarkeit für mich wieder eimal eure Hilfe und wäre euch sehr dankbar mir dabei zu helfen!
hier als Beispiel:
In einer Tabelle/Spalte A wird im fiktiven Beispiel die PLZ und der Ort zusammen als Text angegeben. Die Angaben können sich wiederholen. Ich möchte später mit der SummeWenns-Funktion dies abgreifen und in unterschiedlichen Diagrammen darstellen. Daher benötige ich die ersten 5Zeichen der unterschiedlichen Angaben in einer seperaten Spalte mit der ich weiterarbeiten kann.
Also ab A2 bis A6 steht folgendes drinnen:
12345_Musterdorf
23456_Dorfhausen
21332_Stadt
21332_Stadt
23456_Dorfhausen
in der Spalte ab B2 möchte ich die unterscheidlichen PLZ´s (ersten 5 Zeichen) aufgezählt haben die es in der Spalte A2-A6 gibt (ohne Doppeltnennung)
12345
21332
23456
zudem soll die Liste in Spalte B sich dann anpassen wenn eine neue Angabe mit neuer PLZ dazu kommt.
Ich kann jetzt leider keine Beispieldatei erstellen, da ich im Zug gerade ohne Notebook unterwegs bin und hoffe, dass Ihr anhand meines Textes die Problematik versteht.
Ich hatte auch vor kurzem ein YT-Video gesehen wo jemand die Namen-Varianten so "abgreift" - finde es aber nicht mehr.
Danke jedenfalls schon im vorab für eure Mithilfe!
Hinweis: Bitte kein VBA.
Bye Jens

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Ergebnis listen ist mit ner Formel möglich ...
15.07.2022 13:07:03
neopa
Hallo Jens,
... und zwar z.B. mit folgender Formel =WENNFEHLER(AGGREGAT(15;6;LINKS(A$2:A$9;5)/(ZÄHLENWENN(B$1:B1;LINKS(A$2:A$9;5))=0);1);"")
und diese nach unten kopieren.
Alternativ ohne VBA ohne und in "einem Aufwasch" einfach mit der in Deiner XL-Version vorhandenen Power Query (PQ) Funktionalität. Bei Interesse, dies hier einfach kundtun.
Gruß Werner
.. , - ...
Anzeige
AW: Ergebnis listen ist mit ner Formel möglich ...
15.07.2022 19:22:03
Jens
@Werner
Eben deine Lösung testweise ausprobiert.
Funktioniert Super.
Bei meinem Projekt sind ungefährt 400Zeilen mit 5 unterschiedlichen ersten 5 Zeichen vorhanden (Datenbank) wo sich davon immer wieder im Jahr was ändern kann und das fange ich mit deiner Lösung wunderbar ab.
Danke Dir und allen anderen von euch!
AW: Ergebnis listen ist mit ner Formel möglich ...
15.07.2022 19:45:43
Jens
@Werner
Wie kann ich das mit deiner Formel machen, wenn sich in der ersten 5 Zeichen Buchstaben befinden. Mit Zahlen funktionier das wunderbar. Aber bei einem möglichen Zahlen/Buchstabenmix nicht mehr.
zB
D1234
A2345
I1234
Bye Jens
AW: Ergebnis listen ist mit ner Formel möglich ...
15.07.2022 20:05:40
Jens
@Werner
jetzt habe ich zu früh den Beitrag geschlossen und ich hoffe du siehst noch meine "letzte" Problematik die jetzt aufgetaucht ist:
Wie kann ich das mit deiner Formel machen, wenn sich in der ersten 5 Zeichen Buchstaben befinden. Mit Zahlen funktionier das wunderbar. Aber bei einem möglichen Zahlen/Buchstabenmix nicht mehr.
zB
D1234
A2345
I1234
Bye Jens

Gibt es hier eine PN- Möglichkeit ?
Anzeige
AW: was meinst Du mit PN? Weiter siehe unten owT
15.07.2022 20:09:51
neopa
Gruß Werner
.. , - ...
AW: richtig, die aufgezeigte Formellösung ...
15.07.2022 20:07:11
neopa
Hallo Jens,
... setzt voraus, dass es 5 stellige Zahlen sind, wie in Deinem Eingangsbeispiel aufgezeigt.
Sobald jedoch nur ein alphanumerisches Zeichen in diesem Bereich vorhanden sein kann, muss die Formel entsprechend geändert werden. Bevor das "wie" aufgezeigt werden kann, ist zunächst die Fragezu klären, ob es denn immer genau 5 Zeichen sind? Wenn es teils mehr oder weniger sein sollte, ist dann noch die Frage offen, ob als Trennzeichen zu den anderen Textteil stets das "_" vorhanden ist?
Weiter nach Deiner Antwort, dann meinerseits aber erst morgen.
Gruß Werner
.. , - ...
Anzeige
AW: richtig, die aufgezeigte Formellösung ...
15.07.2022 20:27:47
Jens
@Werner
Konkrette Antwort:
Zum Syntax:
Das mit den 5 Zahlen wird umgestellt seitens der Datenbank auf Anfangs 1Buchstabe und 4 Zeichen auf später sogar 2 Buchstaben und 3 Zahlen
Bespiele:
I1234_zuszuätzliche_variable_Angaben
W2345_zuszuätzliche_variable_Angaben
X1255_zuszuätzliche_variable_Angaben
Später im Laufe des Jahres
AW123_zuszuätzliche_variable_Angaben
AQ123_zuszuätzliche_variable_Angaben
TS122_zuszuätzliche_variable_Angaben
Die ersten 5 Zeichen (Buchstaben/Zahlen) wiederholen sich mehrfach in den Zeilen und damit will ich mit WennSummeS arbeiten
Dies wiederholt sich bis auf 400Zeilen. Deine Formel mit funktioniert mit Zahlen sehr gut (Danke!), aber halt nicht mit dem Mix
Wenn deine Lösung nur mit 1Buchstaben+4Zahlen oder mit 2Buchstaben+3Zahlen gehen solte.lass die 1+4 aus und lege dein Augenmerk auf 2+3. dann dies wäre dann für mich gleich besser einpflegbar.
DANKE jedenfalls für deine Mühe!!!
mit PN meinte private Nachricht - hat sich aber somit erledigt
Bye Jens und einen schönen Abend
Anzeige
AW: dann zusätzlich noch ZEILE() und INDEX() ...
16.07.2022 08:09:23
neopa
Hallo Jens,
... dafür folgende Formel in B2:

=WENNFEHLER(LINKS(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(ZÄHLENWENN(B$1:B1;LINKS(A$2:A$99;5)&"*") =0);1));5);"")

und dies nach unten kopieren.
Und nein, hier im Forum gibt es keine Möglichkeit für PN wie dies in einigen anderen Foren der Fall ist.
Gruß Werner
.. , - ...
AW: dann zusätzlich noch ZEILE() und INDEX() ...
16.07.2022 10:36:52
Jens
@Werner
Ein sehr großes Dankeschön!!!
Das funktioniert jetzt wunderbar mit allen Varianten.
Falls du magst und kannst: mich würde interessieren (Nachvollziehbarkeit), was genau die Formel macht an welcher Stelle.
Ich will nicht nur einfach blind "reinkopieren" sondern auch das Verständnis und die Logik verstehen.
Danke dir jedenfalls und ein schönes Wochenende.
Bye Jens
Anzeige
AW: im Prinzip wie folgt ...
16.07.2022 12:02:43
neopa
Hallo Jens,
... mit dem Formelteil AGGREGAT(15;6;ZEILE(A$2:A$99)/(ZÄHLENWENN(B$1:B1;LINKS(A$2:A$99;5)&"*")=0);1) wird stets die kleinste Zeilennummer gesucht. Und zwar für die in der Spalte B vor der jeweiligen Teilergebniszelle dort stehenden, bzw. mit der Gesamt-Formel ermittelten Werten, in den die ersten 5 Zeichen der Datenwerte der Spalte A noch nicht gelistet ist/sind.
Dabei hilft im Wesentlichen der Formelteil: ZÄHLENWENN(B$1:B1;LINKS(A$2:A$99;5)&"*"), der ja eine Teilergebnismatrix ausgibt. In dieser wird die erste 0 gesucht (durch =0). Dessen Position wird durch den Formelteil ZEILE() ausgewertet und diese der AGGREGAT()-Funktion übergeben. Dies wegen der 15 als dessen 1. Argument als die jeweils kleinste Zeilennummer. Diese Nummer wird als Argument der Funktion INDEX() übergeben, welches dann dafür den entsprechenden Wert aus der Spalte A der Funktion LINKS() übergibt. Das zweite Argument der AGGREGAT()-Funktion, die 6 ist dafür zuständig, dass die Funktion auch dann weiter korrekt eine vorhandene 0 in der Teilematrix suchten kann, wenn die Bedingungsgleichung (ZÄHLENWENN(B$1:B1;LINKS(A$2:A$99;5)&"*")=0) nicht erfüllt ist. Denn durch die mathematische Operation wird aus dem Teilergebnis FALSCH eine 0 und an dieser stelle ergäbe sich in dieser Teilergebnismatrix ja eine Divisionsfehler 1/0. Die 6 sorgt dafür, dass AGGREGAT() diese Teilfehler einfach ignoriert.
Wenn der ZÄHLENWENN()-Formelteil jedoch keine 0 mehr ermitteln kann, ergibt AGGREGAT() natürlich einen Fehlerwert, der dann aber durch WENNFEHLER() abgefangen wird.
Gruß Werner
.. , - ...
Anzeige
AW: im Prinzip wie folgt ...
18.07.2022 13:24:28
Jens
@ Werner Danke dir für deine Erklärung der einzelnen Schritte der Formel!
Kurzefrage wenn ich die Formel + Auswertung auf einem anderen Tabellenblatt (Hilfstabelle) durchführenlassen will muss ich doch nur Überall vor den A-Bereichen den Tabellennamen eintragen oder?
Dein Code:
=WENNFEHLER(LINKS(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/(ZÄHLENWENN(B$1:B1;LINKS(A$2:A$99;5)&"*") =0);1));5);"")
Angepasster Code zum anderen Tabellenblatt (Datenbank):
=WENNFEHLER(LINKS(INDEX(Datenbank!A:A;AGGREGAT(15;6;ZEILE(Datenbank!A$2:A$99)/(ZÄHLENWENN(B$1:B1; LINKS(Datenbank!A$2:A$99;5)&"*") =0);1));5);"")
Leider wird mir dann nichts angezeigt. Wo habe ich da wieder den Denkfehler?
Bye Jens
Anzeige
AW: im Prinzip wie folgt ...
18.07.2022 13:42:36
Jens
erledigt mein Fehler - gefunden..... ;)
AW: bitteschön owT
18.07.2022 13:58:04
neopa
Gruß Werner
.. , - ...
AW: bitteschön, gerne owT
15.07.2022 19:47:22
neopa
Gruß Werner
.. , - ...
AW: Prüfung auf unterscheidliche Werte
15.07.2022 13:09:31
Bernhard
Hallo Jens,
ich würde so vorgehen:
Tabelle in Spalte A mit Strg+T in eine Intelligente Tabelle umwandeln.
Das stellt sicher, dass wenn Daten in A hinzukommen sich B mit aktualisiert.
Dan in B die Formal =Links(A1;5)
Ist einfach aber denke es sollte funktionieren.
Gruß
Bernhard

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige