Supportnet / Forum / Tabellenkalkulation
Duplikate dynamisch filtern
Frage
Hallo zusammen,
ich habe eine dreispaltige Excel-Liste, die Firmennamen, Rechnungsnummer und Rechnungsbetrag enthält. Die Einträge in dieser Liste werden täglich erweitert, so dass die Liste am Ende des Jahres etwa 600 Einträge enthält
Nun möchte ich auf einem zweiten Tabellenblatt eine dynamische Liste aller Firmen aus der oben beschriebenen Liste generieren. Dabei soll jeder Firmenname natürlich nur einmalig vorkommen.
Daten / Filter / Spezialfilter / Duplikate entfernen liefert schon das gewünschte Ergebnis. Allerdings ohne den gewünschten Automatismus.
Wer hat eine Idee?
Danke - Tango1000
Antwort 1 von Saarbauer
Hallo,
folgende lösung wird von mir vorgeschlagen:
im Tabelle1 eine neue Spalte als Hilfsspalte einrichten, vor Firmennamen, dort in Zelle A2 die Formel
=WENN(ZÄHLENWENN($A$2:A2;A2)=1;MAX($E$1:E1)+1;0)
eingeben.
In Tabelle 2 kommt ab A2
=WENN(ISTNV(SVERWEIS(ZEILE()-1;Tabelle1!$A:$B;2;0));"";SVERWEIS(ZEILE()-1;Tabelle1!$A:$B;2;0))
Wobei ich in Zeile 1 beider Tabellen die Überschriften voraussetze und in Zelle A1 0 steht.
Fals erforderlich kann man in Tabelle 1 die Hilfsspalte ausblenden.
Gruß
Helmut
folgende lösung wird von mir vorgeschlagen:
im Tabelle1 eine neue Spalte als Hilfsspalte einrichten, vor Firmennamen, dort in Zelle A2 die Formel
=WENN(ZÄHLENWENN($A$2:A2;A2)=1;MAX($E$1:E1)+1;0)
eingeben.
In Tabelle 2 kommt ab A2
=WENN(ISTNV(SVERWEIS(ZEILE()-1;Tabelle1!$A:$B;2;0));"";SVERWEIS(ZEILE()-1;Tabelle1!$A:$B;2;0))
Wobei ich in Zeile 1 beider Tabellen die Überschriften voraussetze und in Zelle A1 0 steht.
Fals erforderlich kann man in Tabelle 1 die Hilfsspalte ausblenden.
Gruß
Helmut
Antwort 2 von Saarbauer
Hallo,
vergessen, die beiden Formeln sind natürlich in den Spalten soweit wie erforderlich nach unten zu ziehen, etwas mehr macht auch nichts.
Gruß
Helmut
vergessen, die beiden Formeln sind natürlich in den Spalten soweit wie erforderlich nach unten zu ziehen, etwas mehr macht auch nichts.
Gruß
Helmut
Antwort 3 von rainberg
Hallo Tango 1000,
mit dem Spezialfilter liegst du doch schon richtig.
Zeichne dir einfach die Filterprozedur mit dem Makrorecorder auf und weise diesen Code einem Button zu.
Gruß
Rainer
mit dem Spezialfilter liegst du doch schon richtig.
Zeichne dir einfach die Filterprozedur mit dem Makrorecorder auf und weise diesen Code einem Button zu.
Gruß
Rainer
Antwort 4 von Tango1000
Hallo Helmut,
vielen Dank für deine guten und superschnellen Hinweise. Habe damit die gewünschte Lösung gefunden:
Zeile 1
Spalte A (Hilfsspalte)
Spalte B (Name) - soll wie beschrieben gefiltert werden
Spalte C (Rechnungsnummer)
Spalte D (Rechnungsbetrag)
Nun habe ich in die Zelle A2 die Formel =WENN(ZÄHLENWENN($B$2:B3;B3)=1;MAX($A$1:A2)+1;0) eingegeben und herunterkopiert.
[Sie bewirkt, dass vor jedem Namen eine eindeutige Ziffer steht. Wenn der Name zum ersten Mal vorkommt, steht dort eine 1, 2, 3, ... n. Wenn der Name bereits schon mal vorgekommen ist, dann steht dort eine 0.]
Im Tabellenblatt 2 habe ich deinen Formelvorschlag so übernommen.
Nochmals vielen Dank - André.
vielen Dank für deine guten und superschnellen Hinweise. Habe damit die gewünschte Lösung gefunden:
Zeile 1
Spalte A (Hilfsspalte)
Spalte B (Name) - soll wie beschrieben gefiltert werden
Spalte C (Rechnungsnummer)
Spalte D (Rechnungsbetrag)
Nun habe ich in die Zelle A2 die Formel =WENN(ZÄHLENWENN($B$2:B3;B3)=1;MAX($A$1:A2)+1;0) eingegeben und herunterkopiert.
[Sie bewirkt, dass vor jedem Namen eine eindeutige Ziffer steht. Wenn der Name zum ersten Mal vorkommt, steht dort eine 1, 2, 3, ... n. Wenn der Name bereits schon mal vorgekommen ist, dann steht dort eine 0.]
Im Tabellenblatt 2 habe ich deinen Formelvorschlag so übernommen.
Nochmals vielen Dank - André.
Antwort 5 von Tango1000
Hallo Rainer,
auch dir vielen Dank für deine spontane Reaktion. Dein Hinweis mit dem Makrorekorder ist gut. Allerdings halte ich die Lösung für nur "halbdynamisch". Besser komme ich mit der oben beschriebenen Lösung klar.
Grüße - André.
auch dir vielen Dank für deine spontane Reaktion. Dein Hinweis mit dem Makrorekorder ist gut. Allerdings halte ich die Lösung für nur "halbdynamisch". Besser komme ich mit der oben beschriebenen Lösung klar.
Grüße - André.
Antwort 6 von Saarbauer
Hallo André,
es ist richtig wie du die funktion der Hilfsspalte beschrieben hast.
Da in der Funktion ZÄHLENWENN($B$2:B15;B15)
der erste Wert $B$2 statisch und der zweite Wert B15 dynamisch ist wird immer bis zu der Zeile ( hier 15) in der Formel gezählt. Da nicht vorher bekannt ist welcher Wert in Spalte A zu dem Zeitpunkt der grösste ist wird dies über Max($A$1:A2) ermittelt.
Hast ja meinen Tippfehler bemerkt.
Falls du die Rechnungsummen der Firmen noch dazu schreibem möchtest dann in Zelle B2
=SUMMEWENN(Tabelle1!B1:B5000;A2;Tabelle1!C1:C5000)
B5000 und c5000 kann entsprechend angepasst werden
Gruß
Helmut
es ist richtig wie du die funktion der Hilfsspalte beschrieben hast.
Da in der Funktion ZÄHLENWENN($B$2:B15;B15)
der erste Wert $B$2 statisch und der zweite Wert B15 dynamisch ist wird immer bis zu der Zeile ( hier 15) in der Formel gezählt. Da nicht vorher bekannt ist welcher Wert in Spalte A zu dem Zeitpunkt der grösste ist wird dies über Max($A$1:A2) ermittelt.
Hast ja meinen Tippfehler bemerkt.
Falls du die Rechnungsummen der Firmen noch dazu schreibem möchtest dann in Zelle B2
=SUMMEWENN(Tabelle1!B1:B5000;A2;Tabelle1!C1:C5000)
B5000 und c5000 kann entsprechend angepasst werden
Gruß
Helmut
Antwort 7 von rainberg
Hallo Andre,
Diese Meinung kann ich natürlich nicht teilen, wenn du den Filter richtig einstellst, ist er mindestens so dynamisch wie die Formellösung.
Gruß
Rainer
Zitat:
Allerdings halte ich die Lösung für nur "halbdynamisch".
Allerdings halte ich die Lösung für nur "halbdynamisch".
Diese Meinung kann ich natürlich nicht teilen, wenn du den Filter richtig einstellst, ist er mindestens so dynamisch wie die Formellösung.
Gruß
Rainer
Antwort 8 von Klamsy
Also wenn ich das genau so mache wie es hier beschrieben ist, kommt eine Meldung, dass ich auf werte des Ergebnisses zugreife (Zirkel)
Was mache ich falsch?..
Was mache ich falsch?..
Antwort 9 von Tango1000
Hallo Klamsy,
danke für deinen Hinweis. Da hat sich tatächlich ein Fehler eingeschlichen.
Hier noch einmal die Lösung im Ganzen:
Zeile 1
Spalte A (Filter) - Hilfsspalte kann ausgeblendet werden
Spalte B (Name) - soll wie beschrieben gefiltert werden
Spalte C (Rechnungsnummer)
Spalte D (Rechnungsbetrag)
Spalte E leer
Spalte F (Sortierung) - Hilfsspalte kann ausgeblendet werden
Spalte G (Namensliste) - "Jeder Name nur einmal"
Nun habe ich in die Zelle A2 die Formel =WENN(ZÄHLENWENN($B$2:B2;B2)=1;MAX($A$1:A1)+1;0) eingegeben und herunterkopiert.
[Sie bewirkt, dass vor jedem Namen eine eindeutige Ziffer steht. Wenn der Name zum ersten Mal vorkommt, steht dort eine 1, 2, 3, ... n. Wenn der Name bereits schon mal vorgekommen ist, dann steht dort eine 0.]
In Spalte F habe nun in die Zelle F2 eine ´1´, in F3 eine ´2´ usw. bis in F65536 eine ´65535´ eingegeben. Hier ist es wichtig mit Werten statt der Formel zeile()-1 zu arbeiten, weil sonst die Ergebnissliste nicht sortiert werden kann.
In die Zelle G1 habe ich nun noch die Formel =WENN(ISTNV(SVERWEIS(F2;$A:$B;2;0));"";SVERWEIS(F2;$A:$B;2;0)) eingegeben und bis zum Blattende herunterkopiert.
So wird diese Liste automatisch länger, sobald in der Quell-Liste (Spalten A bis D) ein neuer Name hinzukommt.
Grüße - André.
danke für deinen Hinweis. Da hat sich tatächlich ein Fehler eingeschlichen.
Hier noch einmal die Lösung im Ganzen:
Zeile 1
Spalte A (Filter) - Hilfsspalte kann ausgeblendet werden
Spalte B (Name) - soll wie beschrieben gefiltert werden
Spalte C (Rechnungsnummer)
Spalte D (Rechnungsbetrag)
Spalte E leer
Spalte F (Sortierung) - Hilfsspalte kann ausgeblendet werden
Spalte G (Namensliste) - "Jeder Name nur einmal"
Nun habe ich in die Zelle A2 die Formel =WENN(ZÄHLENWENN($B$2:B2;B2)=1;MAX($A$1:A1)+1;0) eingegeben und herunterkopiert.
[Sie bewirkt, dass vor jedem Namen eine eindeutige Ziffer steht. Wenn der Name zum ersten Mal vorkommt, steht dort eine 1, 2, 3, ... n. Wenn der Name bereits schon mal vorgekommen ist, dann steht dort eine 0.]
In Spalte F habe nun in die Zelle F2 eine ´1´, in F3 eine ´2´ usw. bis in F65536 eine ´65535´ eingegeben. Hier ist es wichtig mit Werten statt der Formel zeile()-1 zu arbeiten, weil sonst die Ergebnissliste nicht sortiert werden kann.
In die Zelle G1 habe ich nun noch die Formel =WENN(ISTNV(SVERWEIS(F2;$A:$B;2;0));"";SVERWEIS(F2;$A:$B;2;0)) eingegeben und bis zum Blattende herunterkopiert.
So wird diese Liste automatisch länger, sobald in der Quell-Liste (Spalten A bis D) ein neuer Name hinzukommt.
Grüße - André.