Hallo zusammen,
ich bin auf der Suche nach einer Lösung/einem Makro für folgendes Problem:
Ich habe mehrere Dateien, welche ich mittels PowerQuery zu einer Gesamttabelle zusammenführe.
Die Gesamttabelle enthält in den Spalten A1 bis U1 Überschriften, danach folgen die Date zeilenweise.
In den Spalten D bis P kommen oftmals gleiche Werte vor, wohingegen die Werte in den Spalten A,R,S,T,Q und U differieren.
Mein Ziel ist es nun, via Makro o. Ä. gleiche Daten in einem Zielarbeitsblatt zum Teil an bestimmten Stellen, zum Teil untereinander auszuweisen (s. Beispiel), bestenfalls nach datum (U) sortiert. Dies soll für jeden Datensatz mit gleichen Werten (Spalte G,E,D) geschehen. Gleichzeitig soll der Komplette Inhalt (U) lesbar sein ohne die Zelle anklicken zu müssen (automatische Größe).
Für Lösungsvorschläge wäre ich dankbar, meine Ausgangstabelle enthälte mehrere tausend Zeilen.
- Sortieren der Spalte Q
- Die eindeutigen "Schlüssel"-Kombinationen aus den Spalten G, D und E lassen sich einfach und schnell per Dictionary erfassen.
- Autofilter setzen für jeden Schlüssel aus dem Dic.
- Der Rest ist dann stupide Fleißarbeit: Daten blockweise transferieren - dafür sind dann keinerlei vertiefte VBA-Kenntnisse erforderlich.
Codes zu den Stichworten findest du im Netz.
mal abgesehen davon, dass Dein Beispiel nicht zur Beschreibung passt und wenn Du keine Lust hast was passendes zu präsentieren, ich hab keine Lust was passendes zu basteln, bleibt die Frage: warum extra VBA? Warum erledigst Du das nicht auch in PQ?
Hallo
bei deinem Beispiel sind noch einige Fragen offen. Könnte man per Makro erledigen.
Ich sehe in der Zieltabelle in Spalte E Werte aus der Gesamttabelle. Aber aus welcher Zeile???
Soll nur eine Zeile kopiert werden, und wenn ja welche??? Das ist im Beispiel nicht ersichtlich.
Ab Zeile 23 stehen Werte als ganzer Block, nur aus verschiedenen Spalten der Gesamttabelle.
Müssen diese Werte immer in die Zeile 23 kopiert werden, oder ist das variabel??
Sollen in der Zieltabelle nur die Daten in Spalte A aufsteigend sortiert werden? Oder alle Spalten??
Das sollte man vorher wissen, damit wir nicht umsonst arbeiten.
mfg Gast 123
Hallo,
zunächst vielen Dank für die konstruktiven Antworten.
@EarlFred: Danke für die Rückmeldung habe mich mit Dictionaries noch nicht befasst, werde mich aber mal belesen. Hinsichtlich des Autofilters bin ich nicht sicher ob dieser mein Anliegen hinreichend abbilden kann (s. Kommentar zum Beitrag von Gast 123). Wie ist da deine Einschätzung? Die stupide Fleißarbeit würde ich gerne weitestgehend automatisieren wollen, daher der Ansatz via VBA. Ggf. ist eine VBA (o.Ä.) Umsetzung die Dictionaries und automatisierung ermöglicht realisierbar?
@ Ralf A: Habe mir Mühe gegeben mein Anliegen möglichst nachvollziehbar darzulegen, daher kann ich deine "Laune" diesbezüglich nicht nachvollziehen. Erläutere mir gerne wo deine Probleme liegen und ich versuche meine Schilderungen/das Beispiel konkreter zu formulieren. VBA deshalb, da es für als der sinnvollste Weg erscheint, bin jedoch auch für jeden anderen Lösungsweg offen. Abgesehen vom Daten zusammenführen sind meine PQ-Kenntnisse jedoch begrenzt.
@Gast 123: Danke für die Rückmeldung. Bei den Werten aus den Spalten E3:E18 der Zieltabelle ist letztlich egal aus welcher Zeile der Gesamttabelle sie stammen, da es sich in der Gesamttabelle um identische Daten handelt. Es geht also darum, dass diese Blöcke (Im Beispiel bspw. A2:U5; A6:U7; A8:U10) jeweils nach Q aufsteigend sortiert werden. Die einzelnen Blöcke sollen dann bspw. als separate Arbeitsblätter o. Ä. in einer Zieltabelle aufgelistet werden, damit ich diese im Anschluss drucken kann und je Zieltabelle nur einen Block der Gesamttabelle drucke. Die Sortierung richtet sich also nach zusammenhängenden Blöcken und zusätzlich einer aufsteigenden sortierung nach Spalte Q.
Nachtrag: Ja, ein Kopieren ab Zeile 23 ist immer erforderlich.
(19.04.2024, 09:09)Stoffo schrieb: [ -> ]…Wie ist da deine Einschätzung? Die stupide Fleißarbeit würde ich gerne weitestgehend automatisieren wollen, daher der Ansatz via VBA….
Meine Einschätzung auf Grundlage meiner Interpretation der Aufgabe, die ich trotz Unschärfen meine grundlegend begriffen zu haben, habe ich dir gegeben, ich wüsste nicht, warum ich die nochmals kundtun oder ändern sollte, sofern du nicht eine präzisierte Beschreibung nachlieferst.
Ich habe schon verstanden, dass du das mit VBA lösen willst, daher bezog sich „stupide Fleißarbeit“ auf das Schreiben des Codes für die von mir beschriebenen Schritte. Das ist keine Herausforderung, sondern einfach lästig, weswegen das dein Part wird.
(19.04.2024, 09:09)Stoffo schrieb: [ -> ]@ Ralf A: Habe mir Mühe gegeben mein Anliegen möglichst nachvollziehbar darzulegen, daher kann ich deine "Laune" diesbezüglich nicht nachvollziehen. Erläutere mir gerne wo deine Probleme liegen und ich versuche meine Schilderungen/das Beispiel konkreter zu formulieren. VBA deshalb, da es für als der sinnvollste Weg erscheint, bin jedoch auch für jeden anderen Lösungsweg offen. Abgesehen vom Daten zusammenführen sind meine PQ-Kenntnisse jedoch begrenzt.
Dann will ich Dir mal meine "Laune" erklären...
Ich kann bisher nur vermuten, was Du willst. Und ich vermute, dass im Zielblatt, Spalte E in den Zeilen 3 bis 18 Filter gesetzt werden sollen. Genau weiß ich das aber nicht. Deshalb:
1. Im Zieldatenblatt hast Du in Spalte E, Zeilen 3 bis18, 4 Blöcke die angeblich jeweils EINEN Wert einer Spalte der Gesamttabelle beinhalten sollen. Welchen, das kann ich nicht erkennen. Zumal keiner der in Ziel angegebenen Werte in Gesamt enthalten ist.
2. Ist diese Blockdarstellung überhaupt erwünscht oder soll sie mir irgendwas sagen? Welche der von den im Zielblatt angegebenen Darstellungen ist denn nun das Ziel? Die obere oder die untere Darstellung? Oder doch beide? Wie sollen oben die Werte ermittelt werden?
3. Ab Zeile 23 im Zielblatt zeigst Du eine andere Ansicht als darüber. Ebenfalls mit Werten, die nicht oder zumindest nicht eindeutig zuzuordnen sind.
Wenn Du schon Werte verwendest, dann verwende welche, die in der Quelle und im Ziel eindeutig zuzuordnen sind und das Ziel der Lösung eindeutig repräsentieren.
Hallo
hier mal eine Beispieldatei von mir. Ich bin gespannt ob damit das Problem gelöst ist??
Hinweis: die Daten in Zieltabelle Spalte D werden aus Zeile 2 Gesamttabelle kopiert.
mfg Gast 123
Hallo zusammen,
zunächst weiterhin vielen Dank für eure Bemühungen. Ich habe nun ein wenig rumgebastelt und bin zumindest einen Schritt weiter.
Anbei ein erster Makroentwurf:
Code:
Sub DatenAufteilen()
Dim GesamtTabelle As Worksheet
Dim letzteZeile As Long
Dim i As Long, j As Long, k As Long
Dim eindeutigeDatensaetze As Object
Dim datensatz As String
Dim zielBlatt As Worksheet
Dim zielZeile As Long
' Arbeitsblatt für die Gesamttabelle festlegen
Set GesamtTabelle = ThisWorkbook.Worksheets("Gesamttabelle") ' Name des Arbeitsblatts mit den Gesamtdaten
' Dictionary für eindeutige Datensätze erstellen
Set eindeutigeDatensaetze = CreateObject("Scripting.Dictionary")
' Letzte Zeile in der Gesamttabelle ermitteln
letzteZeile = GesamtTabelle.Cells(Rows.Count, 1).End(xlUp).Row
' Schleife über die Daten in der Gesamttabelle
For i = 2 To letzteZeile ' Überschriften in der ersten Zeile Daten ab der zweiten Zeile
' Eindeutigen Datensatz zusammensetzen (von Spalte D bis P)
datensatz = ""
For j = 4 To 16 ' Spalte D-P
datensatz = datensatz & GesamtTabelle.Cells(i, j).Value & "|"
Next j
' Überprüfen, ob der Datensatz bereits existiert
If Not eindeutigeDatensaetze.exists(datensatz) Then
' Eindeutigen Datensatz in das Dictionary eintragen
eindeutigeDatensaetze.Add datensatz, True
' Neues Arbeitsblatt für den Datensatz erstellen
Set zielBlatt = ThisWorkbook.Worksheets.Add
zielBlatt.Name = "Datensatz_" & eindeutigeDatensaetze.Count
' Daten in das Ziel-Arbeitsblatt kopieren
For j = 1 To 13 '
zielBlatt.Cells(j + 2, 5).Value = GesamtTabelle.Cells(i, j + 3).Value ' Offset um 3 Spalten nach rechts, da wir bei Spalte D beginnen
Next j
' Einträge aus den anderen Spalten einfügen
zielZeile = 23 ' Startzeile für die Einträge aus den anderen Spalten
For k = i To letzteZeile
' Überprüfen, ob der Datensatz zu dem aktuellen Datensatz des Zielarbeitsblatts gehört
If datensatz = "" Then Exit For
If datensatz = Left(GesamtTabelle.Cells(k, 4).Value & "|" & GesamtTabelle.Cells(k, 5).Value & "|" & GesamtTabelle.Cells(k, 6).Value & "|" & GesamtTabelle.Cells(k, 7).Value & "|" & GesamtTabelle.Cells(k, 8).Value & "|" & GesamtTabelle.Cells(k, 9).Value & "|" & GesamtTabelle.Cells(k, 10).Value & "|" & GesamtTabelle.Cells(k, 11).Value & "|" & GesamtTabelle.Cells(k, 12).Value & "|" & GesamtTabelle.Cells(k, 13).Value & "|" & GesamtTabelle.Cells(k, 14).Value & "|" & GesamtTabelle.Cells(k, 15).Value & "|" & GesamtTabelle.Cells(k, 16).Value & "|", Len(datensatz)) Then
' Eintrag gehört zum gleichen Datensatz, in die Ziel-Tabelle einfügen
zielBlatt.Cells(zielZeile, 1).Value = GesamtTabelle.Cells(k, 17).Value ' Spalte Q
zielBlatt.Cells(zielZeile, 2).Value = GesamtTabelle.Cells(k, 1).Value ' Spalte A
zielBlatt.Cells(zielZeile, 3).Value = GesamtTabelle.Cells(k, 18).Value ' Spalte R
zielBlatt.Cells(zielZeile, 4).Value = GesamtTabelle.Cells(k, 19).Value ' Spalte S
zielBlatt.Cells(zielZeile, 5).Value = GesamtTabelle.Cells(k, 20).Value ' Spalte T
zielBlatt.Cells(zielZeile, 6).Value = GesamtTabelle.Cells(k, 21).Value ' Spalte U
zielZeile = zielZeile + 1
End If
Next k
End If
Next i
' Benutzer informieren
MsgBox "Daten wurden erfolgreich aufgeteilt und angeordnet.", vbInformation
End Sub
dies funktioniert schon einigermaßen wie gewünscht. Leider ist es so, dass aktuell in den einzelnen Datensätzen ab Zeile 23 auch Zeilen aufgeführt werden wenn die Spalten R-U allesamt 0-Werte enthalten -> würde ich gerne ausschließen. Ggf. habt ihr ja Lösungsideen/Verbesserungsvorschläge.
Ich hoffe durch das Makro wird mein Anliegen insgesamt klarer.
Problem gelöst:
Code:
' Überprüfen, ob das Zielblatt Daten ab Zeile 23 enthält und dann sortieren
If zielZeile > 23 Then
' Sortieren des Zielbereichs ab Zeile 23 nach Spalte A
With zielBlatt.Sort
.SortFields.Clear
.SortFields.Add Key:=zielBlatt.Range("A23"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange zielBlatt.Cells(23, 1).Resize(zielZeile - 22, 6) ' Bereich ab Zeile 23, 6 Spalten
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If