[Excel 2007] Gezielt Daten zusammenführen

[alexgu]

Hallo zusammen,
ich habe folgendes Problem zu welchem ich hier und auch über Google nicht wirklich eine Lösung gefunden habe. Ich habe eine Excel 2007 Datei. Diese enthält mehrere Arbeitsblätter mit Tabellen die gleich aufgebaut sind, sich jedoch leicht unterscheiden (z.B. Grenzwerte für chemische Zusammensetzungen). Daher kann ich nicht eine große Tabelle daraus machen. Aus diesen Tabellen interssieren mich für eine Auswertung nur jeweils zwei Spalten. Die Spalte A mit einer Losnummer und die Spalte AN mit dem Status (akzeptiert oder verworfen, per Dropdown auswählbar). Die einzelnen Arbeitblätter stellen unterschiedliche Materialvarianten dar und sind dementsprechend benannt. Die Anzahl der Einträge in jeder Tabelle kann unterschiedlich sein. Ich möchte nun ein Arbeitsblatt (Zusammenfassung) in welchem eine Tabelle steht mit folgenden Daten: Spalte A = Materialvariante, Spalte B = Losnummer, Spalte C = Status. Alles untereinander. Am liebsten natürlich immer aktuell. Bisher habe ich für die Zusammenführung der Daten allerdings keine Lösung gefunden. Schon alleine aufgrund der unterschiedlichen Länge der Spalten ist das wahrscheinlich nur per VBA möglich oder? Ich habe zwar auch hinweise auf Power Query gefunden, aber das geht bei meiner Version ja noch nicht. Ebenfalls habe ich ein "merge" add-in gefunden, welches mir aber auch nicht weiter hilft. Hat hier vielleicht jemand einen Tip für mich als VBA Laien?


Gruß
Alex

 

[Cassius1985]

Wenn ich alles richtig verstehe, kannst Du hier ganz simpel mit einem SVERWEIS arbeiten.

Du schreibst die Losnummern x-mal untereinander und machst dann für jede Zelle einen Sverweis auf die verschieden Arbeitsbätter.

Das funktioniert allerdings nur, wenn die Losnummern nicht doppelt in einem Arbeitsblatt vorkommen.

 

[sgrisius]

VBA Programierung oder Macro aufzeichnen was alles kopiert in die Zusammenfassung.

 

[alexgu]

Hallo, danke für die zügigen Antworten.
@ Cassius1985: Das mit dem sverweis habe ich allerdings nicht verstanden. So wie ich die Funktion verstehe, kann ich damit nach bestimmten Werten in einer Tabelle suchen. Ich weiß allerdings nicht, wie ich damit mein Problem löse. Beispiel: In meinem ersten Arbeitsblatt steht unter Losnummer so was sie A201605_1, danach A201611_2, sprich also keine aufeinanderfolgenden Zahlen. Im nächsten Arbeitsblatt kommt das Material von einem anderen Lieferanten der meinetwegen seine Lose mit 2016_0011 kennzeichnet. Auch hier müssen die nicht aufeinander folgen, da nicht alles Material seiner Chargen zu uns geht.

@sgrisius: das mit der Makroaufzeichnung hatte ich bisher nicht gemacht, da ich dies ja immer nur für genau den einen Zustand der Datei funktioniert. Ich kopiere meinetwegen einen Bereich mit drei Zeilen aus Blatt 1 und füge den in Zusammenfassung ein. Danach fünf Zeilen aus Blatt zwei usw. Sobald dich die Zeilenanzahl allerdings verändert, würde das das Makro ja nicht wissen. Ich fürchte es geht also nicht ohne VBA. Leider bin ich da überhaupt nicht bewandelt. Ich müsste in jedem Blatt den ausgefüllten Bereich bestimmen und diesen kopieren und in Zusammenfassung einfügen nachdem ich hier die erste freie Zelle bestimmt habe, damit alles vernünftig untereinander kommt.

Gruß

 

[ika2k]

Das ganze lässt sich auch dynamisch (immer aktuell) gestalten, ist aber etwas aufwändig mit Hilfstabellen zu lösen

grob Googlen nach "alle Blätternamen listen" dann hat man die Blätter in einer Liste, dann in neuer Tabelle mit indirekt Formeln und geschicktem zählen sich Wert für Wert aus den jeweiligen Blätten ziehen - oben anfangen und wenn Ergebnis = "" (leere Zelle) dann nächster Blattname und das ganze wieder von vorne bis Namensliste abgearbeitet

das mal als Idee

Müsstest dann vermutlich trotzdem ein Makro ergoogeln und dann als xlsm speichern

 

[Cassius1985]

Ist denn die Materialnummer immer die gleiche? Um Sinnvoll sortieren zu können, müssen die Werte schon vergleichbar sein...

Ansonsten ist "manuelles" sortieren angesagt.

Kannst Du eine Beispieldatei erstellen/hochladen?

 

[sgrisius]

Mit Makro geht das, Wenn du das Makro programmierst kannst du dich mit Control und Pfeil nach Unten positionieren.
Dann kommst du immer in die letzte Zeile.

Das heist wenn du das Zweite Sheet kopierst geh in das erste Sheet klicke in eine Zelle und Control und Pfeil nach Unten dann noch einmal Pfeil nach Unten und schon bist du in der ersten freien Zelle.

Ergänzung (25. November 2016)

Problem beim Makro ist nur du must es einmal komplett manuel machen. Danach kanns du es nur zum Refresh benutzen.

 

[alexgu]

Super, das mit dem Makro und den Pfeiltasten funktioniert für mich so weit ganz gut. Ich kann jetzt aus den einzelnen Arbeitsblättern die gewunschten Bereiche kopieren und in meine Zusammenfassung einfügen. Dadurch habe ich Spalten B (Losnummer) und Spalte C (Status). Jetzt habe ich noch das Problem dass ich in Spalte A die Materialvariante brauche. Diese kann über viele Zeilen immer gleich sein, sie steht in den Arbeitsblättern jedoch nur in einer Zelle. Meine Idee war nun, das ich über Spalte B heraus finde wo die letzt Zeile ist und dann per Pfeiltasten in die Spalte A gehe und dort den Bereich oberhalb bis unter den Spaltentitel auswähle und dann den vorher gewählten Text in alle Zellen einfüge. Allerdings bekomme ich das über die Tasten nicht hin.

Der Code sieht jetzt so aus

Sub Makro()
'
' Makro Makro
Sheets("Material_1").Select
Range("A1").Select
Selection.Copy
Sheets("Zusammenfassung").Select
Range("B2").Select
Selection.End(xlDown).Select
Range("A5").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Zum einen ist sicher A5 falsch da das Ende der Zeilen nicht zwangsläufig in Zeile 5 liegen muss. Eigentlich soll er aus der letzten Zelle in Spalte B eine Zelle nach links gehen. So hatte ich das Makro versucht aufzunehmen. Zum anderen markiere ich nach oben auch die Überschrift. Ich will aber den Bereich -1 Zelle markieren. Hat hier jemand nen Tipp?

 

[SOL Solutions]

Bist du dir sicher, dass das so was wird? Bisher kopierst Du nur eine Zelle A1. Lad' doch mal ein Beispiel hoch, dann finden wir sicher eine Lösung. Ich kann mir die Problematik leider noch nicht vorstellen

 

[alexgu]

@SOL Solutions, ja ich weiß mit dem geposteten Code Schnipsel kopiere ich natürlich nur die eine Zelle. Der gehörte wie gesagt zu der Problematik, die darüber beschrieben wurde. Der Rest funktioniert schon mal

Edit: Ich habe mal eine Besipieldatei erstellt, um den Sachverhalt ein wenig deutlicher zu machen. Ich hoffe es hilft. Ich möchte aus den Blättern Variante 1 und 2 jeweils die Spalten A und G kopieren und in die Spalten B und C der Zusammenfassung kopieren. Die Anzahl der Zeilen kann hier völlig unterschiedlich sein und natürlich habe ich nicht nur zwei Arbeitsblätter sondern erheblich mehr
Außerdem soll die Zelle A1 der Varianten in die Spalte A kopiert werden. Genau für dieses Problem habe ich keine Lösung (siehe Post #8). Ich schaffe es nicht die Zelle A1 in die gewünschte Anzahl von Zellen der Spalte A zu kopieren. Meine Idee war wie gesagt die letzte ausgefüllte Zeile über die Spalte B zu ermitteln, dann einen nach links und dort den Bereich zu markierender noch leer ist. Über "Strg+Pfeiltaste nach oben" springt er allerdings in die erste wieder ausgefüllte Zelle, was ich ja nicht will.

 

[SOL Solutions]

Aha, ich verstehe, wie viele Zeilen sind hier immer so in den einzelnen Sheets? Geht's hier um 100, oder eher 1.000, oder sogar 10.000?

 

[alexgu]

Das spielt sich auf jeden Fall unter 100 ab. Zumindest im Moment noch

 

[SOL Solutions]

Hier mal was kleines zum Ausprobieren. Kann man sicherlich noch ein bisschen verbessern/erweitern, aber es macht erstmal was Du willst.

Bei Fragen, einfach durchkommen :-)

Anhang anzeigen Zusammenfassung.zip

Gruesse
SOL

 

[alexgu]

@SOL Solutions,
besten Dank für die Datei. Ist ja doch ein wenig anders als mein Ansatz und auch schlanker und wahrscheinlich eleganter Ich habe gerade mal versucht das für mein Problem zu übertragen. Leider bin ich gescheitert. Da ich kein VBA Profi bin stehe ich auch leider ein wenig auf dem Schlauch. Kann es sein, dass dein Makro versucht sich durch Worksheets zu arbeiten, die mit "Material" beginnen? In meinem konkreten Fall ist es leider nicht so einfach. Hier heißen die Sheets so wie 302Cu V63 Carpenter und SUH-3 V62 Daido. Also nicht wirklich eine Gemeinsamkeit. Ich hatte das nur für die Beispieldatei so angepasst.

 

[SOL Solutions]

ok, kein Problem. Das war nur ein Versuch das Ganze variabel zu gestalten, es schaut tatsaechlich alle vorhanden Sheets durch. Dabei muss die Zusammenfassung natuerlich ausgeschlossen werden. Bitte die folgende Zeile anpassen.

If InStr(1, s.Name, "Material", vbTextCompare) > 0 Then

auf

If Not s.Name = "Zusammenfassung" Then

Gruesse
SOL

 

[alexgu]

Sehr cool. Es wird langsam. Jetzt hab ich das Problem, dass er die "Status" Spalte nicht kopiert. Ich habe gedacht das ich das G

 sql = "SELECT * FROM [" & s.Name & "$A6:G" & lastRow & "] WHERE NOT [F1] = '';"

in dem code durch AN ersetze was in meinem Fall die gewünschte Spalte ist. Allerdings wird die nicht in die Zusammenfassung kopiert. Für den Rest klappt es super. Hab ich da was übersehen?

Gruß Alex

 

[SOL Solutions]

Ja, du musst dann auch die ausgegebenen Felder anpassen. Da wir hier alles mit "SELECT *" auswaehlen, hat die Auswahl auch entsprechend die Spalten. In deinem Fall A bis AN entspricht den Feldern F1 bis F40.

Wenn Du jetzt mal schaust was wir ausgeben,

ThisWorkbook.Sheets("Zusammenfassung").Cells(x, 2) = rs.Fields(0)
ThisWorkbook.Sheets("Zusammenfassung").Cells(x, 3) = rs.Fields(6)

siehst du das Field(6) wahrscheinlich leer ist. Du hast zwei Moeglichkeiten:

Nummer 1: Du aenderst rs.Fields(6) auf rs.Fields(39) ab. Allerdings ist mit dieser Methode dein Recordset unnoetig gross und verbrauch mehr resourcen.

Nummer2: Du passt die selection von "SELECT *" auf "SELECT F1, F40" an und die zweite Ausgabe auf Fields(1), da es jetzt nur noch zwei Spalten in deinem Recordset gibt.

Viel Glueck

 

[alexgu]

Sehr cool, es funktioniert. Ich muss jetzt erst mal versuchen zu verstehen, was ich da gemacht habe
Vielen Dank für die Hilfe. Echt Spitze.

Gruß
Alex

Nachtrag: ähm, eine Sache hätte ich noch. Wenn ich jetzt das Ergebnis habe und hier einen Filter auf die drei Spalten setzte, dann ist dieser weg, sobald ich die Liste "update", sprich das Makro ausführe. Wenn ich den Inhalt manuell lösche bleibt die Filtereinstellung da. Kann man das irgendwie beheben? Ich habe versucht ein Makro für das Filtern aufzunehmen und dann unter das bestehende zu kopieren. Hat leider nicht funktioniert.

 

[SOL Solutions]

Sehr gut, Verstehen ist essenziell. Wenn Du meinen Code vollstaendig uebernommen hast (wovon ich mal ausgehe), dann behandelst Du die Material Tabellen wie eine Datenbank und faehrst eine SQL Abfrage ueber die einzelnen Blaetter.

Wie ist es um deine SQL- und Programmierkenntnissen bestellt? Sollten unloesbare Fragen auftauchen, einfach melden

Edit: Nein, nein....nix mit dem Rekorder friemeln ganz quick and dirty wird aus

'sauber machen
With ThisWorkbook.Sheets("Zusammenfassung")
    .Range("A:C").ClearContents
    .Cells(1, 1) = "Material"
    .Cells(1, 2) = "Heat No."
    .Cells(1, 3) = "Status"
End With

das hier

'sauber machen
ThisWorkbook.Sheets("Zusammenfassung").Range("A2:C1000").ClearContents

Edit2: Du kannst aber auch das SQL Statement anpassen und nur Sachenausgeben lassen die du haben willst :-)

ZB: Fuer alle Eintraege welche "acc" sind, sieht die Abfrage so aus:

"SELECT * FROM [" & s.Name & "$A6:G" & lastRow & "] WHERE NOT [F1] = '' AND [F2] = 'acc';"

 

[alexgu]

Tja, da liegt der Hase im Pfeffer. Programmieren hab ib ich mal ein wenig im Studium gemacht. Ist aber 22 Jahre her und war damals Fortran90 und ein wenig C. SQL hab ich überhaupt keine Ahnung
Warum funktioniert das nicht mal, dass ich den Code für das Filtern hinter deinen Code packe?

Nachtrag: Okok, ich lass den rekorder weg Jetzt funktioniert es. Besten Dank du bist echt top.