Spalten abgleichen & Werte übernehmen

Bild

Betrifft: Spalten abgleichen & Werte übernehmen
von: DanielMa
Geschrieben am: 16.11.2015 09:33:15

Guten Morgen liebes Forum,
erneut benötige ich Hilfe bei einem Excelthema.
Meine Ausgangslage:
Ich habe drei Tabellenblätter, in der jeweils eine Artikelnummer geführt wird. Ich möchte abgleichen, ob diese auch in allen vorhanden ist.
Tabelle 1 dient als Referenz. Dort ist die Nummer in Spalte "F" ab Zeile 3 geführt (etwas über 1000 Einträge). Es soll in Tabelle 2 in Spalte "U" nach den Einträgen aus Tabelle 1 gesucht werden. Falls vorhanden, soll die entsprechende Zeile aus Tabelle 2 Spalte "B" in die entsprechende Zeile in Spalte "C" der Tabelle 1 kopiert werden. Genauso soll die Art.Nr. in Tabelle 3 in Spalte "D" gesucht werden. Bei einem passenden Eintrag soll die entsprechende Zelle aus Spalte "A" in die Spalte "D" der Tabelle 1 kopiert werden.
Beispiel:
Referenz ist Art.Nr. 123 aus Tabelle 1 Spalte "F" Zeile 3.
Diese wird nun in Spalte "U" der Tabelle 2 gesucht und in Zeile 10 gefunden. Jetzt möchte ich den Eintrag der Spalte "B" Zeile 10 kopieren und wieder zurück im Tabellenblatt 1 in Spalte "C" Zeile 3 einfügen.
Anschließend suche ich auch nach der Nr. 123 in Tabelle 3. Dort in Spalte "D". Ich finde die Nr. in der Zeile 5 und jetzt möchte ich die Zeile 5 Spalte "A" nach Tabelle 1 Zeile 3 Spalte "D" kopieren.
Es kann vorkommen, dass die gesuchte Art.Nr. mehrfach in Tabelle 2 oder 3 gelistet wird. Ich bin noch nicht sicher, wie man das abfangen kann. Eventuell sollte die Nr. in Tabelle 1 dann einfach gefärbt werden, damit ich mir die Beispiele manuell vornehmen kann.
Ist mein vorhaben mit einer einfachen Excel-Formel umsetzbar? Eventuell S-Verweis?
Ich bin leider nicht sicher, wie ich das am effektivsten aufbauen kann.
Ich freue mich über eure Hilfe und wünsche euch einen guten Start in die neue Woche.
Lieber Gruß, Daniel

Bild

Betrifft: AW: es würde hier..
von: Bernd
Geschrieben am: 16.11.2015 09:34:40
Hallo,
sicherlich eine Beispielmappe weiter helfen.
mfg Bernd

Bild

Betrifft: AW: dazu nutze INDEX() und VERGLEICH() ...
von: ... neopa C
Geschrieben am: 16.11.2015 10:31:33
Hallo Daniel,
... weil SVERWEIS() nur nach rechts suchen kann.
Zu INDEX() und VERGLEICH() sieh hier: http://www.online-excel.de/excel/singsel.php?f=39
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dazu nutze INDEX() und VERGLEICH() ...
von: DanielMa
Geschrieben am: 16.11.2015 11:24:44
Hallo Bernd und Werner,
schonmal vielen Dank für eure Anregungen.
Ich habe eine Beispielmappe erstellt, die genau den Aufbau aufweist wie mein Original. Für die Formel unwichtige Zellen sind als "unwichtig" gekennzeichnet ;)
Weiterhin habe ich mir Werners Tipp zu Herzen genommen und mal mit Index & Vergleich rumgespielt.
Scheinbar habe ich noch einen Denkfehler, wobei der letzte Eintrag (107 bei Art.Nr. 10) richtig übernommen wird.
Was habe ich falsch gemacht?
https://www.herber.de/bbs/user/101549.xlsx
Vielen Dank und Gruß,
Daniel

Bild

Betrifft: hinten gehört 0 hin
von: WF
Geschrieben am: 16.11.2015 11:48:52
Hi,
in C2:
=INDEX(Tabelle2!B$2:B$8;VERGLEICH(Tabelle1!F2;Tabelle2!U$2:U$8;0))
runterkopieren
WF

Bild

Betrifft: AW: zusätzlich noch WENNFEHLER() ...
von: ... neopa C
Geschrieben am: 16.11.2015 11:52:24
Hallo Daniel,
... in C2: =WENNFEHLER(INDEX(Tabelle2!B:B;VERGLEICH(F2;Tabelle2!U:U;0));"") und Formel ziehend nach unten kopieren.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: zusätzlich noch WENNFEHLER() ...
von: DanielMa
Geschrieben am: 16.11.2015 12:25:39
Vielen Dank WF und Werner,
das funktioniert schonmal super! Endlich habe ich auch das Prinzip von Index und Vergleich verstanden, das wird mir sicherlich noch sehr weiterhelfen :) Danke dafür :)
Nochmal zurück:
Wie in der Beispieldatei gekennzeichnet kann es vorkommen, dass eine Art.Nr. mehrfach vorkommt. Hier ist es jetzt die "7", die in Tabelle 2 dann 2x aufgeführt wird. Momentan wird dann einfach der erste gefundene Wert angenommen.
Kann man hier auch eine Bedingung mit einbauen, dass wenn mehrere Einträge gefunden werden, die Zelle in Tabelle 1 farblich markiert wird?
Gruß,
Daniel

Bild

Betrifft: AW: zusätzlich bedingte Formatierung ...
von: ... neopa C
Geschrieben am: 16.11.2015 12:44:37
Hallo Daniel,
... so wie Du schreibst, bei markierten Zellbereich C2:C### folgende bedingte _ Formatierungsformel:


=ZÄHLENWENN(Tabelle2!U:U;F2) >1 
zuordnen und dafür eine entsprechende Hintergrundzellfarbe zuweisen. Mehr zur bedingten Formatierung sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=74 und ff.
Gruß Werner
.. , - ...

Bild

Betrifft: mal wieder vielen Dank!
von: DanielMa
Geschrieben am: 16.11.2015 12:52:31
Hallo Werner,
erneut ein großes Dankeschön an dich! Du hast mir nun schon mehrmals mit tollen Tipps weitergeholfen :)
Hab noch einen schönen Tag und auch Danke! an alle anderen Helfer :)
Gruß,
Daniel

Bild

Betrifft: AW: mal wieder vielen Dank!
von: DanielMa
Geschrieben am: 16.11.2015 15:19:27
Hallo Werner,
abschließend eine Frage:
Die Formel funktioniert einwandfrei, ebenso die bedingte Formatierung. Aber:
In Tabelle 3 sind die Art.Nr. als Standard formatiert und stehen linksbündig in der Zeile. Die in Tabelle 1 eingegebene Formel findet jetzt keine Werte in dieser Spalte der Tabelle 3! Klicke ich eine Art.Nr. aus der Tabelle 3 an, klicke oben ins Eingabefeld (neben dem fx) und bestätige mit Enter, so wird die Art.Nr. rechtsbündig und kann plötzlich von der Formel ausgelesen bzw. abgeglichen werden. Fällt dir oder euch spontan etwas dazu ein? Ich verändere dadurch doch nicht die Formatierung? Natürlich könnte ich jetzt jede Zeile anklicken und mit Enter rechtsbündig machen, aber da muss ja irgendein Fehler vorliegen, den ich gerade übersehe..
Viele Grüße,
Daniel

Bild

Betrifft: AW: dies kann ich so nicht nachvollziehen ...
von: ... neopa C
Geschrieben am: 16.11.2015 15:43:23
Hallo Daniel,
... denn in der hoch geladenen Datei stehen die Art.Nr in Tabelle 3 rechtsbündig und sind auch richtige Zahlenwerte. Demzufolge ergibt auch folgende Formel in Tabelle1:
=WENNFEHLER(INDEX(Tabelle3!A:A;VERGLEICH(F2;Tabelle3!D:D;0));"") korrekte Ergebnisse.
So wie Du es beschreibst, stehen in Deiner Originaldatei in Tabelle3!D:D als Textwerte formatierte Zahlen. Durch die von Dir beschriebene Aktion kannst Du diese Zellenwerte wieder einzeln zu echten Zahlenwerten wandeln oder in dem Du in einem Ritt diesen Zellbereich mit den Text formatierten Zahlenwerten mit einer 1 multiplizierst. Die dritte Alternative wäre die Auswertung der Text formatierten Zahlenwerte mit folgender Formel: =WENNFEHLER(INDEX(Tabelle3!A:A;VERGLEICH(F2&"";Tabelle3!D:D;0));"")
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dies kann ich so nicht nachvollziehen ...
von: DanielMa
Geschrieben am: 17.11.2015 10:26:50
Hallo Werner,
das Original kommt als Excelexport aus einer Anwendung. Hierbei wurde alles als Text markiert. Ich habe die Spalte angewählt und auf Standard gesetzt, jedoch waren die Werte dennoch linksbündig.
Mit deinen Vorschlägen konnte ich das ganze jedoch zum laufen bringen :)
Danke und einen schönen Tag.
Viele Grüße,
Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Spalten abgleichen & Werte übernehmen"