Vergleich von 2 Listen und Ausgabe von Mehreren Werten zu einem gesuchten Wert

[Luebeck]

Hallo an Alle,

ich habe eine Liste, auf dem mehrere Artikel aufgelistet sind. Ich gebe mal am besten ein Beispiel:

Artikelnummer              Farbnummer                      Merkmalname                           Merkmalwertname
999999999-01                      01                              Montage                                    lose
999999999-02                      02                              Höhe                                          100m
999999987-03                      03                              Breite                                         50m
...


Jeder Artikel kommt in unterschiedlichen Farben vor. Und jeder Artikel hat eine unterschiedliche Anzahl an Merkmalen. Ich will aber, dass jeder Artikel mit der gleichen Artikelnummer unabhängig von der Farbe die gleiche Anzahl an Merkmalen hat.

Zunächst habe ich eine Spalte eingebaut, in dem ich die letzten beiden Ziffern der Artikelnummer rausgeschnitten habe (mit der Funktion Rechts). Dann habe ich auf einem separaten Tabellenblatt die Artikelnummer mit den Merkmalen aufgelistet, sodass jeder Artikel mit dem entsprechenden Wert nur einmal vorkommt (Daten-> Duplikate entfernen). Um sicherzustellen, dass jeder Artikel in der gleichen Menge vorkommen soll habe ich die Häufigkeit der einzelnen Artikelnummer mit Farbe ermittelt:

Artikelnummer              Häufigkeit                     Merkmalname                   Häufigkeit              Artikelnummer        Merkmalwertname

999999999-01                      14                              Montage                       9                             999999999                  lose
999999999-02                      5                               Höhe                           2                             999999999                  100m
999999987-03                      7                               Breite                          7                             999999987                  50m
...


Auf diese Weise konnte ich sehen, welche Artikel nicht in der gewünschten Menge eingepflegt sind. D. h. Ich habe einmal die Häufigkeit der Artikel in verschiedenen Farben aufgelistet (Wie oft kommt der Artikel 999999999 in Farbe 01 vor? ) und die Häufigkeit der Artikelnummer 999999999 (ohne Farbberücksichtigung, d. h. eigentlich die Gesamtsummer der gesamten Farbmengen).
Anhand der Wenn-Funktion habe ich ausgegeben, welche Zeilen vervielfacht werden müssen (es sind nicht wenige). Hierzu würde ich auch gerne wissen, wie man am schnellsten zeilen vervielfachen kann? Gibt es hierzu eine Formel? Denn es sind paar tausende Zeilen...

Und nun kommt die Hauptfrage:

Wie kann ich aus meiner Datei, in dem die Artikelnummer mit den Merkmalen aufgelistet sind, mehrere Merkmale auflisten???
Mit Sverweis bekomme ich nur ein Merkmal, aber ich will ja alle Merkmale zu dem Artikel..

So sieht die Tabelle aus:

Artikelnummer              Häufigkeit                     Merkmalname                   Häufigkeit              Artikelnummer        Merkmalwertname

999999999-01                      14                              Montage                       9                             999999999                  lose
999999999-02                      5                               Höhe                             2                             999999999                  100m
999999999-03                      7                               Breite                            7                             999999999                 50m
...



Laut meiner Merkmalliste auf einem anderen Tabellenblatt hat zum Beispiel der Artikel 999999999 folgende Merkmale:

Artikel
999999999                                             Serie

999999999                                             Design
999999999                                             Durchmesser
999999999                                             Material
999999999                                             Ausladung
999999999                                             Montage
999999999                                             Breite
999999999                                             Höhe

Die letzten drei Merkmale (Montage, Breite und Höhe) sind bereits in meiner Liste vorhanden. Wie kann ich aber die fehlenden Merkmale abfragen? Mit SVERWEIS bekomme ich zum Beispiel die erste Eigenschaft Serie, aber nicht den Rest...

Ich hoffe, dass ich das verständlich formuliert habe..und würde mich über einen hilfreichen Tipp sehr freuen

Vielen Dank

[WillWissen]

Moin,

lade bitte eine anonymisierte Beispieldatei hoch, deren Aufbau identisch mit deinem Original ist und trage händisch ein paar Wunschergebnisse ein. https://www.clever-excel-forum.de/Thread...ng-stellen

[Luebeck]

Hallo an Alle,
Hallo @WillWissen,

wie gewünscht stelle ich nun eine Beispieldatei zur Verfügung. Es handelt sich um eine Excel-Datei, die sich aus den zwei Tabellenblättern „Liste“ und „Merkmale“ zusammensetzt. Darin werden Artikel in verschiedenen Farben aufgeführt. Das Problem ist, dass die gleichen Artikel in verschiedenen Farben nicht alle Merkmale besitzen, z. B. hat der Artikel mit der Artikelnummer 111 in Rot drei Merkmale, während der gleiche Artikel in blau alle verfügbaren fünf Merkmale besitzt. Ich möchte aber jeden Artikel mit der gleichen Artikelnummer in verschiedenen Farben alle verfügbaren Merkmale zuordnen. So soll für den Artikel mit der Artikelnummer 111 in blau und in rot die gleichen Merkmale aufgelistet werden. Die Datei, die ich bearbeite, hat über 10 000 Datensätze.


Folgendermaßen bin ich vorgegangen:
Zuerst habe ich die die Spalten „Artikelnummer mit Farbe“, „Merkmalname“, „Merkmalwertname“ und „Artikelnummer ohne Farbe“ in das Tabellenblatt „Merkmale“ kopiert:


In dieser Liste wird jeder Artikel in jeder Farbe nur einmal mit dem zugeordnetem Merkmal aufgelistet.
Rechts neben dieser Auflistung habe ich in einer weiteren Tabelle die Artikelnummer (ohne Farbe) mit den vorkommenden Merkmalen aufgelistet. Auch hier sind Duplikate ausgeschlossen:



In einer weiteren Tabelle rechts habe ich für jede Artikelnummer (ohne Farbe) die Menge der Merkmale berechnet. Die Berechnung habe ich mit der ZÄHLENWENN()-Funktion auf Grundlage der vorherigen Tabelle links (Auflistung der Artikelnummer ohne Farbe mit den Merkmalen) durchgeführt.

[
Bild bitte so als Datei hochladen: Klick mich!
]
(Abbildung 4)

In der letzten Tabelle habe ich die Merkmale der einzelnen Artikel mit Farbe gezählt. Auch hier habe ich die ZÄHLENWENN-Funktion auf die erste Tabelle (erste Auflistung) angewendet.

[
Bild bitte so als Datei hochladen: Klick mich!
]
(Abbildung 5)


Im nächsten Schritt habe ich nun diese Ergebnisse aus den beiden Tabellen Abbildung 4 und Abbildung 5 in die Spalten „Anzahl der Merkmale: Artikel mit Farbe“ (=Ergebnisse der Tabelle aus Abbildung 5) und „Anzahl der Merkmale: Artikel ohne Farbe“ (=Ergebnisse der Tabelle aus Abbildung 4 ) im Tabellenblatt „Liste“ zugeordnet. Dazu habe ich die SVERWEIS()-Funktion in Anspruch genommen:
 

Abschließend habe ich mit der WENN()-Funktion in der Spalte „PRÜFUNG“ geprüft, ob die Menge der Merkmale der „Artikel ohne Farbe“ mit der Menge der „Artikel mit Farbe“ übereinstimmen. Folgendes Ergebnis liegt vor:

[
Bild bitte so als Datei hochladen: Klick mich!
]
 (Abbildung 7)

Nach dieser Prüfung hatte ich einige tausende Zeilen mit dem Ergebnis „FALSCH“, d. h. Merkmalmenge der Artikel ohne Farbe stimmt mit der Merkmalmenge der Artikel mit Farbe nicht überein. In diesem Fall muss ich manuell Zeilen einfügen, die fehlenden Merkmale ermitteln. Und das muss bei einigen tausenden Datensätzen durchgeführt werden.

Folgende Fragen sind offen:
1.)    Wie bzw. mit welcher Formel kann ich die notwendigen Zeilen einfügen?
2.)    Wie kann ich Prüfen, welche Merkmale fehlen? Und wie kann ich die fehlenden Merkmale ergänzen?
Ich hoffe, dass ich einen guten Ansatz bekomme.

Und kann mir jemand ein Hinweis geben, wie ich die Bilder im Text einfügen kann? Ich will die Bilder nachträglich einfügen. Es hat nicht geklappt, weil ein Link angefordert wird. Wer aber sich die Exceldatei anschaut, wird meine Vorgehensweise bestimmt nachvollziehen.

Vielen Dank im Voraus und LG

[Käpt'n Blaubär]

Hallo,

ich lese zunächst nur, daß Du in Deiner Datei eine mächtige Farbklekserei verwirklichen willst.
Was ich von den gestellten Forderungen verstanden habe, wird das wahrscheinlich nicht ohne
VBA machbar sein.
Ich schreibe "wahrscheinlich", weil die Formel-Spezies mich schon das eine oder andere Mal
ziemlich verblüffen konnten und ich sicher bin, das meine Formelkünste nicht reichen würden  :05:

[WillWissen]

Moin,

Und kann mir jemand ein Hinweis geben, wie ich die Bilder im Text einfügen kann?

hochladen genauso wie deine Datei, den Cursor an die Stelle im Text platzieren, an der das Bild eingefügt werden soll und noch rechts noch den Button "In den Beitrag einfügen" anklicken.

[shift-del]

Moin

Ich sehe im wesentlichen zwei Listen: eine mit Artikelbezeichnungen und eine mit Merkmalsangaben.
Welche der beiden soll nun erweitert werden?

Es wäre hilfreich gewesen ein klares Wunschergebnis (vorher/nachher) anzugeben.

Verwirrungs-Index: 0,7

[Luebeck]

Hallo an Alle,
Hallo @WillWissen,

wie gewünscht stelle ich nun eine Beispieldatei zur Verfügung. Es handelt sich um eine Excel-Datei, die sich aus den zwei Tabellenblättern „Liste“ und „Merkmale“ zusammensetzt. Darin werden Artikel in verschiedenen Farben aufgeführt. Das Problem ist, dass die gleichen Artikel in verschiedenen Farben nicht alle Merkmale besitzen, z. B. hat der Artikel mit der Artikelnummer 111 in Rot drei Merkmale, während der gleiche Artikel in blau alle verfügbaren fünf Merkmale besitzt. Ich möchte aber jeden Artikel mit der gleichen Artikelnummer in verschiedenen Farben alle verfügbaren Merkmale zuordnen. So soll für den Artikel mit der Artikelnummer 111 in blau und in rot die gleichen Merkmale aufgelistet werden. Die Datei, die ich bearbeite, hat über 10 000 Datensätze. Meine Liste mit en Daten, die ich bearbeite, sieht so aus:



Folgendermaßen bin ich vorgegangen:
Zuerst habe ich die die Spalten „Artikelnummer mit Farbe“, „Merkmalname“, „Merkmalwertname“ und „Artikelnummer ohne Farbe“ in das Tabellenblatt „Merkmale“ kopiert:


   

In dieser Liste wird jeder Artikel in jeder Farbe nur einmal mit dem zugeordnetem Merkmal aufgelistet.
Rechts neben dieser Auflistung habe ich in einer weiteren Tabelle die Artikelnummer (ohne Farbe) mit den vorkommenden Merkmalen aufgelistet. Auch hier sind Duplikate ausgeschlossen:

   



In einer weiteren Tabelle rechts habe ich für jede Artikelnummer (ohne Farbe) die Menge der Merkmale berechnet. Die Berechnung habe ich mit der ZÄHLENWENN()-Funktion auf Grundlage der vorherigen Tabelle links (Auflistung der Artikelnummer ohne Farbe mit den Merkmalen) durchgeführt.

   

In der letzten Tabelle habe ich die Merkmale der einzelnen Artikel mit Farbe gezählt. Auch hier habe ich die ZÄHLENWENN-Funktion auf die erste Tabelle (erste Auflistung) angewendet.

   


Im nächsten Schritt habe ich nun diese Ergebnisse aus den beiden Tabellen Abbildung 4 und Abbildung 5 in die Spalten „Anzahl der Merkmale: Artikel mit Farbe“ (=Ergebnisse der Tabelle aus Abbildung 5) und „Anzahl der Merkmale: Artikel ohne Farbe“ (=Ergebnisse der Tabelle aus Abbildung 4 ) im Tabellenblatt „Liste“ zugeordnet. Dazu habe ich die SVERWEIS()-Funktion in Anspruch genommen:
 
   

Abschließend habe ich mit der WENN()-Funktion in der Spalte „PRÜFUNG“ geprüft, ob die Menge der Merkmale der „Artikel ohne Farbe“ mit der Menge der „Artikel mit Farbe“ übereinstimmen. Folgendes Ergebnis liegt vor:

   

Nach dieser Prüfung hatte ich einige tausende Zeilen mit dem Ergebnis „FALSCH“, d. h. Merkmalmenge der Artikel ohne Farbe stimmt mit der Merkmalmenge der Artikel mit Farbe nicht überein. In diesem Fall muss ich manuell Zeilen einfügen, die fehlenden Merkmale ermitteln. Und das muss bei einigen tausenden Datensätzen durchgeführt werden.

Ich gehe nochmal auf das Detail ein: Nach meiner Mengenprüfung der Merkmale liegt folgendes Problem vor:

   

Die Artikel 80000000-08 und 80000000-09 haben nur 1 Merkmal von 7 Merkmalen (siehe z. B. Artikel 80000000-06). Ich muss für mehrere solche Artikel die Anzahl der Zeilen anpassen und die fehlenden Merkmale ergänzen.

So soll das korrigierte Ergebnis aussehen:

   


Folgende Fragen sind offen:
1.)    Wie bzw. mit welcher Formel kann ich die notwendigen Zeilen einfügen?
2.)    Wie kann ich Prüfen, welche Merkmale fehlen? Und wie kann ich die fehlenden Merkmale ergänzen?
Ich hoffe, dass ich einen guten Ansatz bekomme.

Und kann mir jemand ein Hinweis geben, wie ich die Bilder im Text einfügen kann? Ich will die Bilder nachträglich einfügen. Es hat nicht geklappt, weil ein Link angefordert wird. Wer aber sich die Exceldatei anschaut, wird meine Vorgehensweise bestimmt nachvollziehen.

Vielen Dank im Voraus und LG[/quote]

---

Habe alles mit Bildern aktualisiert und zum Schluss auch das Wunschergebnis eingefügt

[shift-del]

Ich biete eine PQ-Lösung.

Ich habe das nur für das Beispiel 80000000er-Artikel gemacht. Für andere Artikel gab es ja kein Wunschergebnis.

[Luebeck]

Ich biete eine PQ-Lösung.

Ich habe das nur für das Beispiel 80000000er-Artikel gemacht. Für andere Artikel gab es ja kein Wunschergebnis.

Vielen Dank, aber soll ich diese Tabelle jetzt bei mehr als 10 000 Daten fortsetzen :20: Ich habe eine Excel-Datei mir mehr als 10 000 Datensätzen und muss die fehlenden Merkmale zuordnen

[shift-del]

Du kannst die beiden Tabellen entsprechend ergänzen und dann den Query aktualisieren.
Keine Ahnung ob das Ergebnis dann noch deinen Vorstellungen entspricht.