Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1576to1580
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1576to1580
1576to1580
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Unikate Kombinationen aus 6 Spalten ermitteln

Unikate Kombinationen aus 6 Spalten ermitteln
23.08.2017 17:11:10
Moe
Hallo zusammen
Ich möchte gerne eine Materialliste automatisch erstellen, wobei ich mit meinen Excel-Kompetenzen an meine Grenzen stosse...
Hier eine reduzierte Beispieldatei (mit zwei Registerblätter);
https://www.herber.de/bbs/user/115678.xlsx
Aus der Datentabelle muss ich pro Material (Spalte: Beschreibung) eine Liste generieren, welche alle Kombinationen aus 6 Spalten ("DN", "DN1", "DN2", "DN3", "Insulation Thickness", "Isolationsmaterial") untereinander auflistet. Im Registerblatt "Auswertung" habe ich manuell eine Beispielauflistung eingefügt, welche es zu automatisieren gibt...
Hat jemand eine Idee, wie ich das am besten aufbaue?
Ohne VBA wäre mir lieber und weil die Tabelle relativ gross ist und es schlussendlich viele Materialen und Zeilen sind, wäre es gut, wenn die Funktionen einigermassen schnell laufen...
Danke für die Hinweise und Gruss,
Moe

36
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt ...
23.08.2017 17:16:43
...
Hallo Moe,
... wie viele Datensätze hat Deine Datentabelle ca./max?
Gruß Werner
.. , - ...
AW: nachgefragt ...
23.08.2017 17:25:50
Moe
Hoi Werner
Es sind ca. 5'000 Zeilen mit insgesamt 20-40 Spalten (aber immer "nur" 6 Spalten als Kriterien für die Liste), also rund 200'000 Datensätze. Etwas mehr Hilszeilen, dafür läuft es schneller und für mich "einacher" zum nachvollziehen, wäre mir fast lieber - selbstverständlich nur wenn möglich...
Bin auf die Löstungsvorschläge gespannt!
Gruss,
Moe
AW: hätte zwar eine Lösungsformel ...
23.08.2017 18:44:24
...
Hallo Moe,
... aber für eine derartige Anzahl von Auswertung gibt es sicherlich schneller Lösungswege.
Aber um solche aufzuspüren fehlt es mir an Daten und Muse.
Meine Formel für B24 (mit benutzerdefinierten Zahlenformat: 0;; )
=WENNFEHLER(INDEX(Datentabelle!$A:$W;AGGREGAT(15;6;ZEILE(B$2:B$5000)
/(Datentabelle!$A$2:$A$5000=VERWEIS(9;1/($A$22:B$22"");$22:$22))/(INDEX(Datentabelle!$A$2:$W$5000;;
VERGLEICH(B$23;Datentabelle!$1:$1;0))"");ZEILE(A1));VERGLEICH(B$23;Datentabelle!$1:$1;0));"")
Diese kannst Du nach rechts bis Spalte G und weit genug nach unten kopieren und anschließenden den gesamten Block nach J24:O##
Gruß Werner
.. , - ...
Anzeige
AW: hätte zwar eine Lösungsformel ...
24.08.2017 09:48:52
Moe
Hallo Werner
Diese Formel ist der Wahnsinn! Hab sie leider noch nicht ganz verstanden aber funktioniert im Testfile perfekt.
Ich habe sie nun im kompletten File integrieren wollen (Geschwindigkeit ist bis jetzt kein Problem!) und da stimmt sie nur bei der ersten Auswertung (Tabelle "Rohrbogen". Ich habe bis jetzt noch nicht herausgefunden, weshalb bei der zweiten Tabelle "Stahlrohr" plötzlich Duplikare vorkommen... Hier eine Beispieldatei mit dem Problem: https://www.herber.de/bbs/user/115696.xlsx
Erkennt jemand den Fehler?
Als nächster Schritt möchte ich jede Tabelle noch sortieren, dies vermutlich am besten in einem neuen Registerblatt. Die Priorität wäre in der Reihenfolge mit den Spalten und von klein nach gross... Ist die ebenfalls ohne VBA möglich?
Danke für die Unterstützung! Ihr seid meine Rettung! ;)
Gruss,
Moe
Anzeige
AW: nachgefragt ...
24.08.2017 10:24:21
...
Hallo Moe,
... in Deiner gestrigen Beispieldatei waren keine Duplikate und genau genommen sind in Deiner heutigen Datei auch kaum welche, denn im Wert MEP.Geom-Length (mm) der Datentabelle unterscheiden sie sich.
Dies willst Du aber unberücksichtigt lassen? So dass in Deiner Auswertung für Stahlrohr nach DIN 2448 nur noch die jetzigen Daten dieser Auswertungstabelle in Zeile 24, 30, 50 und 58 gelistet werden? Oder?
Könnte ich in die Formel noch einarbeiten. Diese verlängert sich natürlich und drückt damit deren Performance weiter.
Gruß Werner
.. , - ...
AW: nachgefragt ...
24.08.2017 10:48:45
Moe
Hoi Werner
Ja genau, die Spalte MEP.Geom-Length (mm) soll für die Auslistung nicht berücksichtigt werden, also alle aufgeführten Kombinationen in der Auswertungstabelle sollten nur einmal vorkommen. Die totale Anzahl bzw. die Länge kann ich dann z.B. über SUMMEWENNS usw. selber lösen.
Im kompletten File hat die Datentabelle etwas mehr Spalten und die Spalte "Beschreibung" beginnt erst in Spalte G und nicht A. Auch die Auswertung beginnt etwas weiter rechts...
Hier die reduzierte Beispieldatei: https://www.herber.de/bbs/user/115701.xlsx
Falls es für dich möglich ist, dies auch gerade anzupassen, wäre es genial.
Vielen Dank!
Gruss,
Moe
Anzeige
AW: auch wenn erste Daten in Spalte G liegen ...
24.08.2017 11:28:55
...
Hallo Moe,
... ist die Formel so konstruiert, dass sie weiterhin von Spalte A beginnend zählt. Könnte ich zwar umstellen aber ist so mE einfacher nachvollziehbar. Insofern war Deine vorgenommene Anpassung inkorrekt.
Wie geschrieben kann ich die Duplikate durch Formelerweiterung "ausfiltern". Doch die Formel würde dann unübersichtlich groß und entsprechend sehr langsam. Deshalb würde ich dafür mit einer Hilfsspalte je Art (Beschreibung) arbeiten. Sind im Original in der "Auswertung" die Spalte F, N ... usw. frei? Und in der "Datentabelle" die Spalte AD?
Gruß Werner
.. , - ...
Anzeige
AW: auch wenn erste Daten in Spalte G liegen ...
24.08.2017 11:57:57
Moe
Hoi Werner
Zuerst danke für Deine Bemühungen!
Für mich ist es ok, wenn die Formel von Spalte A beginnend zählt, solange sie alle Spalten mit den Kriterien korrekt berücksichtigt ;)
Hilfsspalten dürfen gerne eingesetzt werden, weil die Formel schon so für mich genug kompliziert ist! Nein, die Spalten F,N, usw. sind im Register "Auswertung" mit den Berechnungen belegt. Man könnte auch ein neues Registerblatt einfügen... Die Spalte AD ist in der Datentabelle ebenfalls belegt, aber ab der Spalte AH wäre es frei.
Gruss,
Moe
AW: zu den Hilfsspalten ...
24.08.2017 12:33:58
...
Hallo Moe,
... diese in Spalte AH in der Datentabelle ist ok. Wenn jedoch die Spalten F, N ... im Tabellenblatt Auswertung von Dir belegt sind, solltest Du Dir überlegen, ob die Auswertungslisten dann erst ab Spalte H beginnt und damit Spalte G für die Hilfsspalte frei ist und dann eben die weiteren Blöcke immer weiter nach rechts verschoben werden und zwar so, dass vor jedem Block eine Hilfsspalte frei ist. Die Hilfsspalten können natürlich später ausgeblendet werden.
Wenn dem dann so ist, lasse es mich wissen.
Gruß Werner
.. , - ...
Anzeige
AW: zu den Hilfsspalten ...
24.08.2017 13:03:59
Moe
Hoi Werner
Das ist für mich i.O., werde die Tabellen ensprechend deinen Anforderungen versetzen.
Danke und Gruss,
Moe
AW: nun, dann wie folgt ...
24.08.2017 15:47:29
...
Hallo Moe,
... in Deiner Datentabelle_bearbeitet!AH2: =I2&"#"&K2&"#"&M2&"#"&O2&"#"&W2&"#"&Y2
Formel bis zum Datensatzende herunter kopieren bzw. diese gleich als "intelligente" Tabelle formatieren (anstelle des der "#" kannst Du auch ein anderes Zeichen verwenden, welches nicht in Deinen Datenwerten vorkommt!; natürlich dann auch in der nachfolgenden Formel).
In Auswertung!G24: =H24&"#"&I24&"#"&J24&"#"&K24&"#"&L24&"#"&M24 und soweit erforderlich nach unten kopieren.
In Auswertung!H24:
=WENNFEHLER(INDEX(Datentabelle_bearbeitet!$A:$AC;AGGREGAT(15;6;ZEILE(H$2:H$5000)
/(Datentabelle_bearbeitet!$G$2:$G$2040=VERWEIS(9;1/($A$22:H$22"");$22:$22))
/(INDEX(Datentabelle_bearbeitet!$A$2:$AC2040;;VERGLEICH(H$23;Datentabelle_bearbeitet!$1:$1;0))"")
/(ZÄHLENWENN(INDEX($A:G;23;KÜRZEN((SPALTE()+2)/9;0)*9-2)
:INDEX($A:G;ZEILE()-1;KÜRZEN((SPALTE()+2)/9;0)*9-2)
;Datentabelle_bearbeitet!$AH$2:$AH$2040)=0);1);VERGLEICH(H$23;Datentabelle_bearbeitet!$1:$1;0));"")
Formel nach rechts bis Spalte M und weit genug nach unten kopieren.
Dann den gesamten Formelbereich Auswertung!G24:Auswertung!M## (also mit Hilfsspalte) dann nach rechts in P24:V## kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: nun, dann wie folgt ...
24.08.2017 17:15:41
Moe
Hoi Werner
Diese Formel ist nicht mehr menschlich! Wahnsinn, was du alles hinbringst!
Die Auswertung funtkioniert perfekt (die Performance nimmt stark ab aber das ist noch nicht problematisch) mit 500 Zeilen. Bei rund 2'000 Zeilen listet es mir trotzdem wieder Dupklikate auf und ich habe keine Ahnung warum.
Wegen der Filegrösse kann ich nicht die originale Datei senden.
Aber hier die kleinere Datei mit 500 Zeilen welche funktioniert: https://www.herber.de/bbs/user/115723.xlsx
Wenn man die Datentabelle (Zeile 2-499) 4x nach unten kopiert tritt der "Fehler" ebenfalls auf....
Hast du eine Idee, wie man das in den Griff bekommt?
Vielen Dank und Gruss,
Moe
Anzeige
AW: nun, dann wie folgt ...
24.08.2017 17:49:57
Moe
Hoi Werner
Ich schon wieder...
Habe eine Beispieldatei aufbereitet, wo das vorhinbeschriebene "Problem" auftritt:
https://www.herber.de/bbs/user/115726.xlsx
Kannst du dir das erklären?
Gruss,
Moe
AW: die Problemursache lokalisiert ...
24.08.2017 20:33:22
...
Hallo Moe,
... in meiner Formellösungsvorschlag war ich gemäß den bis dahin vorliegenden Daten davon ausgegangen, dass in allen Zellen Datenwerte stehen. Das ist aber nicht mehr gegeben, wie mir Deine neu bereitgestellten Daten zeigen.
Insbesondere nicht in der Spalte Y Der Datentabelle. Nachdem ich das gefunden hatte, glaubte ich das Problem bereits zu lösen, indem ich alle da vorhandenen Leerzellen auch durch 0-Werte ersetze (dazu sieh auch unten). Das Problem wurde damit jedoch nur teilweise beseitigt. Ich war wie vor den Kopf gestoßen. Bis ich dann fast mehr zufällig feststellte, dass trotz meiner 0en-Füllaktion immer noch "leere Zellen" da waren. Diese sind aber keine echten Leerzellen, sondern beinhalteten mal ="". Mehr dazu sieh mal meine heutigen Aussagen hier: https://www.herber.de/forum/archiv/1576to1580/t1576182.htm
Durch eine Aktivierung der Funktion "Text in Spalten" für die Y-Spalte in der Datentabelle können daraus allerdings wieder echte Leerzellen erstellt werden. Diese können dann wiederum nach Markierung der Spalte Y und anschließend betätigen von [F5]; Inhalte; Leerzellen und dann Eingabe =0 und STrG+U auch noch mit 0-Werten gefüllt werden.
Allerdings kam mir jetzt noch eine Idee, wie ich die Datenänderung der Leerzellen und der "leeren Zellen" in Spalte Y vermieden werden könnte und sogar die vorhandenen 0-Werte gelöscht werden und gleichzeitig eine Beschleunigung der Formelauswertung erfolgen könnte. Dies würde ich dann allerdings erst Morgen Vormittag realisieren, so dies Deinerseits dann noch benötigt werden sollte.
Gruß Werner
.. , - ...
Anzeige
AW: die Problemursache lokalisiert ...
25.08.2017 06:55:37
Moe
Hoi Werner
Herzlichen Dank für deinen Einsatz!
Gerne warte ich gespannt auf dein neuen Lösungsvorschlag!
Gruss,
Moe
AW: ein neuer Formellösungsansatz ...
25.08.2017 08:59:47
...
Hallo Moe,
... der nachfolgend aufgeführt ist, bietet mE ein momentanes Optimum an Aufwand, Nachvollziehbarkeit, und Performance der Auswertung.
Dazu habe ich zur bisherigen Hilfsspalte im Datentabellenblatt (hab ich namentlich gekürzt auf "Daten") weitere eingeführt und entsprechend "beformelt".
Die Hilfsspaltenformel in AH2 wurde erweitert zu: =G2&"!"&I2&"#"&K2&"#"&M2&"#"&O2&"#"&W2&"#"&Y2
Dann folgende Formel in AI1: WENNFEHLER(INDEX($G:$G;AGGREGAT(15;6;ZEILE(G2:G5000)/($G2:$G5000"")/(ZÄHLENWENN($AH1:AH1;$G2:$G5000)=0);1));"") und diese Formel weit genug nach rechts kopieren (ermittelt die verschiedenen Arten aus der Spalte G)
Danach in AI2 folgende Formel: 
=WENN(($G2AI$1)+(AI$1="")+(ZÄHLENWENNS($G$2:$G2;AI$1;$AH$2:$AH2;$AH2)>1);"";MAX(AI$1:AI1)+1)
und diese nach rechts und unten kopieren (ermittelt die Zeilenzahl der jeweiligen Art aus Spalte G und zwar jeweils duplikatfrei) .
Im Auswertungstabellenblatt:
In G24 (weit genug nach unten kopieren) 
=WENN((G23="")*(ZEILE()>24);"";
WENNFEHLER(VERGLEICH(ZEILE()-23;INDEX(Daten!$A:$AZ;;VERGLEICH(INDEX(H:H;22);Daten!$1:$1;0));0);""))
In H24 dann nur noch:
=WENN(G24="";"";INDEX(Daten!$A:$Y;INDEX($A:H;;KÜRZEN((SPALTE()+2)/9;0)*9-2);VERGLEICH(H$23;Daten!$1:$1;)))
Diese Formel bis Spalte M und weit genug nach unten kopieren.
Danach den Formelkomplex G24:M### nach P24:V### kopieren und ferdsch ...
Eine sortierte Ausgabe wird dann möglich, wenn Du Deine Datentabelle zuvor entsprechend sortierst.
Der auszuwertende Datenbereich in Daten!AI1 ist gemäß Deiner Vorgabe auf 5000 gesetzt. Den solltest Du auf das max notwendige beschränken bzw. besser gleich Deine Datentabelle als "intelligente" Tabelle formatieren und darauf bezogen den Auswertungsbereich in der Formel definieren, dann ist er dynamisch immer korrekt und nie überdimensioniert.
Gruß Werner
.. , - ...
Anzeige
AW: ein neuer Formellösungsansatz ...
25.08.2017 11:23:23
Moe
Hoi Werner
Genial! Funktioniert auch bei der "grossen" Tabelle einwandfrei und einigermassen schnell! Vielen Dank!
Ich werde evtl. die Datentabelle über eine DropDown-Liste pro Anlage (Spalte A in "Daten") vorsortieren, damit es übersichtlicher und schneller wird, spielt aber von der Logik her keine Rolle. Super!
Es gibt eine andere Tabelle, welche "nur" auf vier Spalten (I,K,M,O) ausgewertet werden muss. Ich konnte die Hilsspalten entsprechend anpassen, so dass das auch funktioniert! Top! ;)
Nun zum Thema Sortieren:
Ich werde mich noch in die "intelligente" Tabele einarbeiten, habe das noch nie gemacht. Leider kommt die originale Datentabelle direkt aus einer anderen Software und die Spaltennamen können sich ändern (mein erster Versuch hat gezeigt, dass die "intelligente" Tabelle keine Formeln in der Überschrift haben kann). Sortieren direkt in der Datentabelle möchte ich - nur wenn möglich - vermeiden, damit es automatisch bleibt...
Hast du eine Idee, wie man die Auswertungstabelle in einem weiteren "Bearbeitungsschritt" nach den 6 Spalten sortieren könnte? Oder soll ich gerade einen neuen Task eröffnen, damit nicht alles an dir hängen bleibt und ist ja eigentlich eine neue Fragenstellung...?
Gruss (und Danke, dass du meinen Horizont in der Excel-Welt erweitert hast!)
Moe
AW: bitte, und nun hierzu ...
25.08.2017 11:52:22
...
Hallo Moe,
... richtig, Spaltennamen müssen in formatierten Tabellen Text-Konstante sein. Das sollte aber nicht wirklich ein Problem sein, denn man muss ja in der Auswertung nicht zwingend auf die Überschriften-Zeile zugreifen, sondern könnte auch mit den Daten der erste Zeile, die dann die zu vergleichenden Überschriftstexte beinhalten, vergleichen und die Spaltennamen der formatierten Liste könnten trotzdem fix sein.
Wenn die Sortierung "nachgelagert" sein soll, dann müsstest Du zunächst mal angeben, nach welchen Kriterien diese denn erfolgen soll.
Gruß Werner
.. , - ...
AW: bitte, und nun hierzu ...
25.08.2017 13:34:35
Moe
Hoi Werner
Ok, ich werde mir das bezüglich der formatierten Tabelle überlegen bzw. ausprobieren. Ich habe jetzt die Datentabelle bereits nach Anlagennummer vorsektioniert (max. 500 Zeilen) und es läuft sehr schnell. Passt für mich soweit.
Nun zum Sortieren (nur wenn du noch magst ;):
Eigenlich sollte die Auswertungstabelle genau nach den Spalten der Grössse (steigernd) nach sortiert werden, ohne dass die Kombinationen verändert werden. Der "Insulation typ" muss bezüglich der Reihenfolge nicht auch noch berücksichtigt werden. Ich werde noch die 0 mit "-" ersetzen, aber das kriege ich hin ;)
Ich habe eine Beipieldatei erstellt, hoffe damit wird es verständlich:
https://www.herber.de/bbs/user/115763.xlsx
Danke und Gruss,
Moe
AW: Sortierung ist formeltechn. möglich, aber ...
25.08.2017 14:43:31
...
Hallo Moe,
... diese dürfte sich dann bei Deiner Datenmenge erheblich(er) auf die Performance auswirken.
Wahrscheinlich würde ich eine Sortierung schon gleich im Datenblatt mit möglicherweise zusätzlichen Hilfsspalten "vorbereiten", so dass die Auswertung danach gleich sortiert gelistet wird und keine zusätzliches Tabellenblatt notwendig würde.
Die Formelsortierung würde sich beschleunigen lassen, wenn nicht alle "Arten" aus Daten!AI1:AX1 gleichzeitig vorgenommen werden müssten. Wenn z.B. in Auswertung!H22 eine Dropdownliste aus Daten!AI1:AX1 eingesetzt werden kann, würde sich die Formelmenge auf nur noch ca. 1/6 reduzieren und damit den Geschwindigkeitsverlust zumindest kompensieren. Die notwendigen Formeln könnte ich dann ja auch so konstruieren, dass bei Bedarf und Möglichkeit (Auswertungsgeschwindigkeit wäre für Dich noch akzeptabel) auch ein zweiter Auswertungsbereich in Auswertung!P22:W### durch Kopie des Ersten leicht angelegt werden kann.
Was meinst Du?
Allerdings werde ich heute nicht mehr dazukommen, weil bereits anderweitig verplant.
Gruß Werner
.. , - ...
AW: Sortierung ist formeltechn. möglich, aber ...
25.08.2017 15:03:35
Moe
Hoi Werner
Danke für deine Inputs!
Zuerst vorne weg: Es ist kein Problem wenn das heute nicht mehr reicht!! ;)
Das mit den Dropdownliste der "Arten" möchte ich lieber nicht, weil ich jetzt bereits eine Dropdownliste pro Anlage habe (was die Datentabelle und die mögliche Kombinationen entsprechend reduziert). Somit müsste man dann für jede Anlage und jede "Art" (in der kompletten Tabelle sind es ca. 20 davon) alle Kombinationen per Dropdown durchgehen...
Ein zusätzliches Registerblatt oder weitere Hilfspalten sind für mich absolut kein Problem, denke könnte sich auf die Performance positiv auswirken, weil die Bereieche für das Sortieren kleiner sind. Aber da kennst du dich definitiv besser aus! Ausserdem ist die Performance zumindest bis 5 Min. Rechenzeit (habe die automatische Berechnung bereits deaktiviert und aktualisiere mit F9) sekundär. Jetzt bin ich bei der kompletten Tabelle bei rund einer Minute.
Was meinst du dazu? Ist das möglich? Wäre fantastisch!
Gruss,
Moe
AW: zu den Hilfsspalten ...
24.08.2017 15:47:35
Moe
Hoi Werner
Ich bins nochmal...
Im Original habe ich die Auswertungstabellen verschoben.
Hier die entsprechende reduzierte Datei: https://www.herber.de/bbs/user/115719.xlsx
Vielen Dank für die Unterstützung und Gruss,
Moe
AW: das war 6 sec zu spät ...
24.08.2017 15:50:20
...
Hallo Moe,
... prüfe bitte selbst, ob meine Verschiebung nach Deiner Vorgabe mit Deiner jetzigen übereinstimmt.
Gruß Werner
.. , - ...
AW: Unikate Kombinationen aus 6 Spalten ermitteln
24.08.2017 18:50:42
Christian
Hallo Moe,
hatte parallel angefangen eine VBA-Lösung zu entwickeln...
https://www.herber.de/bbs/user/115727.xlsm
... ohne den Faden weiter verfolgt zu haben :-)
Werner hat ja mächtig auf die Tube gedrückt und dir bereits sensationelle Formeln geliefert!
Du kannst ja bei Gelegeneit die profane VBA-Variante trotzdem einmal antesten.
Freue mich über deine Rückmeldung.
VG, Ch.
AW: Unikate Kombinationen aus 6 Spalten ermitteln
25.08.2017 07:09:17
Moe
Hallo Christian
Danke für dein Makro! Ich habe es getestet und mir ist nicht alles ganz klar; z.B. beinhalten die Tabellen teilweise noch Duplikate. Aber evtl. hilft dir bereits den Verlauf unserer Fragen & Antworten. Grundsätzlich sind mir Formellösungen lieber, weil ich .xlsm in meinem Bearbeitungsprogramm nicht als Vorlage importieren kann, also müsste ich alles auf Verknüpfungen aufbauen... Ausserdem verstehe ich VBA zu wenig, um auch nur Anpassungen zu machen... Aber trotzdem vielen Dank!
Ja, Werner ist unglaublich und ich denke er kriegt das hin!
Gruss,
Moe
AW: Unikate Kombinationen aus 6 Spalten ermitteln
25.08.2017 12:14:34
Christian
Hi Moe,
danke f. die Rückmeldung. Der Fehler war, dass bei der Duplikatsprüfung im Schlüssel die Anzahl berücksichtigt wurde. Ich habe die Prüfung geändet so das der Schlüssel aus folgenden Felder zusammengesetzt wird: DN,DN1,DN2,DN3,Insulation Thickness,Isolationsmaterial.
Das Makro schreibt jetzt für jeden neuen Item in der Datentabelle autom. einen neuen Block in das Blatt
'Auswertung_sortiert', wandelt die Blöcke in gut referenzierbare Datentabellen (T_1 ff.) um und sortiert nach Spalte Anzahl aufsteigend. Habe mal mit 6000 (unterkomplexen) DS getestet. Laufzeit liegt bei kleiner 5 Sek.
https://www.herber.de/bbs/user/115752.xlsm
VG, Christian
AW: Unikate Kombinationen aus 6 Spalten ermitteln
25.08.2017 13:53:57
Moe
Hoi Christian
Sehr beeindruckend! Sieht wirklich gut aus!
Trotzdem werde ich zurzeit auf den Formellösungen von Werner bleiben, weil ich diese - wenn auch nur halbwegs - verstehe und ich kleine Anpassungen selber machen kann. Bei einem Makro ist das für mich zurzeit nicht möglich, obschon ich mich bei Gelegenheit einarbeiten möchte...
Trotzdem vielen Dank und Gruss,
Moe
AW: Unikate Kombinationen aus 6 Spalten ermitteln
25.08.2017 14:57:35
Christian
Hi Moe,
hier meine finale Version ;-)
Jetzt wird auch die Anzahl je Unikat addiert ....
https://www.herber.de/bbs/user/115767.xlsm
VG, Christian
AW: inklus. sortierter Ausgabe, nur mit Formeln ..
26.08.2017 12:03:12
...
Hallo Moe,
... vorab: die effektivere Methode bei Deiner Datenmenge ist ohne Zweifel eine VBA-Ausgabe (behaupte ich, obwohl ich mich mit VBA nicht beschäftige)
Du hattest aber den ausdrücklichen Wunsch nach einer Formellösung hier immer wieder betont. Deshalb hab ich mich auch interessehalber der Thematik für Deine Massendaten gewidmet. Dies hat mich zwar ein paar Stunden am heutigen "glücklicherweise" ;-) verregneten Samstag an Zeit gekostet, aber ich hab eine vertretbare Lösung gefunden. Dazu konnte die bisherige Datenstruktur komplett beibehalten werden und es wurden auch keine neuen Hilfsspalten notwendig. Lediglich drei Hilfsspaltenformeln hab ich abgeändert (entsprechend erweitert). Dabei hab ich stets versucht Formeln zu konstruieren, die zwar die zusätzliche Bedingung (Sortierung) berücksichtigt (und damit auch länger werden) aber möglichst nicht viel langsamer in der Auswertung sind. War selbst überrascht, dass sich dies bei den vorhanden knapp 1000 Datensätzen sich relativ weniger auswirkte als von mir befürchtet.
Deshalb hab ich gleich noch eine Dropdownzelle eingebaut, über die die Ausgabe innerhalb des Tabellenblattes Auswertung auf Klick zwischen sortierter und unsortierter Ausgabe gewechselt werden kann.
Oh, gerade kommt meine Sonne und meint ich solle jetzt in diese. Ich melde mich heute gegen Abend dann wieder.
Gruß Werner
.. , - ...
AW: inklus. sortierter Ausgabe, nur mit Formeln ..
26.08.2017 13:28:01
Moe
Hoi Werner
Vielen Dank, das hört sich fantastisch an! Ja, dass es eine reine Formellösung ist, ist mir wichtig ;)
Nun eine wichtige Info, welche eventuell deine Sortierformeln negativ beeinflussen könnte:
Ich wollte heute Morgen einen neuen Export aus der Bearbeitungssoftware machen, dazu wählte ich meine Excel-Vorlage (mit den Berechnungen) aus. Es passierte nichts... Nach mehreren Stunden Fehlersuche wurde klar, dass (aus mir absolut unerklärlichen Gründen!!!!) die Vorlage wegen der Funktion AGGREGAT nicht akzeptiert wird. Ich habe nun deine Formel in den Zellen AI1-BX1:
WENNFEHLER(INDEX($G:$G;AGGREGAT(15;6;ZEILE(G2:G5000)/($G2:$G5000"")/(ZÄHLENWENN($AH1:AH1;$G2:$G5000)=0);1));"")
... ersetzt und mache einen direkten Zellbezug auf eine separate Auswertungsliste in einem neuen Registerblatt, welche mir alle "Arten" untereinander auflistet, einfach weniger schön bzw. ohne AGGREGAT. Die Logik deines Formelkomplexes blieb ansonsten gleich, auch die Spalten usw. blieben identisch.
Fazit: Ich hoffe, du wolltest die Sortiererei nicht über AGGREGAT lösen, ansonsten müssen wir uns eine Möglichkeit ausdenken, die Auswertung ohne diese Funktion zu lösen. :/
Alles nur wenn möglich versteht sich...!!
Gruss und hoffe, diese Meldung kam nicht ein paar Stunden zu spät,
Moe
PS: ich gehe jetzt auch mal an die Sonne ;)
AW: dies wäre dann aber vorab noch zu klären ...
26.08.2017 16:46:29
...
Hallo Moe,
... denn das was Du festgestellt hast, kann ich mir momentan nur erklären, wenn Du die Datei nicht unter einer Excelversion ab 2010 oder jünger aufgerufen hast, sondern unter Excel 2007.
Solltest Du die Lösung auch für Excel 2007 einsetzen wollen/müssen, könnte ich diese Formel auch noch in eine echte klassische Matrixformel ohne AGGREGAT() umschreiben. Dies wäre dann aber auch die einzige derartige Formel, denn die Sortierung hab ich bei dieser Datenmenge bewusst nicht mit AGGREGAT() oder gar klassischen Matrixformeln aufgebaut. Wenn das Problem unter einer aktuelleren Excelversion auftritt, müsstest Du mir einen relevanten Dateiauszug zur Verfügung stellen, wo das Problem nachvollziehbar wäre. Bis später dann.
Gruß Werner
.. , - ...
AW: könnte es evtl. auch sein ...
28.08.2017 08:39:57
...
Hallo Moe,
... dass Du in Deiner Datei die Berechnungsoption noch auf manuell zu stehen hattest?
Gruß Werner
.. , - ...
AW: könnte es evtl. auch sein ...
28.08.2017 09:10:21
Moe
Hoi Werner
Nein, ich denke dass es tatsächlich mit der Version zu tun hat... Ich habe definitiv Excel 2010, aber das Problem lag nicht an Excel selber, sondern an der Ausgabesoftware, welche meine Vorlage nicht akzeptierte, wenn diese Funktion integriert ist...
Das hiesst, dass die Daten gar nicht erst in das Auswertungs-Excel kamen, also gab es gar nichts zu aktualisieren ;)
Nun klappt ja alles bestens, also lasse ich das mal so stehen...
Gruss,
Moe
AW: da die folgenden Formeln "unabhängig" sind ...
26.08.2017 19:32:00
...
Hallo Moe,
... stelle ich Dir diese nun doch schon bereit, weil ich nicht weiß, ob ich Morgen dazu komme.
Die Hilfsspaltenformel in Daten!AH2 ändert sich zu:
=G2&"!"&TEXT(I2;"000")&"#"&TEXT(K2;"000")&"#"&TEXT(M2;"000")&"#"&TEXT(I2;"000")&"#"
&TEXT(I2;"000")&"#"&Y2 und Formel nach unten kopieren.
und die in Daten!AI2 zu:
=WENN(($G2AI$1)+(AI$1="");"";WENN($AH$1="ja";WENN(ZÄHLENWENNS($AH$1:$AH2;$AH2;$G$1:$G2;AI$1)
=1;ZÄHLENWENNS($AH:$AH;"
und Formel nach unten und rechts kopieren.
Die AGGREGAT()-Formel in Daten!AI1 kann bei Excel2010 und jünger so bleiben wie bisher. Nur wenn Du teils Excel 2007 im Einsatz hast, dann anstelle derer folgende klassische Matrixformel einsetzen und nach rechts kopieren:
{=WENN(AH1="";"";WENNFEHLER(INDEX($G:$G;KKLEINSTE(WENN(($G2:$G5000"")
*(ZÄHLENWENN($AH1:AH1;$G2:$G5000)=0);ZEILE(G2:G5000));1));""))}
In Daten!AH1 hab ich die Dropdownzelle mit Liste: ja;nein und dem benutzerdefinierten Zahlenformat: "Ausgabe sortiert: "@"!" vorgesehen. Je nach Wahl erfolgte ein sortierte Ausgabe oder Ausgabe in Originalreihenfolge in Daten. Ist die Zelle leer, erfolgte keine Ausgabe.
Die Hilfsspaltenformel in Auswertung!G24 ist nun geändert zu:
=WENN((G23="")*(ZEILE()>24);"";WENN(ZEILE(G1)>ANZAHL(INDEX(Daten!$A:$AZ;;VERGLEICH(INDEX(H:H;22);
Daten!$1:$1;0)));"";VERGLEICH(KKLEINSTE(INDEX(Daten!$A:$AZ;;VERGLEICH(INDEX(H:H;22);Daten!$1:$1;0));
ZEILE(P1));INDEX(Daten!$A:$AZ;;VERGLEICH(INDEX(H:H;22);Daten!$1:$1;0));0)))
und Formel nach unten kopieren und diese Hilfsspaltenformeln Auswertung!G24:G### nach P24:P### etc. kopieren.
Die endgültige Auswertungsformeln in H24:M24 können 1:1 so bleiben, wie schon gehabt.
Gruß Werner
.. , - ...
AW: da die folgenden Formeln "unabhängig" sind ...
28.08.2017 09:04:05
Moe
Hoi Werner
Wahnsinn! Wirklich geniale Konstruktion! Funktioniert sogar erstaunlich schnell! Danke!
Ein kleiner "Fehler" hat sich eingeschlichen, welcher ich selber beheben konnte: Bei deiner Formel ab AH2, wurde die DN 3 (Spalte O) und die Insulation thickness (Spalte W) nicht berücksichtigt. Ich habe die Formel geändert auf:
=G2&"!"&TEXT(I2;"000")&"#"&TEXT(K2;"000")&"#"&TEXT(M2;"000")&"#"&TEXT(O2;"000")&"#"
&TEXT(W2;"000")&"#"&Y2
was wunderbar funktioniert! ;)
Also ich werde nun das "Tool" finalisieren und hoffe, dich nicht mehr weiter beanspruchen...
Nochmals vielen Dank für die Unterstützung!
Gruss,
Moe
AW: danke, freut mich auch ...
28.08.2017 11:15:26
...
Hallo Moe,
... und zur Erklärung der eingestellten Hilfsspaltenformel AH2. In dieser hatte ich den Teil: &TEXT(I2;"000")&"#" mehrmals aneinander kopiert und in den zwei letzten zwei teilen die Änderung des Spaltenbezugs nicht vorgenommen. Das war mir nicht aufgefallen, weil diese Spaltenwerte bereits sortiert standen. Aber gut das Du es bemerkst hast. man kann ja nie wissen, welche Daten zur Auswertung anstehen.
Gruß Werner
.. , - ...

301 Forumthreads zu ähnlichen Themen

Spalten aus über 400 excel files auslesen
marky

Hallo Excel Freaks !
Habe folgendes Problem.
Ich möchter über ein makro auf einen Ordner Zugreifen indem sich über 400 excel files befinden. Die excel files beinhalten nur ein Sheet wobei dieses den Namen des files hat (Also nicht Tabelle 1 oder Sheet 1 oder so).
Ich möchte dass das...
03.09.2008 21:44:41
Thread-ID: 1007240
5 Beiträge
Anzeige
Werte aus 2 Spalten vergleichen??
stefan

Hallo,
ich habe in 2 Spalten Werte. Wie kann ich prüfen, ob die werte in der einen spalte enthalten sind. WEnn sie enthalten sind, soll in einer 3.spalte ein text ausgegeben werden.
wie mach ich das am besten?
danke euch?
13.03.2009 16:57:19
Thread-ID: 1059944
4 Beiträge
Array Splitten Transponse Ausgabe in 2 Spalten
chris

Hallo VBA experten,
ich habe eine frage und bitte um eure Hilfe.
Ich komme alleine nicht weiter:(
Ich habe eine Tabelle1. In Spalte 1 Stehen z.b Werte untereinander.
27>27
17>17
6>6
17>1
usw...
diese möchte ich gerne in ein Array übernehmen und da...
23.04.2009 10:44:32
Thread-ID: 1070604
10 Beiträge
Anzeige
Spalten zählen und ausblenden
Sophie

Hallo liebe Forum-Leser!
Ich versuche in Excel sowas ähnliches wie MS Project darzustellen (weil das so gewünscht wird, fragt aber nicht warum).
Durch Datumseingabe (für Anfang und Ende des Projekts) will ich nur den Zeitstrahl (in Kalendeswochen) darstellen, so dass man die einzelne Sc...
15.06.2009 19:17:19
Thread-ID: 1081675
9 Beiträge
kombinationen aus spalten bilden
gh

hallo zusammen,
gibt es bei excel eine möglichkeit aus spalte 1 und 2 oder 1, 2 und 3 kombinationen zu bilden?
vielen dank im voraus!
lg
25.09.2009 12:52:27
Thread-ID: 1105553
11 Beiträge
Datum aus 2 spalten filtern
Gregor

Hallo
Ich habe in Spalte M ein Datum "Ausführung Beginn" und in Spalte N ein Datum "Ausführung Ende". Wie kann ich für einen bestimmten Zeitraum alle Daten zwischen Ausrührung Beginn und Ausführung Ende filtern. Zb. alle Arbeiten die in der Zeit vom 1.11.10 und 31.11.10 ausgeführt werden bz...
18.11.2010 20:16:31
Thread-ID: 1187609
3 Beiträge
alle Kombinationen aus zwei Spalten ermitteln
Jockel

Hallo,
ich habe eine Tabelle mit zwei Spalten:
A_____________B______________
Beleg_________Code__________
125___________60____________
125___________40____________
122___________30____________
122___________30____________
usw.
Ich möchte nun per Makro ermitte...
25.01.2011 09:28:18
Thread-ID: 1197160
4 Beiträge
+/- Ausgabe aufgrund Folge von 2 Spalteneinträge
2

Hallo Excelianer,
habt Ihr eine Idee, wie ich die +/- Einträge automatisiert auf Basis der 2 Regelen darstellen kann.
Die Spalten F,G,H, sind lediglich Hilfsspalten.
Ich scheitere immer daran, wenn ich auf einen vorherigen Block schauen muss. In Regel 1 muss ich ja im selben Bereic...
13.05.2011 16:01:26
Thread-ID: 1213743
2 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
5
3
9
6
8
10
2
3
1
3
14
7
6
3
10
6
7
4
6
4
3
4
1
19