Microsoft Excel

Herbers Excel/VBA-Archiv

Texte unter 2 bestimmungen ausgeben


Betrifft: Texte unter 2 bestimmungen ausgeben von: Chris
Geschrieben am: 27.07.2018 22:08:31

Hallo an die Excel Gemeinde.

Wollte mal fragen ob es eine Möglichkeit gibt, die Texte aufzulisten (aus Tabelle2!D1:D92408)

die folgende beiden Kriterien erfüllen:

1. der Text muss mindestens zweimal vorkommen (kann bis zu 93mal vorkommen)
2. In den Zeilen, in denen der Text vorkommt müssen mindestens 2 unterschiedliche Zahlen in Spalte F stehen.

Falls jemand eine Makrolösung bevorzugt, dies kann aufgrund meines Aufbaus der Tabelle nur in den 3094 Zeilen vorkommen, in denen der Text in Spalte D mit roter Schriftfarbe versehen ist. Nur so als Info, falls das die Sache vereinfacht.

Vielen Dank
Chris

  

Betrifft: AW: Texte unter 2 bestimmungen ausgeben von: Hajo_Zi
Geschrieben am: 28.07.2018 06:46:22

Hallo Chris,

meinst Du in Spalte F Ziffern 1 bis 9, Bei 2 Zahlen 12 9 ist es Text nicht Zahlen.

Eine hochgeladene Arbeitsmappe erhöht die Wahrscheinlichkeit, dass Du eine Lösung für Dein Problem erhältst.
Erstelle folglich bitte eine Demomappe, aus der deine Aufgabenstellung klar erkennbar ist und lade diese hoch.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, solltest du diese Daten anonymisieren bzw. pseudonymisieren.
Falls Du den Download des Forums nicht benutzen möchtest beachte bitte: von unsicheren Servern file-upload lade ich keine Datei herunter (lt. Einschätzung meines Virenprogramms)

GrußformelHomepage

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.


  

Betrifft: AW: Texte unter 2 bestimmungen ausgeben von: Chris
Geschrieben am: 28.07.2018 07:07:14

Hallo Hajo, in Spalte F stehen ganze Zahlen zwischen -292665 und 34987.

Hier eine kleine Bsp Datei

https://www.herber.de/bbs/user/122959.xlsx

Christian


  

Betrifft: AW: Texte unter 2 bestimmungen ausgeben von: Hajo_Zi
Geschrieben am: 28.07.2018 07:17:45

Hallo Christian,

ich kann Dir Leider nicht helfen, da ich dies

2. In den Zeilen, in denen der Text vorkommt müssen mindestens 2 unterschiedliche Zahlen in Spalte F stehen.

nicht nachvollziehen kann. Ich sehe nur eine Zahl, Ziffern waren es ja nicht.
Viel Erfolg noch.

Gruß Hajo


  

Betrifft: AW: Texte unter 2 bestimmungen ausgeben von: Chris
Geschrieben am: 28.07.2018 07:35:44

Hallo Hajo,

in allen Zeilen in denen Text2 steht steht dieselbe Zahl (19) in Spalte F. Daher soll Text 2 nicht ausgegeben werden.

In den Zeilen in denen Text3 steht, stehen unterschiedliche Zahlen in Spalte F (zweimal 16 und einmal 20) daher soll Text3 ausgegeben werden.

Jetzt Verständlich?

Gruß
Christian


  

Betrifft: mehrfache Texte nur bei unterschiedlichen Zahlen von: WF
Geschrieben am: 28.07.2018 07:59:42

Hi,

in H1:
=WENN(SUMMENPRODUKT((D$1:D$99&F$1:F$99=D1&F1)*1)<>ZÄHLENWENN(D$1:D$99;D1);D1;"")
runterkppieren

WF


  

Betrifft: AW: mehrfache Texte nur bei unterschiedlichen Zahlen von: Chris
Geschrieben am: 29.07.2018 07:18:27

Hallo WF,

sorry dir stimme ich da nicht zu. Mir wurde nicht nur diese, sondern noch eine weitere Formel von Werner gegeben. Das diese die richtige ist, war erst klar als ihr euch geeinigt hattet wer meine Aufgabenstellung richtig interpretiert hat.
Somit hat mir die Erfahrung gefehlt, das selbst herauszufinden, welche Formel die korrekte ist.

Gruß
Christian


  

Betrifft: AW: eine reine Formellösung ist kaum ratsam, ... von: neopa C
Geschrieben am: 28.07.2018 13:33:57

Hallo Chris,

... denn die würde bei Deiner großen auszuwertenden Datenmenge wohl die meisten PCs auch ohne die aktuelle Hitze arg ins Schwitzen bringen.

Meine nachfolgende Formellösung ermittelt zwar alle gesuchten Texte nacheinander gelistet (ohne Dupletten). Aber für viel mehr als die von mir angesetzten auszuwertenden 999 Datensätze würde ich diese Formel nicht einsetzen. Und Du willst ja knapp 100.000 Datensätze auswerten.

In I2:

=WENNFEHLER(INDEX(D:D;AGGREGAT(15;6;ZEILE(D$1:D$999)/(ZÄHLENWENNS(D$1:D$999;D$1:D$999;
F$1:F$999;F$1:F$999)=1)/(ZÄHLENWENN(D$1:D$999;D$1:D$999)>1)/(ZÄHLENWENN(I$1:I1;D$1:D$999)=0);1));"")
und Formel nach unten kopieren.

Günther würde sicherlich den Einsatz von PowerQuery empfehlen. Doch da ich bzgl. PQ noch Anfänger bin, ist mir eine Lösung damit nicht gelungen.

Mein Vorschlag deshalb, eine Kombination aus Pivotauswertung und einer einfachen Formelhilfsspalte sowie einer abschließenden weiteren einfachen Formelendauswertung dieser Hilfsspalte und der Pivotauswertungsergebnisse. Letztere erfordert zuvor noch Überschriften über den Daten von Spalte D:F und ist von mir in Spalte L:M im Tabellenlayout(!) ohne Zwischen- und Gesamtergebnisse ausgegeben. Diese sollte auch bei dieser Datenmenge ausreichend schnell sein. Einziger Nachteil, bei Datenänderung muss zwingend die Pivotauswertung aktualisiert werden. Allerdings reicht dazu ein einziger Mausklick aus.

Die Hilfsspaltenformel K2 durch Doppelklick nach unten kopieren lassen und das Endergebnis wird mit der Formel I2, welche weit genug nach zu kopieren ist ermittelt.

 DEFGHIJKLM
1TextleerWert  Erg_List  TextWert
2Text3 20  Text3  Text112
3Text2 19  Text4  Text219
4Text2 19  Text5 4Text316
5Text3 16      20
6Text3 16    6Text411
7Text1 12      12
8Text4 12      10
9Text5 12    9Text511
10Text5 12      12
11Text4 11     Text610
12Text5 11     (Leer)(Leer)
13Text4 10       
14Text6 10       
15          

Formeln der Tabelle
ZelleFormel
I2=WENNFEHLER(INDEX(L:L;KKLEINSTE(K:K;ZEILE(D1))); "")
K2=WENN((L2<>"")*(L3=0)*(ISTZAHL(M2)); ZEILE(); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: eine reine Formellösung ist kaum ratsam, ... von: Chris
Geschrieben am: 28.07.2018 13:45:22

Hallo Werner,

Ich bin es gewohnt dass eine formel bei mir mal 20 min braucht zum rechnen :-) probiere mal aus wenn ich heute nachmittag daheim bin. Das ist eine einmalige sache wo ich die formel brauche. Der sinn ist, das eigentlich jedem text eine feste zahl zugeordnet sein soll. Nur wenn getippt wird entstehen tippfehler und die will ich beheben. Ist also einmalig wo dass berechnet werden muss. Kann also auch ruhig was zeit brauchen.

Gruß chris


  

Betrifft: Mit Power Query ? von: robert
Geschrieben am: 28.07.2018 15:09:44

https://www.herber.de/bbs/user/122969.xlsx

Gruß
robert


  

Betrifft: AW: dieses trifft es leider noch nicht ganz ... von: neopa C
Geschrieben am: 28.07.2018 16:10:23

Hallo Robert,

... denn in Deinen Beispiel ist auch "Hubert" als Ergebnis gelistet, dürfte es aber laut Aufgabenstellung nicht. Denn er ist zwar zweimal aber da mit dem gleichen Wert gelistet.
An diesem Problem mit PQ war ich heute Mittag auch gescheitert. Es gibt aber bestimmt auch dafür eine Lösung. Vielleicht findest Du diese ja schon.

Gruß Werner
.. , - ...


  

Betrifft: Huber ist 3 mal..... von: robert
Geschrieben am: 28.07.2018 16:16:42

Hi,

gelistet- 2 mal 12 und 1 mal 44- daher gelistet-nach meinem Verständnis :-)

Gruß
robert


  

Betrifft: AW: hast völlig Recht, ...das ist DIE Lösung ... von: neopa C
Geschrieben am: 28.07.2018 16:53:53

Hallo Robert,

... das hab ich vorhin auf die Schnelle wirklich übersehen hatte immer nur zwei gesehen :-(.

Damit ist mir jetzt durch Deine Lösung klar geworden, wo mein Fehler bei meiner PQ-Lösungsversuch lag. Ich hatte nicht nach "eindeutige Reihen zählen" gruppiert. Gut zu wissen.

Danke.

Wenn man es weiß, wie es richtig geht, ist wie ich schon vermutet hatte, die PQ-Lösung für Chris die einfachste und eindeutig effektivste Lösungsmethode.

Gruß Werner
.. , - ...


  

Betrifft: äußerst seltsam von: WF
Geschrieben am: 28.07.2018 15:08:15

Du rätst von einer Formel ab und dann kommt von Dir eine, die mehr als doppelt so lang ist, wie meine ?

Ist mir zu hoch
WF


  

Betrifft: AW: hast einiges überlesen, außerdem ... von: neopa C
Geschrieben am: 28.07.2018 16:06:04

Hallo WF,

... habe ich die Aufgabenstellung anders ausgelegt als Du.

Deine Formel listet lediglich die Texte in der Hilfsspalte an der Stelle auf, wo die 2 Bedingungen zutreffend sind. Dies aber egal ob schon mal gelistet war oder nicht.

Überlesen hast Du, dass Chris nicht nur 99 Datensätze sondern das tausendfache dieser auswerten will. Mal ganz davon abgesehen davon, dass bei Anpassung Deiner Formel auf diese Datenmenge Excel arg zu kämpfen hätte, wem nützt ein Listing mit Deiner Formel? Dies kann dann bestenfalls auch nur ein Zwischenergebnis sein.

Meine Formel könnte die 93 Zellen (so hab ich bisher Chris verstanden) nach unten kopiert werden und hätte in diesen Zellen dann alle gesuchten Texte gelistet. Hab diese aber nur für bis zu ca. 1000 auszuwertenden Datensätzen vorgeschlagen.

Deswegen hab ich Chris von einer reinen Formellösung generell abgeraten (also auch von meiner) und alternative Lösungsmöglichkeiten vorgeschlagen und eine davon aufgezeigt. Diese benötigt zwar ein vielfaches an Formeln, dürfte aber wesentlich schneller zum sehr wahrscheinlich gewünschten Ergebnis gelangen als reine Formellösungen.

Günstiger mE wäre wohl nur eine VBA-Lösung oder eine funktionsfähige mittels PQ.

Gruß Werner
.. , - ...


  

Betrifft: warum hast Du dann diese Monsterformel gebracht, von: WF
Geschrieben am: 28.07.2018 16:14:28

... wenn Du diese selbst als untauglich einstufst ?

WF


  

Betrifft: AW: warum hast Du dann diese Monsterformel gebracht, von: Chris
Geschrieben am: 28.07.2018 16:45:32

Hallo ihr beiden,

ich blicke jetzt gar nicht mehr durch, wer hier was wie verstanden hat,

ich drücke es mal andersrum aus,

es soll jeder Text aus Spalte D aufgelistet werden, außer denen die

1. nur einmal vorkommen oder
2. öfter als einmal vorkommen, aber bei jedem Vorkommen dieselbe Zahl in Spalte F steht.

Ein Makro wäre vlt. wirklich am sinnvollsten, weil man dort die Anzahl der Zeilen deutlich einschränken kann.

Mir ist eingefallen, alle Datensätze, die ich von Hand erfasst habe und damit Tippfehler enthalten können haben in Spalte E einen Text mit Schriftgröße 20. Alle Zeilen, deren Text in Spalte E eine andere Schriftgröße haben, wurden nicht händig erstellt und haben daher auch keine Tippfehler. Damit hätten wir die Anzahl von 90000 auf 200-300 eingegrenzt.

Aber eine Formel kann ja m.e. nicht die Schriftgröße überprüfen.

Gruß
Chris


  

Betrifft: AW: warum hast Du dann diese Monsterformel gebracht, von: Sepp
Geschrieben am: 28.07.2018 16:47:31

Hallo Chris,

und warum prüfst du nicht schon bei der Eingabe auf Richtigkeit?


 ABCDEF
1Gruß Sepp
2
3



  

Betrifft: AW: warum hast Du dann diese Monsterformel gebracht, von: Chris
Geschrieben am: 28.07.2018 16:55:22

Hallo Sepp,

gute Frage, mache ich ja auch, nur bei so vielen Datensätzen kann man trotzdem mal was übersehen. Außerdem ist es theoretisch möglich, wenn auch sehr unwahrscheinlich, dass wenn zwischen dem Eingeben von 2 Datensätzen eine längere Zeit lag, dass sich in der Zeit die Zahl in der Datenquelle geändert hat.

Gruß
Christian


  

Betrifft: AW: warum hast Du dann diese Monsterformel gebracht, von: Sepp
Geschrieben am: 28.07.2018 17:23:34

Hallo Chris,

wie machst du den die Prüfung? XL kann das auch, nennt sich 'Datengültigkeit'!


 ABCDEF
1Gruß Sepp
2
3



  

Betrifft: AW: warum hast Du dann diese Monsterformel gebracht, von: Chris
Geschrieben am: 28.07.2018 17:32:41

vergleichen, das was ich abgetippt habe mit dem Original, also visuelle Prüfung, keine von Excel


  

Betrifft: AW: warum hast Du dann diese Monsterformel gebracht, von: Sepp
Geschrieben am: 28.07.2018 17:46:58

Hallo Chris,

wenn ich dich richtig verstehe, darf jeder Name zwar mehrmals vorkommen er muss aber immer die selbe Nummer in F stehen haben.

Für die Zellen in D und F jeweils: Daten > Datentools > Datenüberprüfung > Benutzerdefiniert > als Formel eingeben.

=((ZÄHLENWENN($D$2:$D$1000;D2)>1)*(SUMMENPRODUKT(($D$2:$D$1000&$F$2:$F$1000=D2&F2)*1)=1))=0
 ABCDEF
1Gruß Sepp
2
3



  

Betrifft: um heute 07:59 Uhr kam diese Formel von mir von: WF
Geschrieben am: 28.07.2018 18:06:33

.


  

Betrifft: AW: das wäre jetzt eine neue Aufgabenstellung ... von: neopa C
Geschrieben am: 28.07.2018 17:00:33

Hallo Chris,

... bis dato war die Rede von Spalte D und F. Jetzt schreibst Du von Daten in Spalte E?

Welche Daten sollen denn nun wirklich geprüft werden und wie soll das Ergebnis denn aussehen?

Gruß Werner
.. , - ...


  

Betrifft: AW: das wäre jetzt eine neue Aufgabenstellung ... von: Chris
Geschrieben am: 28.07.2018 17:10:45

Hallo Werner,

die Aufgabenstellung bleibt die Gleiche,

ich habe nur gesagt, dass die Texte die die gewünschte Formel ausgeben soll, allesamt in Zeilen stehen, in denen der Text in Spalte E Schriftgröße 20 hat.

Dass man die anderen Zeilen mit anderen Schriftgrößen nicht untersuchen braucht, weil dort definitiv nichts steht, was die Formel ausgeben soll.

Gruß
Christian


  

Betrifft: AW: deswegen, weil ich damit gerechnet hatte, ... von: neopa C
Geschrieben am: 28.07.2018 16:52:50

Hallo,

... dass Du gegen meine Feststellungen in meinem ersten Beitrag sein könntest und ich mir schon vorab eine etwas bessere Argumentationsgrundlage Dir gegenüber schaffen wollte.

Allerdings belegt Deine neue Aussage leider nur, dass Du meine Aussagen entweder nicht liest oder aus irgendeinen mir nicht erklärlichen Grund, nicht verstehen willst oder wenigsten wirkliche Sachargumente einbringst.

Gruß Werner
.. , - ...


  

Betrifft: eine lückenlose Aufteilung ist sowieso untauglich von: WF
Geschrieben am: 28.07.2018 17:04:24

Hi,

Der Fragesteller will ja, wie er angemerkt hat, korrigieren.
Spalte H nach <>"" gefiltert, sieht er, wo ne Macke ist.
Die entspr. Zahl in F korrigiert, verschwinden 2 bis mehrere Einträge in H

WF


  

Betrifft: AW: deswegen, weil ich damit gerechnet hatte, ... von: Chris
Geschrieben am: 28.07.2018 17:06:37

Hallo WF, Hallo Werner,

jetzt muss ich mich auch mal einmischen, selbst wenn ich mir damit keine Freunde mache und mir irgendwie anderweitig helfen muss.

Ich bin hier ein Fragesteller, der sich Hilfe wünscht, damit eine über langen Zeitraum erstellte Tabelle keine Fehler mehr enthält.
Ich erwarte von Euch ernst gemeinte, nach bestem Wissen und Gewissen erstellte Antworten. Klar können da natürlich auch Misverständnisse entstehen, genauso wie jeder auch mal dabei Fehler machen kann. Dafür ist so ein Forum ja auch da, um sich über unterschiedliche Meinungen, wie ein Problem zu lösen ist auszutauschen und auf Fehler in erstellten Formeln, Makros und was es noch so gibt, hinzuweisen.

Wenn es jedoch soweit geht, dass solche Diskussionen zwischen euch beiden auf dem Rücken meiner Fragestellung ausgetragen werden, sogar vorgeworfen wird, man würde sich nicht ausreichend mit den Beiträgen beschäftigen, frage ich mich, ob ich hier im richtigen Forum bin, sowas könnt ihr gerne unter euch klären.

Gruß
Christian


  

Betrifft: AW: erst durch Deine Aussagen 16:55 und 17:32 ... von: neopa C
Geschrieben am: 28.07.2018 19:09:12

Hallo Chris,

... die Du Sepp gegenüber gemacht hast, ist jetzt für mich klar, dass Du was anderes als Zielstellung hast, als welche ich interpretiert habe. Diese beiden Aussagen hast Du jedoch erst nach meinen letzten Beitrag hier gemacht.

Durch Deine jetzigen Aussagen sind natürlich meine Lösungsvorschläge (inklusive der PQ-Lösung, die Robert auf Grund meiner Anregung aufgestellt hat) alle zwar als Hilfestellung nur für Dich gemeint, aber eben leider "für die Katz" gewesen.

Eine gute, eindeutige Fragestellung ist eben immer noch die halbe Lösung.

Ich sehe auf die Schnelle keinen Ansatz wie Deine zu prüfenden Daten mit der anderen Schriftgröße zur Reduzierung der auszuwertenden Daten berücksichtigt werden können, so dass mE weiterhin knapp 100.000 Datensätze zu prüfen sind. Ob Dir da der Formelansatz von WF, erweitert auf Deine wirklich zu prüfende Datenmenge, eine Hilfe sein kann, kannst jetzt nur Du prüfen.

Ich denke dass es möglicherweise auch dafür eine PQ-Lösung geben könnte. Da dieser Beitrag aber für knapp eine Woche mein letzter Beitrag hier im Forum sein wird, kann ich den weiteren Verlauf Deiner Problemlösung bis dahin nicht mehr verfolgen.

Gruß Werner
.. , - ...


  

Betrifft: AW: erst durch Deine Aussagen 16:55 und 17:32 ... von: Sepp
Geschrieben am: 28.07.2018 19:17:48

Hallo Werner,

"für die Katz" waren eure Vorschläge nicht, ich habe ja Chris gefragt, warum er nicht bereits bei der Eingabe verhindert, dass falsche Daten eingegeben werden. Für die bereits vorhandenen Daten braucht er ja immer noch eine Kontrolle.


 ABCDEF
1Gruß Sepp
2
3



  

Betrifft: AW: erst durch Deine Aussagen 16:55 und 17:32 ... von: Chris
Geschrieben am: 28.07.2018 23:02:56

Hallo Werner,

darf ich dann mal eine Möglichkeit ins Spiel bringen, mit der Schriftgröße?

Ein Makro das eine Hilfsspalte macht, in der jede Zeile einen Eintrag erhält, in der die SG 20 ist.

Nach dieser Spalte sortieren und schwupps braucht man die Formel nur noch auf diesen Bereich statt auf 90000 Zeilen zu beziehen.

Aber etwas anderes, nach meinem Verständnis habe ich zu Beginn nach einer Formel gefragt, die alle Texte auflistet, die mehr als einmal vorkommen und in den Zeilen in denen der jeweilige Text steht, unterschiedliche Zahlen in Spalte F stehen. Ich verstehe immer noch nicht, was daran misverständlich sein kann. Aber das liegt wahrscheinlich auch daran, dass mir die Erfahrung fehlt, mich in die Seite der Helfer hineinversetzen zu können.

Gruß
Chris


  

Betrifft: die Formel, die Du suchst, hab ich gestern von: WF
Geschrieben am: 29.07.2018 05:52:43

um 07:59 Uhr gebracht.
Dir fehlt es nicht an Erfahrung, sondern an Aufmerksamkeit.

WF

https://www.herber.de/forum/messages/1636338.html


Beiträge aus dem Excel-Forum zum Thema "Texte unter 2 bestimmungen ausgeben"