Microsoft Excel

Herbers Excel/VBA-Archiv

Datenexport transformieren die Zweite

Betrifft: Datenexport transformieren die Zweite von: Nils
Geschrieben am: 08.09.2020 17:36:28

Hallo zusammen,

ich hatte vor einigen Tagen eine Anfrage ins Forum gestellt, auf die Chris mir eine auf den ersten Blick sehr hilfreiche Lösung bereitstellen konnte:


ich hatte gestern ein Thema hochgeladen, was sich bei näherer Betrachtung als unnötig komplex erwiesen hat, deshalb starte ich meine Anfrage neu und umformuliert. Die Arbeitsdatei befindet sich hier:

https://www.herber.de/bbs/user/140037.xlsx

Ich möchte die Daten aus dem Systemexport Tab. „Systemexport“ so transformieren, dass sie mit Pivot analysiert werden können.

Dazu ist es erforderlich, die im Export leider übereinander dargestellten Positionen in einzelnen Spalten zu positionieren.
Im Falle der „Rest“-Informationen (Tab. “Zielformatierung“) kann ich das manuell über ‚Text in Spalten‘ lösen, da die Inhalte durch Leerzeichen getrennt sind.

Keinen Ansatz habe ich für die übergreifenden Produkt- und Lieferantenbezeichnungen, die gem. „Zielformatierung“ ebenfalls in separate Spalten übertragen werden sollen.

Hierbei ist zu berücksichtigen, dass die Produktbezeichnung IMMER über die Lieferantenbezeichnung gestellt ist, wenn diese in Spalte A übereinander der „Rest“-Bezeichnung überstellt sind, was für eine Formellösung ggf. hilfreich ist


Er hat dafür ein Makro geschrieben, das die Beispieldatei

https://www.herber.de/bbs/user/140037.xlsx

zunächst einwandfrei ausgelesen hat:

Sub t()
Dim i As Long, lZ As Long, strProdukt As String, strLieferant As String
Dim WS1 As Worksheet: Set WS1 = Worksheets("Systemexport")
Dim WS2 As Worksheet: Set WS2 = Worksheets("Zielformatierung")

WS2.Rows("3:" & Rows.Count).Delete

For i = 3 To WS1.Cells(Rows.Count, 1).End(xlUp).Row
    If Left(WS1.Cells(i, 1), 1) = " " Then
        lZ = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
        WS2.Cells(lZ, 1) = strProdukt
        WS2.Cells(lZ, 2) = strLieferant
        WS2.Cells(lZ, 3) = Trim(WS1.Cells(i, 1))
    Else
        If WS1.Cells(i, 1) Like "Lieferant*" Then
            strLieferant = WS1.Cells(i, 1)
        Else
            strProdukt = WS1.Cells(i, 1)
        End If
    End If
Next i
End Sub
Leider ist mir erst später aufgefallen, dass das Makro von einem immer mit „Lieferant“ beginnenden Lieferantennamen ausgeht. Das hatte ich möglicherweise irreführend formuliert.
Die in der Beispieldatei hinterlegten Platzhalter entsprechen nicht den tatsächlichen Lieferantennamen.
Diese wollte ich hier lediglich nicht veröffentlich. Die tatsächlichen Namen sind völlig uneinheitlich.
Gibt es unter dieser Voraussetzung überhaupt noch eine Möglichkeit, dass Makro zu modifizieren?

Viele Grüße
Nils

Betrifft: AW: Datenexport transformieren die Zweite
von: Hajo_Zi
Geschrieben am: 08.09.2020 17:40:40

Hallo Nils,

ich konnte Dein Makro in der Datei nicht finden, liegt es vielleicht daran das bei mir eine XLSX Datei kein Makro hat?

GrußformelHomepage

Betrifft: AW: Datenexport transformieren die Zweite
von: Nils
Geschrieben am: 08.09.2020 19:20:35

Hallo Hajo,



sorry, das Makro hatte ich nicht implementiert. Es ist in meiner o.g. Nachricht nur textlich hinterlegt und muss einkopiert werden. Mit Power Query kenne ich mich leider nicht aus, habe aber schon mehrfach gelesen, dass das für solche Anliegen wohl sehr geeignet ist.



Viele Grüße



Nils

Betrifft: AW: Datenexport transformieren die Zweite
von: Günther
Geschrieben am: 08.09.2020 19:53:43

Moin Nils,

das mit der *.xlsx ist deutlich besser, die meisten Helfer verfügen über genügend Intellekt, um die beiden Teile zusammenzufügen. Und viel wichtiger: Die Zahl der wirklichen Helfer, die aus gutem Grund keine VBA-verdächtigen Files öffnen, nimmt zu (ich gehöre auch dazu).

Und was Power Query betrifft: Wenn dir dein Arbeitgeber ein Seminar für dieses Tool spendiert, könnte solch ein Ergebnis herauskommen: https://my.hidrive.com/lnk/hAAGjCu1
(natürlich) ohne VBA, sogar ohne eine einzige Formel. :-) Die Investition lohnt sich garantiert!
 
Gruß
Günther  |  mein Excel-Blog
-.-.-.-

Da ich mich nicht mehr über den "eigenwilligen" Hindernislauf beim Foren-Upload ärgern will, stelle ich meine Uploads hier nur noch in meiner DE-Cloud zur Verfügung. Den Link findest du im Beitrags-Text. Abrufe sind natürlich freiwillig und werden selbstredend nicht ausgewertet.



Betrifft: AW: Datenexport transformieren die Zweite
von: Günther
Geschrieben am: 08.09.2020 18:11:24

Moin,
wenn du eine Aufstellung der Lieferanten und auch der Chemikalien hättest, dann könntest du das bei entsprechende Kenntnis des Handlings wahrscheinlich wesentlich komfortabler mit Power Query lösen.
 
Gruß
Günther  |  mein Excel-Blog

Betrifft: AW: Datenexport transformieren die Zweite
von: Firmus
Geschrieben am: 08.09.2020 20:07:59

Hallo Nils,

1. gibt in den 'Lieferantenzeilen' irgendein Kriterium, das bei allen Lieferanten auftritt?
Das könnte ähnlich dem LIKE-Statement abgefragt werden. (LIKE "*xxx*")

2. Sind die Artikel immer mit Leerzeichen eingerückt?
Das könnte zur Unterscheidung 'Artikelzeile vs. Lieferantenzeile' herangezogen werden.

3. Wenn 2. zutrifft:
Wie kann dann z.B. die Zeile 'ACETYLCYSTEIN' von einer Lieferantenzeile unterschieden werden?

Gruß,
Firmus

Betrifft: AW: Datenexport transformieren die Zweite
von: Nils
Geschrieben am: 09.09.2020 08:47:06

Guten Morgen zusammen,

die Lieferanten sind tatsächlich im Gegensatz zu den Produkten immer mit einem Leerzeichen eingerückt! Außerdem ist die Produktbezeichnung durchweg GROSS geschrieben, während die Lieferanten in regulärer Groß- und Kleinschreibung hinterlegt sind.

Viele Grüße

Nils

Betrifft: AW: Datenexport transformieren die Zweite
von: ChrisL
Geschrieben am: 09.09.2020 18:26:07

Hi
If WS1.Cells(i, 1) <> UCase(WS1.Cells(i, 1)) Then
cu
Chris

Betrifft: AW: Datenexport transformieren die Zweite
von: Firmus
Geschrieben am: 09.09.2020 19:53:05

Hi Nils,

falls die Lösung sofort benötigt wird, schau die geänderte SUB an.
Sub t()
       Dim i As Long, lZ As Long, strProdukt As String, strLieferant As String
       Dim WS1 As Worksheet: Set WS1 = Worksheets("Systemexport")
       Dim WS2 As Worksheet: Set WS2 = Worksheets("Zielformatierung")
       
       WS2.Rows("3:" & Rows.Count).Delete
       
       For i = 3 To WS1.Cells(Rows.Count, 1).End(xlUp).Row
       
            If IsNumeric(Mid(WS1.Cells(i, 1).Value, 7, 1)) = True Then      '7. Stelle  _
numerisch => Produktzeile mit Details
                lZ = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
                WS2.Cells(lZ, 1) = strProdukt
                WS2.Cells(lZ, 2) = strLieferant
                WS2.Cells(lZ, 3) = Trim(WS1.Cells(i, 1))
            Else
                If WS1.Cells(i, 1) <> UCase(WS1.Cells(i, 1)) Then           'Es ist Groß/ _
Kleinschreibung => LieferantenName
                    strLieferant = WS1.Cells(i, 1)
                Else
                    strProdukt = WS1.Cells(i, 1)                            'Großschreibung =>  _
PRODUKTNAME
                End If
            End If
       Next i
End Sub
Sollte klappen, aber nicht getestet.

Gruss
Firmus

ps: PQ ist natürlich der bessere Ansatz.

Beiträge aus dem Excel-Forum zum Thema "Datenexport transformieren die Zweite"