Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Suche findet Werte nicht
21.10.2019 13:14:25
B.
Ich bin langsam am Verzweifeln.
Ich habe Office 365 Pro Plus (kann man beim Eingabeformular übrigens nicht auswählen :-) )
Ich versuche über eine Artikelnummer eine SAP Nummer zu finden und die entsprechende Nummer auszugeben. Meine Formel dazu lautet:
=WENN(LÄNGE(E4)>20;"mehr als 20 Zeichen in der Artikelnummer ";WENNFEHLER(WENN(E4>0; INDEX('D:\Users\09249565\Desktop\Test\[Herstellernummer.xls.xlsx]Herstellernummer'!$A:$J; VERGLEICH(TEXTKETTE("*";TEIL(E4;1;1);"*";TEIL(E4;2;1);"*"; TEIL(E4;3;1);"*";TEIL(E4;4;1);"*";TEIL(E4; 5;1);"*";TEIL(E4;6;1);"*"; TEIL(E4;7;1);"*";TEIL(E4;8;1);"*";TEIL(E4;9;1);"*";TEIL(E4;10;1);"*"; TEIL(E4;11;1);"*";TEIL(E4;12;1);"*";TEIL(E4;13;1);"*";TEIL(E4;14;1);"*"; TEIL(E4;15;1);"*";TEIL(E4; 16;1);"*";TEIL(E4;17;1);"*";TEIL(E4;18;1);"*"; TEIL(E4;19;1);"*";TEIL(E4;20;1);"*"); 'D:\Users\09249565\Desktop\Test\[Herstellernummer.xls.xlsx]Herstellernummer'!$A:$A;0);3);"");""))
Zur Erklärung:
Mit der ersten Wenn Abfrage stelle ich fest, ob mehr als 20 Zeichen in der Artikelnummer stehen. dies muss ich machen, da ich nur 20 Zeichen im weiteren Schritt auflöse.
Mit der Wennfehler Abfrage fange ich Fehlermeldungen wegen nicht vorhandenen Zeichenfolgen ab und gebe "" (kein Zeichen) aus.
Mit der Nächsten Wenn Abfrage stelle ich fest ob in der Zelle E4 (E5,E6...) etwas Drinsteht. wenn nicht: ""
Dann kommt meine Matrixabfrage. Wenn in der Tabelle Herstellernummer der Wert vorhanden ist, soll die Zelle 3 (SAP Nummer) ausgegeben werden.
Dazu mache ich eine Suche und löse die Artikelnummer mit * auf, da es vorkommen kann das die Nummer bei der Eingabe mit Leerzeichen und/oder / und oder/ - oder Buchstaben davor / danach eingegeben wurde.
Um es jetzt gleich zu sagen: Diese Eingabe unterliegt nicht meiner Kontrolle und wird von ca.:100 verschiedenen Leuten eingegeben. Eine Änderung von über 100.000 Einträgen ist nicht möglich.
Das einzige was ich mache ist die Spalte A in der Tabelle Herstellernummer über Daten-Datentools -Text in Spalten-"Fertig stellen" in Zahlen zu wandeln.
Sonst Funktioniert die Suche nicht.
Ich habe die Tabelle Herstellernummer etwas eingedampft um die Größe zu verringern.
Mein Problem ist das Excel nur Nummern findet, die Zeichen dazwischen haben.
Wenn also eine Herstellernummer ohne Zwischenzeichen wird nicht erkannt.
Ein * (Asterix) sollte aber ein Platzhalter für eine beliebige Anzahl Zeichen oder keine sein.
Die ersten beiden Nummern in der ET Liste mit SAP (E4, E5) werden beide in der Zeile 42993 der Herstellernummer gefunden. Das ist aber nicht das gewünschte Ergebnis. Sie sollten eigentlich in der Zeile 6199 oder 35549 sowie 6200 oder 35550 zu finden sein.
Die Herstellernummer kann entweder in der Spalte A oder in der Spalte D stehen.
So jetzt habe ich euch mit Infos überhäuft.
ich hoffe trotzdem das mir jemand von euch helfen kann.
Falls jetzt jemand mit VBA kommt, da habe ich leider nur rudimentäre Erfahrung, bin aber auch dafür bereit wenn es sein muss.
Könnte aber komplizierter werden (mir das zu erklären :-) )
Vielen Dank schon mal vorab.
Ich versuche die beiden Dateien hochzuladen, schaffe es aber nicht.
Immer wenn ich Zum Fileupload klicke kommt eine Seite 404
Wer kann mir helfen?
Danke

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Suche findet Werte nicht
21.10.2019 13:30:00
Günther
Moin,
ohne die Muster-xlsx ist es wirklich eine große Rätselstunde. Wenn das gar nicht klappt kannst du ja einen Upload auf OneDrive in deine Überlegungen einbeziehen ...
Gruß
Günther
AW: Suche findet Werte nicht
21.10.2019 13:39:01
B.
Ich bin gerade dabei die Dateien einzudampfen.
Ich habe gerade gemerkt das noch eine Bedingte Formatierung die Datei auf über 2MB aufgebläht hat.
Jetzt die 2 Dateien
ET Liste mit SAP: = Arbeitsliste
https://www.herber.de/bbs/user/132630.xlsx
Herstellernummer: = DatenListe
https://www.herber.de/bbs/user/132631.xlsx
Anzeige
AW: Suche findet Werte nicht
21.10.2019 14:37:19
Günther
Nun ja, VBA wäre gewiss meine erste Wahl. Insbesondere bei diesem irrsinnigen Gewusel an Eingabe-Möglichkeiten. Und ein Unternehmen dieser Größe sollte doch die Möglichkeit haben, externe Dienstleistung auch zu ordern und zu entlohnen.
Die zweite denkbare Möglichkeit sehe ich in Power Query (ist integriert -> Daten | Abrufen und transformieren). Aber einerseits fehlen mir dazu Infos, was dann wirklich wie gewertet werden soll und andererseits wäre mir das für die "notleidende Industrie" entschieden zu viel Aufwand im Rahmen der kostenlosen Foren-Hilfe.
Aber PQ ist lernbar, begreifbar! Ich kenne da jemanden sehr gut, der mit 71 Lenzen damit angefangen hat und sich jetzt nach 3 Jahren bereits recht gut damit auskennt. Einschließlich der wichtigen Funktionen der Formelsprache M.
Gruß
Günther
Anzeige
AW: Suche findet Werte nicht
21.10.2019 17:09:42
B.
Leider ist es bei "großen" Unternehmen üblich das für so kleine Lichte wie mich keine Gelder für solche Aufgaben "locker" gemacht wird.
Da Ich dies auch nur für mich und meine Arbeit nutzen kann, ist es ein NoGo. Dafür bin ich zu klein.
Da mich das ganze aber auch Interessiert und ich etwas dazulernen möchte, habe ich mein Problem hier eingestellt. Eine ähnliche Abfrage habe ich auch noch in anderen Tabellen vor zu machen.
Ich dachte das es vielleicht eine Befehlsfolge gibt, die mein Problem lösen würde.
Wie man an der Formel erkennen kann, bin ich zwar ambitioniert dazuzulernen, kenne aber nicht alle Befehle.
Auch mit VBA fange ich gerade an mich anzufreunden, bin aber auch dort noch nicht soweit um das Problem für mich zu lösen.
Wenn ich weiss wie das funktioniert kann ich es im Transfer auch für andere Dinge nutzen, daher meine Frage hier.
Vielen Dank
Anzeige
AW: Suche findet Werte nicht
21.10.2019 16:37:16
B.
Arbeitsdatei:
https://www.herber.de/bbs/user/132630.xlsx
Datendatei:
https://www.herber.de/bbs/user/132631.xlsx
AW: Suche findet Werte nicht
21.10.2019 17:09:29
Daniel
Hi
1. verwende die Funktion VERKETTEN anstelle von TEXTKETTE, dann funktioniert die Formel auch auf älteren Excelversionen.
2. wenn du den Suchbegriff mit Jokern anreicherst, ist er immer Text. Vergleich und SVERWEIS unterscheiden jedoch streng zwischen Text und Zahl, dh der Text "123" ist nicht das selbe wie die Zahl 123 und ergibt keinen Treffer
dh du musst darauf achten, dass die Spalte A im Blatt Herstellernummern Text ist und Text bleibt. (und ggf zu Text wird)
3. ich halte es führ gefährlich, zwischen jedem Zeichen einen * einzufügen. Insbesondere dann, wenn in der Spalte A mehrere Nummern stehen, bekommst du auch einen Treffer, wenn die Ziffern der gesuchten Nummer in der richtigen Reihenfolge über den gesamten Text verteilt sind, und genau das passiert bei dir, du bekommst einen völlig falschen Treffer, weil der gesuchte Wert als Zahl in der Liste steht und somit nicht gefunden wird, dafür aber die Ziffernfolge in der Zeile 6 vorkommt.
ich würde hier so vorgehen und mit verschiedenen Prüfschärfen arbeiten, dh in der ersten Stufe prüfst du, ob der gesuchte Wert direkt vorhanden ist, in der zweiten mit Jokern nur am Anfang und Ende und in der dritten Stufe dann mit Jokern zwischen drin. Wobei ich mir hier zusätzlich eine Warnmeldung ausgeben würde, denn in diesem Fall musst du das Ergebnis nochmal manuell überprüfen.
Prinzipell muss die Formel dann so aussehen:
=WennFehler(Index(xxx;Wennfehler(Vergleich(E4;aaa;0);Wennfehler(Vergleich("*"&E4&"*";aaa;0); Vergleich(Textkette(...);aaa;0));3);"nicht gefunden")
Gruß Daniel
Anzeige
AW: Suche findet Werte nicht
21.10.2019 17:14:07
B.
Danke Daniel.
Ich werde morgen versuchen damit klarzukommen. Egal wie es ausgeht, werde ich sobalt ich ein Ergebniss habe, mich melden.
Danke
AW: Suche findet Werte nicht
21.10.2019 17:39:58
Piet
Hallo
ich habe mir die Dateien mal zum ansehen herunter geladen, habe aber noch keinen Durchblick!
Wo stehen die Ursprungsdaten? In der Arbeits oder Datendatei? In der Arbeitsdatei sehe ich drei Eintraege, ohne erkennen zu können wie die zustande kommen. Dazu zweimal dieselbe SAP Numnner!
Wenn alle Schreibweisen als Beispiel genau erklaert werden, mit Lösung wie es aussehen soll, waere die Aufgabe von der Logik her leichter zu versthen. Bei Filter 795031060 P11543 kann ich z.B. bicht erkennen wo die Endung P11543 herkommt, oder ob das für die Lösung relevant ist?
Aus 8-745-80-1185 z.B. das Messer erkennen kann man mit VBA auswerten. Aber mir fehlt im Augenblick noch der Durchblick was genau wie ausgewertet werden muss?
mfg Piet
Anzeige
AW: Suche findet Werte nicht
22.10.2019 11:34:31
B.
Hallo Piet,
Danke für dein Interesse.
Also die Arbeitsdatei "ET Liste mit SAP" ist die mit den 3 Einträgen.
Die 3 Nummern in der Artikelnummer ist mit der "Hand" (Copie Paste) aus einem Angebot eingetragen.
Eigentlich sollten bei den ersten beiden Einträgen die SAP Nummern unterschiedlich sein.
Sind sie aber nicht.
Die SAP Nummer soll sich ihre Daten aus der Datentabelle "Herstellernummer" ziehen.
Diese ist im Original mit ca. 120.000 Zeilen zu groß zum heraufladen. Daher habe ich sie sehr stark verkürzt.
Ich kann aber gerne noch ein paar Zeilen einfügen wenn du möchtest.
Die Lieferantenmaterialnummer wird im SAP eingetragen. Sie kann aus verschiedenen Zeichenfolgen bestehen, die von SAP in der Tabelle zusammen geführt werden.
Bei dieser Nummer 795031060 P11543 ist der erste Teil 795031060 aus einem Angebot von Firma x. Später wurde uns das gleiche Teil von der Firma x mit der Nummer P11543 angeboten. Daher stehen beide Nummern in der Zelle. Wird uns das gleiche Teil von Firma y angeboten, steht es in einer anderen Zeile mit der gleichen SAP Nummer (Material) und dem gleichen Materialkurztext aber einem anderen Lieferant.
Da ich in meiner Arbeitstabelle "ET Liste mit SAP" und im weiteren Arbeitsverlauf nur mit der SAP Nummer weiterarbeite, benötige ich die "Umschlüsselung" von der Artikelnummer (egal welcher Hersteller) auf die SAP Nummer.
Dies soll in der Arbeitstabelle geschehen.
Später füge ich noch den Lagerort und Menge und andere Dinge ein. Die Suche danach funktioniert auch einwandfrei.
Nur die Umschlüsselung von Artikelnummer die entweder in der Tabelle Herstellernummer in der Spalte "A" steht oder im Text der Spalte "D"
Der Text der Spalte D setzt sich aus der Artikelbezeichnung, sofern vorhanden EINER (der ersten bekannten) Artikelnummer und Daten zur Größe, Material oder anderen Dimensionen.
Bekommen wir jetzt von einem Lieferanten eine "Neue" Bestellnummer, wird diese (über SAP) in die Spalte A mit eingefügt.
Daher kann es dazu kommen, das mehrere Nummern in einer Zelle stehen.
Mir geht es Darum die Materialnummer des Herstellers mit unserer SAP Materialnummer abzugleichen. Und das möglichst zuverlässig.
Ich hoffe ich konnte alle Fragen umfassend beantworten
Danke
Anzeige
AW: Suche findet Werte nicht
26.10.2019 20:51:14
Piet
Hallo
ich schicke dir eine Beispieldatei zum Testen. Die hat es sicher in sich!
Obwohl es ein Erstversuch ist könnte es ein guter Treffer sein. Ich hoffe du bist zufrieden.
Die Datei heisst bei mir "Auswerte Datei", und ist völlig unanbhaengig von beiden Originalen!
D.h., kopiere bitte den gesamten Datensatz aus der Datedatei (Hersteller) ins Beispiel und kopiere aus der "Arbeitsdatei" alle bereits gefundenen SAP Nummern in die Tabelle "Arbeitsblatt". Dann starte den Button "Bisherige Herstellernummer markieren". Das Makro markiert alle gefundenen Daten in der Hersteller Tabelle. ZUM LÖSCHEN - diese Daten sind einfach überflüssig zum weitersuchen!
Weil es nicht deine Original Datei ist, sondern eine reine Auswertung Datei, kannst du sie löschen! Danach solltest du in der Herstellerliste zuerst die Material Nummer generieren! Ich bin gespannt wieviele das Programm als gültige Datensaetze generieren kann. Bei unklaren Daten wird das angezeigt. Im Beispiel siehst du bereits einige Fehlermeldungen.
Vorteil ist, das du die Notizen Spalten sortieren kannst. Ein grosser Pluspunkt zum bearbeiten. Dann kannst du in "Tabelle 1" die Daten gezielt auswerten lassen. Das Makro kann vorhandenen SAP Nummern die Material Nummer zuordnen, oder aus der Herstellerliste alle weiteren SAP Nummern heraussuchen und unten anhaengen. Neu ist die Lieferanten Spalte, damit ich doppelte SAP Nummern unterscheiden konnte!
Fertig Datensaetze kannst du bitte von Hsnd in dein Original kopieren. Das war mir zuviel Arbeit.
Hinweis:
Ich weiss nicht wie lange das Programm bei 100.000 Daten laueft? Du solltest es anfangs mal mit 5-10.000 Daten versuchen. Die Zeit bekommst du seitlich angezeigt. Es bleibt dir überlassen weiviele Daten du ins Beispiel kopierst. Nun ja, war viel Arbeit, würde mich freuen wenn es in der Praxis gut klappt. Ich bin gespannt ....
Die Spaltenbreite musste ich verkleinern. Hatte leider nur einen 12 Zoll XP Laptop zur Verfügung!
https://www.herber.de/bbs/user/132780.xlsm
mfg Piet
Anzeige

305 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige