Versch. Werte aus komplexer Exceldatei extrahieren/reduzieren

[rasta1]

Hallo liebes Forum,

nach Jahren des Mit-Lesens und Nachschauens, kann ich das jetzige Problem nicht mehr ohne (eure) Hilfe lösen.
Ich freue mich also über jeden Tipp, der hilfreich sein könnte - Mein Problem sind mangelnde VBA-Kenntnisse und nur "Büro-Erfahrung", was Excel anbetrifft.

Zum Problem:

Jetzt habe ich eine Exceldatei mit 24.000 Zeilen, ein Sammelexport von Rechnungen aus irgendeinem Rechnungsprogramm.
Das Problem sind unterschiedlichste Zeilenformate (mit/ohne verbundenen Zellen), Zellinhalte teilweise mit vielen Leerzeichen dazwischen usw. - ich benötige aus den ganzen Rechnungsangaben jedenfalls immer nur 4 Werte:

- "Rechnung Nr." und die 6 Stellen dahinter
- "Lieferdatum:" und die 10 Stellen dahinter
- "Bestellnummer" und die 14 Stellen dahinter
- "Gesamtbetrag" und den Inhalt der Zelle rechts daneben (dort steht der Betrag)

Nun habe ich 2 Abende lang mit erfolglos TEILEN, RECHTS, LINKS und so weiter versucht. Die Rechnungsnummer bekomme ich extrahiert - die anderen Anforderungen bekomme ich nicht hin - ich scheitere vor allem daran, dass es manchmal verbundene und dann auch wieder nicht verbundene Zellen gibt. Die Bestellnummer ist wiederum in einem Feld mit den Adressdaten und Leerezeichen enthalten - kurzum: Eine für meine Kenntnisse viel zu komplexe Anforderung


[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]
Mit welchen Formeln bzw. Ansätzen bekomme ich das (in mehreren Schritten) ausgelesen?

DANKE im Voraus,
Stefan
  Sammelmappe.xls.xlsx (Größe: 11,26 KB / Downloads: 11)

[Fennek]

Hallo,

meine Empfehlung wäre VBA.

Die Entwicklung eines Codes benötigt aber ein Beipiel, in dem Daten-Typen (Buchstaben, Zahlen, Trennzeichen) dem Original ensprechen.

Ein Beispiel für jede vorhandene Variante reicht.

mfg

PS:

Ist der 2 Datensatz mit einer Leerzeile getrennt?

Es wird ziemlich mühsam werden, mal sehen ...

[Fennek]

Hallo,

für die Beispieldatei geht:

Code:

Type Rechnung
    Re_Nr      As String
    Datum  As Date
    Best_Nr As String
    Betrag  As Currency
End Type

Sub Re_Lesen()
Dim WSF As WorksheetFunction: Set WSF = Application.WorksheetFunction
Dim rng As Range, Re As Rechnung, BeTx As String
Range("A1:V20").MergeCells = False

For i = 1 To 15
    If InStr(1, Cells(i, 1), "Rechnung", vbBinaryCompare) > 0 Then
        Re.Re_Nr = Replace(Cells(i, 1), "Rechnung Nr. ", "")
        Re.Datum = Cells(i - 1, "R")
        BeTx = Split(WSF.Trim(Cells(i + 1, 1)), vbLf)(0)
        Re.Best_Nr = Mid(BeTx, InStrRev(BeTx, " ") + 1)
        Set rng = Range(Cells(i, 3), Cells(i + 20, 3)).Find("Gesamtbetrag", , xlValues, xlPart)
        If Not rng Is Nothing Then Re.Betrag = rng.Offset(, 15)
   
        Debug.Print Re.Re_Nr
        Debug.Print Re.Datum
        Debug.Print Re.Best_Nr
        Debug.Print Re.Betrag
    End If
Next i
End Sub

Frage: geht es noch umständlicher? (Vermutlich)

Da es vermutlich eine unterschiedliche Anzahl der Posten geben kann, wird eine Such-Funktion notwendig.

Bevor Du dich auf so einen Ansatz einläßt, denke in Ruhe nach, pflegen des Codes wird ziemlich schwierig.

mfg

[Gast 123]

Hallo

ich habe eine ganz andere wichtige Frage.  Du sprachst von 24.000 Zeilen.   Heisst das im Klartext, das die Rechnungen in einer Tabelle alle untereinander aufgelistet sind?  Ich nehme an das dann auch die Positionen und Artikel je nach Kunde mehr als nur 1 Zeile ausmachen.  Damm wäre das Chaos perfekt.

Nicht aufgeben für jedes Problem gibt es eine Lösung.  Ich muss nur wissen wie die Tabelle aufgebaut ist.  Mein Tipp dazu, mit einem Makro eine Kopie Datei durchlaufen, wo man auch vor dem Start die verbundenen Zellen rausnehmen kann.  Kannst du dazu noch mal eine Beispieldatei mit ca. 5-10 Rechnungen hochladen, am besten auch Rechnungen mit unterschiedlicher Artikellänge, damit man den Tabellenaufbau sehen kann.

mfg Gast 123

[schauan]

Hallöchen,

hier ein anderer Ansatz. Ist erst mal nur für einen Datensatz, als Darstellung für das Prinzip. Die verbundenen Zellen muss man nicht aufheben. Voraussetzung ist aber, dass die Struktur innerhalb der Zeile gleich bleibt, also zumindest dass die Daten z.B. in der benachbarten verbundenen Zelle neben dem Begriff stehen. Da spielt es auch keine Rolle, ob heute 3 oder morgen 13 Zellen verbunden sind.
Den Inhalt des Array kannst Du prüfen, wenn Du am Ende einen Haltepunkt setzt und es in die Überwachung nimmst oder ins Lokalfenster schaust.

Code:

Sub test()
'Variablendeklarationen
Dim rngCell As Range, found, arrFound
'Array auf Ausgangsgroesse fuer ersten Datensatz stellen
ReDim arrFound(0, 3)
'Schleife ueber alle Zeilen
For Each rngCell In ActiveSheet.UsedRange.Columns(1).Cells
  'Zeilen auf Inhalte pruefen, bei Treffer Inhalt in entsprechende
  '"Array-Spalte" uebernehmen
  Set found = Rows(rngCell.Row).Find(what:="Lieferdatum")
  If Not found Is Nothing Then arrFound(0, 0) = found.Offset(0, 1)
  Set found = Rows(rngCell.Row).Find(what:="Rechnung Nr.")
  If Not found Is Nothing Then arrFound(0, 1) = Trim(Split(found.Value, ".")(1))
  Set found = Rows(rngCell.Row).Find(what:="Bestellnr")
  If Not found Is Nothing Then arrFound(0, 2) = found.Offset(0, 1)
  Set found = Rows(rngCell.Row).Find(what:="Gesamtbetrag")
  If Not found Is Nothing Then arrFound(0, 3) = found.Offset(0, 1)
  'hier muesste jetzt die Erweiterung des Array fuer den naechsten Datensatz kommen
'Ende Schleife ueber alle Zeilen
Next
End Sub

[Gast 123]

Hallo

lade bitte mal deine komplette Datensammlung in das Beispiel und starte das Makro über den Button.  Bei 24.000 Zeilen kann es 1/2 bis 1 Minute dauern bis das Makro durch ist.  Wenn mein Makro richtig ausgelegt ist dürftest du über das Ergebnis überrascht sein.  Im Idealfall hat er alle Rechnungen gefunden und aufgelistet.

Ich bin gespannt auf die Rückmeldung, und wieviele Rechnungen aufgelistet wurden??  Als kleiner Zusatz noch mit Kunden Nr.

mfg Gast 123

[LCohen]

  Sammelmappe.xls.xlsx (Größe: 16,52 KB / Downloads: 1)

1. V:V: =ZEILE()
   
2. Eine Dummy-Rechnung hinter die echten Sätze anfügen (damit der letzte Betrag auch gefunden wird)
   
3. Formeln des 2. Blattes einfügen (Du benötigst xl365, xl2021 oder xlWeb)
   

[rasta1]

Hallo Fennek,



zuerst einmal vielen Dank für deine Antwort, ich arbeite mich gerade durch die ganzen Lösungsvorschläge - und JA, die Datensätze sind mit einer (oder auch zwei) Leerzeile getrennt und es sind unterschiedliche Anzahlen an Posten, leider. Die Fülle der Antworten lässt mich hoffen ,ich arbeite mich jetzt vor... :)



Danke + viele Grüße,

Stefan

---

Hallo Gast 123,



danke für die tollen Antworten, ich teste es umgehend.

Jedenfalls "Ja", die Rechnungen sind alle in einer Tabelle untereinander aufgelistet und Positionen sowie Artikel machen je nach Kunde mehr als nur 1 Zeile aus = das Chaos ist perfekt (für mich zumindest ).


Ähm ... wo lasse ich denn das Makro "per Knopfdruck" laufen?



DANKE EUCH ALLEN, fabelhaft!



Stefan