Mar 04, 2010, updated at 07:35:29 (UTC)

8091

Excel - Auslesen von Daten und Einfügen in anderes Arbeitsblatt

Hallo,
bin neu hier und habe ein Problem bezüglich einer Automation für Excel. Wäre euch für jede Hilfe dankbar, bin eigentlich einigermaßen IT-erfahren habe nur die letzten Jahre wenig in der Richtung gemacht.

Problem ist folgendes:
Es sollen aus 2 sich von der Zeilenanzahl verändernden Excelblättern die immer selben Daten ausgelesen werden und in ein drittes Datenblatt eingefügt werden.
Dabei geht es um einen Lieferantenvergleich, Problem dabei ist, da es sich um Gemüse/Obst handelt, das immer mal wieder Artikel dazu kommen und auch wieder rausfallen, ausgelesen soll aber nur der Stammkern, der immer in den beiden Tabellen vorkommt, werden. Dieser ist auch anhand sich nicht verändernder Artikelnummern identifizierbar, ich weiß leider nur nicht wie ich Excel beibringe auf die Artikelnummern, also den Wert und nicht auf Zelle/Spalte einzugehen, weil sonst würde ja der Sverweis gehen.
Natürlich soll auch der zur Artikelnummer gehörende Preis übertragen werden.

Hoffe ich habe mich verständlich ausgedrückt.

Danke für jede Hilfe

Stephan

Please also mark the comments that contributed to the solution of the article

Content-Key: 137371

Url: https://administrator.de/contentid/137371

Printed on: April 27, 2024 at 02:04 o'clock

21 Comments

Latest comment

Moin,

klingt nach einem Job für Mister VBA.
Kennst Du Dich mit VBA aus?

Stefan

Hallo,

danke für die schnelle Antwort. Zu deiner Frage, sagen wir mal so, ich habe Grundkenntnisse in VBA, aber ich hab leider seit ca. 4 1/2 Jahren nix mehr in VBA gemacht. Abschreiben kann ich aber

Grüße Stephan

Hallo StephanAmount!

Probiers mal damit:

Option Explicit
Option Compare Text

Const SheetNeu = "Tabelle1"     'Tabellenname Neu  
Const SheetAn1 = "Tabelle2"     'Tabellenname Artikel 1  
Const SheetAn2 = "Tabelle3"     'Tabellenname Artikel 2  

Const StartZeile = 2            'Startzeile Tabelle1  

Const SpalteArtNr = 1           'Spalte Tabelle2/3 Artikel-Nummer  
Const SpalteNamen = 2           'Spalte Tabelle2/3 Artikel-Bezeichnung  
Const SpaltePreis = 3           'Spalte Tabelle2/3 Preis  

Const SpalteN1 = 1              'Spalte Tabelle2 Namen  
Const SpalteA1 = 2              'Spalte Tabelle2 ArtNr  
Const SpalteP1 = 3              'Spalte Tabelle2 Preis  
Const SpalteA2 = 4              'Spalte Tabelle3 ArtNr  
Const SpalteP2 = 5              'Spalte Tabelle3 Preis  

Sub InitTabelleNeu()
    Dim Wks0 As Worksheet, Wks1 As Worksheet, Wks2 As Worksheet
    Dim NextLine As Long, c As Range, i As Long
    
    Set Wks0 = Sheets(SheetNeu)
    Set Wks1 = Sheets(SheetAn1)
    Set Wks2 = Sheets(SheetAn2)
    
    NextLine = StartZeile
    
    For i = StartZeile To Wks1.Cells(Rows.Count, SpalteNamen).End(xlUp).Row
        Set c = Wks2.Columns(SpalteNamen).Find(Wks1.Cells(i, SpalteNamen), LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            With Wks0
                .Cells(NextLine, SpalteN1) = Wks1.Cells(i, SpalteNamen)
                .Cells(NextLine, SpalteA1) = Wks1.Cells(i, SpalteArtNr)
                .Cells(NextLine, SpalteP1) = Wks1.Cells(i, SpaltePreis)
                .Cells(NextLine, SpalteA2) = Wks2.Cells(c.Row, SpalteArtNr)
                .Cells(NextLine, SpalteP2) = Wks2.Cells(c.Row, SpaltePreis)
                 NextLine = NextLine + 1
            End With
        End If
    Next
 End Sub

Wobei die Konstanten entsprechend angepasst werden müssen.

Den Quellcode im VB-Editor in ein Modul kopieren und mit <Extras><Makro><Makros><InitTabelleNeu> Makro starten.

Das Makro vergleicht die Namen der beiden Tabellen und bei Übereinstimmung, werden die entsprechenden Werte in die neue Tabelle geschrieben.

Gruß Dieter

[edit] geändert [/edit]

Hallo Dieter,

vielen Dank für das Script werde es nachher gleich mal testen und dir dann schreiben ob´s geklappt hat.

Beste Grüße aus München

Stephan

edit:

in dem Teil

Const SpalteArtNr = 1 'Spalte Artikelnummer
Const SpaltePreis = 2 'Spalte Preis (Aus Tabelle Artikel 2)

muss ich das nicht für Tabelle 1 und Tabelle 2 machen, war vielleicht von mir unverständlich ausgedrückt, die beiden Lieferanten haben verschiedene Artikelnummern, ich werde zu je einem Namen im zusammengefügten Sheet beide Artikelnummern angeben, also in der Art:
Name Art.Nr.L1 PreisL1 Art.Nr.L2 PreisL2

sorry danke

Hallo StephanAmount!

Wie jetzt? Oben hast Du geschrieben, die Artikel wären anhand der Artikelnummer indentifizierbar?

Ich bin bei meinem Beispiel davon ausgegangen, das es zwei Tabellen gibt, in denen sich in den gleichen Spalten die Artikel-Nr. und der Preis befindet und wenn in beiden Tabellen die gleiche Artikenummer vorkommt, dann sollen diese in das neue Tabellenblatt übernommen werden. Wie auch immer, am sinnvollsten wäre es, wenn Du mal ein explizites Ist/Soll-Beispiel postest.

Grüße aus Mannheim

Dieter

Hallo Dieter,

entschuldige, ich meinte das so:
Tabelle 1 Lieferant 1:
Art Nr. Name Preis
123 TOMATE 1,50

Tabelle2 Lieferant 2:
Art.Nr Name Preis
789 TOMATE 1,80

zusammengefügte Tabelle 3:
Name ArtNr L1 Preis L1 ArtNr L2 Preis L2
TOMATE 123 1,50 789 1,80

Sorry das ich mich falsch ausgedrückt habe, Lieferant 1 und Lieferant 2 haben verschiedene Artikelnummern, diese ändern sich aber nicht, und ich schreibe beide Artikelnummern in mein Ziel Sheet, und so sollen dann die zwei Preise ins Ziel Sheet kommen.
Hoffen ich hab mich diesmal besser ausgedrückt.

Grüße Stephan

eins noch, via dem Attribut Tomate lässt es sich leider nicht identifizieren da die Lieferanten hier oftmals leicht abweichende Bezeichnungen haben, wie z.B Wassermelone - Melone, Wasser

Hallo StephanAmount!

Aja, jetzt hab ich's auch verstanden, wobei mir nicht ganz klar ist, warum bei einem Lieferanten Tomaten die Bezeichnung Wassermelonen tragen

Code oben geändert.

Gruß Dieter

Tja, Wassermelonen und Tomaten ähneln sich manchmal

Auf jeden Fall:

ES FUNKTIONIERT!!!!

Vielen vielen Dank,
eine kleine letzte Frage habe ich aber noch, wenn des nicht geht auch net schlimm, aber Fragen kostet ja nix:
Wenn ich jetzt in Tabelle 1, also der wo alles zusammengefügt wird zwei Artikel nebeneinander machen möchte, damit die Liste nicht so lang wird, also Format:
Name Artnr1 P1 Artnr2 P2 Name Artnr1 P1 Artnr2 P2,
was müsste ich denn da bei deN Attributen ändern oder ist des net möglich, wäre nicht schlimm bin so schon sehr sehr glücklich

ein schönes Wochenende und Grüße aus dem sonnigen München

Stephan

Hallo Stephan!

Wenn ich das richtig verstehe, dann meinst Du eine Art Zeilenumbruch. D.h. nur bis zu einer festgelegten Zeile und dann wieder oben ein paar Spalten weiter beginnen.

Mit ein paar zusätzlichen Codezeilen läßt sich das auch machen

Das Problem mit den unterschiedlichen Artikelbezeichnungen könnte man auch lösen und je nach Größenordnung eine Extra-Tabelle oder ein einfaches Text-Array verwenden. Oder eine komplette Liste in einer Extra-Tabelle?

Beispiel Tabelle:

	A	B
18	Tomaten	Wassermelonen

Beispiel Array (pärchenweise):

GleicheArtikel = Array("Tomaten", "Wassermelonen", "Salat", "Grüne Blätter")

Machen kann man fast alles, die Frage ist dann immer nur DAS WIE

Wünsche auch ein schönes WE und Grüße aus Mannheim

Dieter

Hallo Dieter,

das mit den unterschiedlichen Artikelbezeichungen hab ich ganz einfach gelöst, ich hab die Bezeichnungsabfrage rausgenommen, da ich die Bezeichnung nur einmal vergebe in der Zieltabelle und diese nach dem Gusto des Küchenchefs aussehen soll

Nochmals vielen Dank, funktioniert wirklich ganz hervorragend

Beste Grüße

Stephan

Hallo Stephan!

Hier der neue Code mit Zeilenumbruch:

Option Explicit
Option Compare Text

Const SheetNeu = "Tabelle1"     'Tabellenname Neu  
Const SheetAn1 = "Tabelle2"     'Tabellenname Artikel 1  
Const SheetAn2 = "Tabelle3"     'Tabellenname Artikel 2  

Const StartZeile = 2            'Zeile Starten Tabelle1  
Const BreakZeile = 40           'Zeile Umbruch Tabelle1  
Const FreiSpalte = 1            'Spalte Anzahl Spalten freilassen Tabelle1  

Const SpalteArtNr = 1           'Spalte Tabelle2/3 Artikel-Nummer  
Const SpalteNamen = 2           'Spalte Tabelle2/3 Artikel-Bezeichnung  
Const SpaltePreis = 3           'Spalte Tabelle2/3 Preis  

Const SpalteN1 = 1              'Spalte Tabelle2 Name  
Const SpalteA1 = 2              'Spalte Tabelle2 ArtNr  
Const SpalteP1 = 3              'Spalte Tabelle2 Preis  
Const SpalteA2 = 4              'Spalte Tabelle3 ArtNr  
Const SpalteP2 = 5              'Spalte Tabelle3 Preis  

Sub InitTabelleNeu()
    Dim Wks0 As Worksheet, Wks1 As Worksheet, Wks2 As Worksheet
    Dim NextLine As Long, NextCols As Long, c As Range, i As Long
    
    Set Wks0 = Sheets(SheetNeu)
    Set Wks1 = Sheets(SheetAn1)
    Set Wks2 = Sheets(SheetAn2)
    
    Wks0.Rows(StartZeile & ":" & Wks0.Cells(Rows.Count, SpalteN1).End(xlUp).Row).ClearContents  
    
    NextLine = StartZeile:  NextCols = 0
    
    For i = StartZeile To Wks1.Cells(Rows.Count, SpalteNamen).End(xlUp).Row
        Set c = Wks2.Columns(SpalteNamen).Find(Wks1.Cells(i, SpalteNamen), LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            With Wks0
                .Cells(NextLine, SpalteN1 + NextCols) = Wks1.Cells(i, SpalteNamen)
                .Cells(NextLine, SpalteA1 + NextCols) = Wks1.Cells(i, SpalteArtNr)
                .Cells(NextLine, SpalteP1 + NextCols) = Wks1.Cells(i, SpaltePreis)
                .Cells(NextLine, SpalteA2 + NextCols) = Wks2.Cells(c.Row, SpalteArtNr)
                .Cells(NextLine, SpalteP2 + NextCols) = Wks2.Cells(c.Row, SpaltePreis)
                 NextLine = NextLine + 1
                 If NextLine > BreakZeile Then
                    NextLine = StartZeile
                    NextCols = NextCols + SpalteP2 + FreiSpalte
                End If
            End With
        End If
    Next
 End Sub

Die neuen Konstanten entsprechend anpassen:
Codezeile 09 = Bis zu dieser Zeile schreiben, danach wieder ab StartZeile
Codezeile 10 = Anzahl freie Spalten zwischen den einzelnen Blöcken (A:E|Anzahl Leer|X:Y...)

Gruß Dieter

Hallo Dieter,

hoffe du hattest ein gutes Wochenende.
Habe am Wochenende mal fleißig getestet und auch die Originaldaten eingefügt.
Dabei bin ich leider auf ein Problem gestoßen, irgendwie gleicht der die Art. nr in der Zieltabelle nicht mit der Artikelnr. aus der Quelldatei ab,
das heißt, da ich ja nicht alle Artikel aus der Quelldatei in meiner Zieldatei haben will, stimmen dann die Zuordnungen nicht mehr, hier ein Beispiel:

Tabelle 1 (vor dem Makro)

Gemüse Art. Nr. L1 Preis L1 Art. Nr. L2
Artischocken11501 4041
Auberginen 09401 4040
Blaukraut 11502 4018
Blumenkohl11503 4022
Bohnen, 10451 4033
Bohnen, Kenia10454 4034
Broccoli 11504 4023
Fenchel 11506 4036
Frühlingszwiebel11553 4086
Gurken 09405 4044

Tabelle 3 (Quelldaten)

Artikel-Nr. Beschreibung Menge G-Menge VK-Netto
4231 Ananas 8er kg 1,00 8,000 1,26
4224 Apfel Boskop kg 1,00 21,000 5,00
4220 Apfel Braeburn kg 1,00 21,000 0,89
0617 Apfel Cox Orange kg 1,00 21,000 0,00
4218 Apfel Gala kg 1,00 21,000 0,86
4217 Apfel Gold Delicious kg 1,00 21,000 0,86
4221 Apfel Granny kg 1,00 16,000 0,99
4308 Apfel Red Delicious kg 1,00 8,000 1,35
4200 Aprikose kg 1,00 5,000 10,80
4041 Artischocken kg 1,00 5,000 1,62
4040 Auberginen kg 1,00 5,000 2,25
0075 Austernpilzkappen 2,0 kg Kiste 1,00 2,000 4,14
4060 Avocado Hass kg 1,00 4,500 3,56

Tabelle 1 (nach dem Makro)

Artischocken 4220 0,89 €
Auberginen 0617 0 €
Blaukraut 4218 0,86 €
Blumenkohl 4217 0,86 €
Bohnen, 4221 0,99 €
Bohnen, Kenia 4308 1,35 €
Broccoli 4200 10,80 €
Fenchel 4041 1,62 €
Frühlingszwiebel 4040 2,25 €
Gurken 0075 4,14 €
Ingwer 4060 3,56 €

Woran kann des denn liegen?
Weil so ist´s einfach eine Art KOpierfunktion mit vorgegebener Spalte, aber ich dachte mir das des geht das der beim Abgleich nur die Nummern kopiert und an die richtige Stelle schiebt die in dem Zielsheet sind so das ich da dann von den Artikeln die ich habe die neuen PReise bekomme. Hintergrund ist eben auch das in der Quelldatei immer wieder Artikel rausfallen und dazukommen, so dass eine direkte Zuordnung nicht möglich ist, sondern eben nur ühber den Abgleich zwischen Zieldatei und Quelldatei

Grüße und sorry das ich nochmal was fragen muss

Stephan

Hallo Stephan!

Irgendwie blicke ich da jetzt nicht mehr so richtig durch.

Von daher erst nochmal die Funktion des Makros:
Schritt 1: Löschen des aktuellen Inhalts ab der StartZeile in Tabelle1
Schritt 2: Alle Zeilen in Tabelle 2 Spalte B (Namen) durchlaufen und Namen in Tabelle 3 Spalte B suchen:

	A	B	C	...	A	B	C
1	Art-Nr.	Namen	Preis	...	Art-Nr.	Namen	Preis
X	12345	Aprikose	10,80	...	98765	Aprikose	11,90

Schritt 3: Wenn gefunden, dann in Tabelle1 eintragen:

	A	B	C	D	E
1	Namen	Art-Nr.	Preis	Art-Nr.	Preis
X	Aprikose	12345	10,80	98765	11,90

Das ist der aktuelle Ablauf

Jetzt sehe ich anhand Deiner Aufstellung sowas: 4200 Aprikose kg 1,00 5,000 10,80
Wobei sich mir zuerst mal die Frage stellt, wie steht das jetzt in der Tabelle?

4200

Aprikose

Und sieht die Tabelle des anderen Lieferanten genauso aus?

Bei der aktuellen Suche, wird explizit nach dem ganzen Zellinhalt in Namen gesucht.
D.h. "Aprikose" findet nicht "Aprikosen" sondern nur "Aprikose"

Beim ändern des Parameters xlWhole in xlPart, wäre diese Ergebnis zu erwarten:
"Aprikose" findet "Aprikosen" aber "Aprikosen" findet nicht "Aprikose"

Möglich wäre dann noch, nur nach einer bestimmten Anzahl von Buchstaben zu suchen z.B.:
Left("Aprikosen", 5), das würde dann nach "Aprik" suchen

Gruß Dieter

Hallo Dieter,
langsam wird´s mir ja echt schon peinlich aber naja ich frag einfach nochmal:
ich kapiere jetzt den Ablauf des ganzen Makros, allerdings wäre folgender Ablauf besser wenn des machbar ist,
in Tabelle 1 soll nichts gelöscht werden, bis auf die Preise in den Spalten C und E.
Die Tabellen 2 und 3 sollten nach Artikelnummern in Tabelle 1 suchen und wenn es die Artikelnummer in Tabelle 1 gibt,, dann soll ein Preis von der Tabelle 2 bzw 3 nach 1 übertragen werden, Hintergrund wie gesagt folgender es sollen nur ca. 140 preise verglichen werden, die gemüsehändler haben aber ca 800 artikel daher soll eben nur die liste aus tabelle 1, die ich geschrieben habe verglichen werden und die preise aus denn tabellen 2 und 3 dort eingefügt werden.

hoffe ich hab´s gut ausgedrückt um was es geht und du bist noch net sauer auf mich

beste grüße
stephan

Hallo Stephan!

Zitat von @StephanAmount:
hoffe ich hab´s gut ausgedrückt um was es geht und du bist noch net sauer auf mich

Ja mei. Sauer bin ich noch net, awwer dess konn noch kumme, wenn Du mich nochmal so hinters Licht führst

Da haben wir aber ganz schön aneinander vorbeigeredet. Ich hatte das irgendwie so verstanden, dass eine Tabelle erstellt werden soll, die alle Artikel aufführt, die in beiden Lieferanten-Tabellen mit gleicher Bezeichnung (Namen) zu finden sind.

Dann bleibt die Frage, weil Du es erwähnt hattest, ob in Deiner Tabelle jetzt alles untereinander oder über mehrere Spalten verteilt ist?

Gruß Dieter

Hallo Stephan!

Auf ein Neues:

Option Explicit
Option Compare Text

Const SheetNeu = "Tabelle1"     'Tabellenname Neu  
Const SheetAn1 = "Tabelle2"     'Tabellenname Lieferant1  
Const SheetAn2 = "Tabelle3"     'Tabellenname Lieferant2  

Const StartZeile = 2            'Zeile Starten Tabelle1  
Const SpalteArtNr = 1           'Spalte Tabelle2/3 Artikel-Nummer  
Const SpalteNamen = 2           'Spalte Tabelle2/3 Artikel-Bezeichnung  
Const SpaltePreis = 3           'Spalte Tabelle2/3 Preis  

Const SpalteN1 = 1              'Spalte Tabelle2 Name  
Const SpalteA1 = 2              'Spalte Tabelle2 ArtNr  
Const SpalteP1 = 3              'Spalte Tabelle2 Preis  
Const SpalteA2 = 4              'Spalte Tabelle3 ArtNr  
Const SpalteP2 = 5              'Spalte Tabelle3 Preis  

Sub InitTabelleNeu3()
    Dim Wks0 As Worksheet, Wks1 As Worksheet, Wks2 As Worksheet, c As Range, i As Long
    
    Set Wks0 = Sheets(SheetNeu)
    Set Wks1 = Sheets(SheetAn1)
    Set Wks2 = Sheets(SheetAn2)
    
    For i = StartZeile To Wks0.Cells(Rows.Count, SpalteA1).End(xlUp).Row
        Set c = Wks1.Columns(SpalteArtNr).Find(Wks0.Cells(i, SpalteA1), LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then Wks0.Cells(i, SpalteP1) = Wks1.Cells(c.Row, SpaltePreis)
    Next
    For i = StartZeile To Wks0.Cells(Rows.Count, SpalteA2).End(xlUp).Row
        Set c = Wks2.Columns(SpalteArtNr).Find(Wks0.Cells(i, SpalteA2), LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then Wks0.Cells(i, SpalteP2) = Wks2.Cells(c.Row, SpaltePreis)
    Next
 End Sub

Gruß Dieter

Hallo Dieter,

ich wünsche dir einen guten Morgen, vielen Dank für deine erneute Hilfe, es klappt jetzt auch fast perfekt, einziges Problem ist noch er ordnet den Preis nicht der Artikelnummer zu, d.h. er findet eine Artikelnummer in Zeile 13, die er kennt dann wird dieser automatisch der Preis aus Zeile 13 zugeordnet, die Artikelnummer auf der Preistabelle ist aber nicht an der selben stelle wie in der vergleichstabelle, also man müsste des irgendwie gebundelt übertragen, so dass zu der artikelnummer auch der entsprechend der artikelnummer richtige preis zugeordnet wird.

danke grüße stephan

p.s1: wenn du mal in muc bist geb ich dir einen aus
p.s2: so günstig hätte ich im märz noch nie thai-spargel eingekauft

))

Hallo Stephan!

Auch einen schönen guten Morgen!

Sorry, da hatte sich doch tatsächlich ein Fehler in Zeile 28 und 32 eingeschlichen (i anstatt c.Row)

Hab's oben korrigiert.

Hoffe der Preis vom Thai-Spargel stimmt jetzt

Gruß Dieter

PS. Nja, muc is äh bissl weit, ca 350Km bis Schwabing und glaube nochmal ca 30Km nach Oberföhring. Ist schon ne Weile her, als ich das letztemal in der Gegend war Trotzdem Danke für das Angebot

Hallo Dieter,

jep jetzt passt alles vielen Dank für deine großartige und schnelle Hilfe,
wünsche dir noch eine gute Woche und vielleicht schaffst es ja doch mal wieder nach MUC, ansonsten sieht man sich bestimmt noch öfter hier.

Grüße Stephan

Hallo Stephan!

Zitat von @StephanAmount:
jep jetzt passt alles vielen Dank für deine großartige und schnelle Hilfe,

Gern geschehen

wünsche dir noch eine gute Woche und vielleicht schaffst es ja doch mal wieder nach MUC, ansonsten sieht man sich bestimmt
noch öfter hier.

Danke, wünsche Dir auch ne angenehme Woche.

Ja, dann bis demnächst

Gruß Dieter

German solved Question Microsoft Office Microsoft

Excel - Auslesen von Daten und Einfügen in anderes Arbeitsblatt

Hallo,bin neu hier und habe ein Problem bezüglich einer Automation für Excel. Wäre euch für jede Hilfe dankbar, bin eigentlich einigermaßen IT-erfahren habe nur die letzten Jahre wenig in der Richtung gemacht.

Hallo,
bin neu hier und habe ein Problem bezüglich einer Automation für Excel. Wäre euch für jede Hilfe dankbar, bin eigentlich einigermaßen IT-erfahren habe nur die letzten Jahre wenig in der Richtung gemacht.