Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1956to1960
1956to1960
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)

Forumthread: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)

Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
13.12.2023 13:08:18
Mage80
Hallo,

Ich habe eine Excel-Tabelle mit mehr als 15.000 Bestellungen und Kundeninformationen. Ich möchte die Kunden in derselben Tabelle nach ihrer letzten Bestellung sortieren, aber einige Kunden erscheinen unter derselben Bestellnummer, weil sie mehr als ein Produkt in eine Bestellung eingeben.

1. bei Kunden mit unterschiedlichen Bestellnummern wird die letzte Bestellung angezeigt
2. wenn der Kunde mehr als ein Produkt mit der gleichen Bestellnummer bestellt hat, bleiben diese Bestellnummern auch in der Liste, aber nur die aktuellen, d.h. die mit dem letzten Bestelldatum

Hier ist eine Beispiel-Datei, wie die endgültige Version aussehen soll. Können Sie mir bitte helfen oder Vorschlag geben mit oder ohne VBA?
https://www.herber.de/bbs/user/165160.xlsx
Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
13.12.2023 13:47:58
daniel
HI
bei 15.000 Zeilen so:

1. Liste nach Kundennummer und Bestelldatum-absteigend sortieren.

2. per Formel in einer Hilfsspalte die zu löschenden Zeilen kennzeichnen:
für deine Beispieltabelle in F2 die Formel:
 =WENN(C2>C1;1;WENN(D2=D1;F1;0))

dann eine zweite Hilfsspalte mit dieser Formel: 
=WENN(F2=1;ZEILE();G$1)

in die Überschriftenzeile der zweiten Hilfsspalte (G1) einen Wert eintragen, diese Zelle darf nicht leer sein

3. jetzt das Daten - Datentool - Duplikate entfernen auf die Tabelle anwenden, mit der zweiten Hilfsspalte als kriterium

per VBA würde ich auch diese Schritte ausführen, der Recorder ist dein Freund.

Gruß Daniel
Anzeige
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
13.12.2023 16:20:48
daniel
HI
für Excel 365 gäbe es auch noch eine Formellösung:

in eine Zelle diese Formel (passend für deine Beispieldatei) 

=LET(kd;EINDEUTIG(Tabelle5[Kunde]);ID;kd&MAXWENNS(Tabelle5[Bestelldatum];Tabelle5[Kunde];kd);FILTER(Tabelle5;ISTZAHL(VERGLEICH(Tabelle5[Kunde]&Tabelle5[Bestelldatum];ID;0));"---"))


allerdings kann ich dir nicht sagen, wie performant das bei deiner realen Datenmenge ist.

Gruß Daniel
Anzeige
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
13.12.2023 15:07:24
Alwin Weisangler
Hallo,

falls du es per VBA machen möchtest, wäre dies eine von diversen Möglichkeiten: 


Option Explicit



Sub newBestProKd()

    Dim objDic As Object, ltzDate As Date, arrKd, arrTmp(), i&, j&, k&

    Set objDic = CreateObject("Scripting.Dictionary")

    If Not Tabelle1.ListObjects("Tabelle57").DataBodyRange Is Nothing Then Tabelle1.ListObjects("Tabelle57").DataBodyRange.Delete

    With Tabelle1.ListObjects("Tabelle5")

        .Range.AutoFilter Field:=3

        .Range.AutoFilter Field:=4

        For i = 1 To .DataBodyRange.Rows.Count

            objDic(.DataBodyRange.Cells(i, 3).Value2) = 0

        Next i

        arrKd = objDic.keys

        For i = 0 To UBound(arrKd)

            ltzDate = WorksheetFunction.MaxIfs(.DataBodyRange.Columns(4), .DataBodyRange.Columns(3), arrKd(i))

            .Range.AutoFilter Field:=3, Criteria1:=arrKd(i)

            .Range.AutoFilter Field:=4, Criteria1:="=" & ltzDate, Operator:=xlAnd

            arrTmp = .DataBodyRange.SpecialCells(xlCellTypeVisible)

            .Range.AutoFilter Field:=3

            .Range.AutoFilter Field:=4

            With Tabelle1.ListObjects("Tabelle57")

                .ListRows.Add

                With .DataBodyRange

                    For j = 1 To UBound(arrTmp, 1)

                        For k = 1 To UBound(arrTmp, 2)

                            .Cells(j + .Rows.Count - 1, k) = arrTmp(j, k)

                        Next k

                    Next j

                End With

            End With

        Next i

    End With

End Sub

https://www.herber.de/bbs/user/165162.xlsm

Gruß Uwe
Anzeige
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
13.12.2023 16:10:28
Mage80
Danke Uwe!

Die Lösung mit VBA funktioniert auch :)



Gruß Johann
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
13.12.2023 15:43:19
Mage80
Hi Daniel,

vielen Dank für die Formel. Ich habe es ausprobiert und es funktioniert und nach dem Löschen der Dubletten sind die Daten von unnötigen gereinigt.

Nochmals herzlichen Dank!


Gruß Johann
Anzeige
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
14.12.2023 18:17:08
Luschi
Hallo Johann,

bei den Datenmengen sollte eine Excel-Power Query-Lösung sehr interessant sein, da
- keine Formeln
- kein Vba
- nur wenig PQ-M-Code erforderlich
- und PQ ist für strukturierte Massendaten ausgelegt

Hier meine PQ-Lösung: https://www.herber.de/bbs/user/165202.xlsx

Gruß von Luschi
aus klein-Paris

Anzeige
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
15.12.2023 10:55:23
Mage80
Hallo Luschi,

danke für PQ-M. Das ist auch sehr praktisch.


Gruß,
Johann
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
15.12.2023 16:57:52
Luschi
Hallo Johann,

bei dieser Anforderung: Ich habe eine Excel-Tabelle mit mehr als 15.000 Bestellungen und Kundeninformationen.
bin ich mehr daran interessiert, ob
- Formellösungen
- Vba oder
- PQ
die Wahl der Qual sind; ich hoffe auf zeitliche Einschätzung hinsichtlich der Ablaufdauer von Dir.

Gruß von Luschi
aus klein-Paris
Anzeige
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
19.12.2023 10:17:51
Mage80
Hallo Luschi,

ja. Gerne :) Hier sind die Ergebnisse.

Alle 3 Methoden führen zu Ergebnissen, aber es gibt gute und schlechte Aspekte.

Test-Datei: 50.110 Datensätze (14 Spalten)
Office-Version: Office 365 Business
Hardware: Intel-Core i7-10510U CPU 2,30 GHz / 16 GB RAM

Lösung mit Formel:
Erstellung Hilfsspalten und Formel einsetzen: ca. 5 min
Ablaufdauer von Formelabfragen: weniger als 1 min.
Kontrollieren von Datensätzen, Entfernung von Dubletten, Erstellung von Finale-Datei: ca. 5-10 min.

Gesamt: ca. 16 min.

Vorteile: Die Formeln sind auch für die Hilfspalte sichtbar und die Korrektheit der Reihenfolge kann vor der Erstellung der Enddatei überprüft werden.
Nachteile: Es sind 4 Arbeitsschritte erforderlich. Wenn nur eine fehlt, ergibt es ein falsches Ergebnis.

Lösung mit VBA:
Es ist sehr gut, mit einem Makro in VBA zu arbeiten, aber nur, wenn die Datenmenge nicht zu groß ist. Es ist also keine gute Lösung für eine Datei mit mehr als 10K Datensätzen!

Erstellung Makro mit VBA und Verbindung mit der Tabelle: 5-10 min
Ablaufdauer: mehr als 15 min danach stürzt ab! :(
Ich habe es auch mit 500 Datensätzen versucht, Ablaufdauer ist ca. 1 min.

Lösung mit PQ-M:
Sehr praktisch und die Prozessüberwachung funktioniert auch bei einer Datei mit 50K Datensätzen ohne Probleme. Deshalb habe ich mich für PQ-M entschieden.

Erstellung der Abfragen in PQ-M: 5 min.
Ablaufdauer: weniger als 1 min.


Vielen Dank euch!


Gruß,
Johann
Anzeige
AW: Tabelle je Kunde 1x letzte aktuelle Bestellung (Datum)
19.12.2023 13:43:12
Luschi
Hallo Johannm

danke für Deinen Auswertungsbericht: daß klingt doch richtig hoffnungsvoll, sich nicht nur bei Massendaten mit Power Query-'M' eingehender zu beschäftigen.

Gruß von Luschi
aus klein-Paris
AW: zu Deiner Lösungsvarianten-Auswertung nachgefragt ...
21.12.2023 14:33:03
neopa C
Hallo Johann,

... diese ist zunächst zu loben. Es ist (leider) eine seltene Ausnahme, von einem Fragesteller eine derartige Auswertung für ihm gegebene verschiedene Lösungsvarianten so zu erhalten. Danke deshalb dafür also auch von mir.

Ich hätte nun aber auch eine Bitte an Dich. Ich hatte vor Tagen auch mal zunächst für mich eine PQ-Lösung für Deine Aufgabenstellung erstellt. Die habe ich nachfolgend zu der von Luschi als eine mögliche weitere Alternative aufgestellt. Siehe https://www.herber.de/bbs/user/165427.xlsx.

Luschi ist ein Programmierprofi ich nutze PQ im Wesentlichen nur interaktiv. Und so habe ich die für Dein Problem aufgestellt. Meine Lösung bedarf aktuell dadurch zweier Abfragen, aber die eben keiner M-Code-Programmierkenntnisse benötigen und somit sicherlich leichter nachvollziehbar sind.

Mich würde nun interessieren, wie viel Zeit diese Lösungsvariante für Deine Originaldaten von 50 Tausend Datensätzen benötigt. Würde mich freuen, wenn Du dafür bei Gelegenheit die nötige Zeit findest. Vielleicht hilft Nachlesern dieses threads ja auch mein Lösungsansatz ein etwas für ähnliche Aufgabenstellungen.

Gruß Werner
.. , - ...
Anzeige
AW: zu Deiner Lösungsvarianten-Auswertung nachgefragt ...
21.12.2023 16:23:43
Luschi
Hallo Werner,

hier mal Deine beiden Abfragen zu einer zusammengefaßt: https://www.herber.de/bbs/user/165430.xlsx

Gruß von Luschi
aus klein-Paris
AW: so kann man die Abfragen zusammenführen, aber ...
21.12.2023 17:35:13
neopa C
Hallo Luschi,

... dazu bedarf es dann einen Eingriff in den interaktiv erstellten M-Code und dies erfordert demzufolge entsprechende zusätzliche Kenntnisse, die man nutzt, wenn man sie beherrscht. Danke für Deine entsprechende Anregung sowie die Datei.

Bin gespannt, ob Johann dazu kommt auch dies zu lesen und entsprechend zu reagieren.

Gruß Werner
.. , - ...
Anzeige
AW: so kann man die Abfragen zusammenführen, aber ...
22.12.2023 12:52:16
Mage80
Hallo Werner,

vielen Dank für die Zusendung eines Beispiels ohne ein verwendetes PQ-M Programmskript. Aber ich kann deine Datei nicht öffnen, da Excel die Datei blockiert, weil eine externe Datenverbindung besteht. Solche Dateien möchte ich aus Sicherheitsgründen auch nicht öffnen.

Wenn du hier die abfragen aus dem "Erweiterter Editor" als Antwort einfügen könntest , würde ich gerne auch deine PQ-Lösung mit 50K Datensätzen testen und bewerten.



Danke Dir! für alternative Lösung.


Gruß,
Johann
Anzeige
AW: für mich zwar leider nicht nachvollziehbar, ...
23.12.2023 07:13:03
neopa C
Hallo Johann,

.... dass die Meldung bei meiner eingestellten bei Dir kommt, nicht aber bei der von Luschi zuerst eingestellten Datei. Diese hatte ich lediglich um meine Lösungsalternative ergänzt und so eingestellt. Ich erkläre es momentan mir so, dass ich meine Lösung in der Version XL 2016 erstellt habe und MS in Deiner aktuelleren XL-Version möglicherweise davor warnen will, warum auch immer.
Kommt bei Dir die gleiche Meldung auch, wenn Du die zweite von Luschi eingestellte Datei aktivierst oder nicht? Denn er hat die Zusammenfassung meiner zwei - nachfolgend hier eingestellten 2 Abfragen - darin belassen und arbeitet mittlerweile mit XL365.

Nun der M-Code meiner zwei interaktiv erstellten Abfragen:
Die erste benannt: neopa_Gruppiert 
let

    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle5"]}[Content],

    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Bestellnummer", Int64.Type}, {"Knummer", Int64.Type}, {"Kunde", type text}, {"Bestelldatum", type date}, {"Artikel", type text}}),

    Gruppiert = Table.Group(#"Geänderter Typ", {"Knummer"}, {{"Anzahl", each List.Max([Bestelldatum]), type nullable date}})

in

    Gruppiert


und die zweite benannt: neopa_Ergebnis 
let

    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle5"]}[Content],

    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Bestellnummer", Int64.Type}, {"Knummer", Int64.Type}, {"Kunde", type text}, {"Bestelldatum", type date}, {"Artikel", type text}}),

    Zusammengeführt = Table.NestedJoin(#"Geänderter Typ", {"Knummer", "Bestelldatum"}, neopa_Gruppiert, {"Knummer", "Anzahl"}, "Gruppiert", JoinKind.LeftOuter),

    Erweitert = Table.ExpandTableColumn(Zusammengeführt, "Gruppiert", {"Knummer"}, {"Knummer.1"}),

    Gefiltert = Table.SelectRows(Erweitert, each ([Knummer.1] > null)),

    Ergebnis = Table.RemoveColumns(Gefiltert,{"Knummer.1"})

in

    Ergebnis


Würde mich freuen nach Weihnachten hierzu von Dr zu lesen. Wünsche Dir jetzt aber erst einmal ein paar schöne Weihnachtsfeiertage.

Gruß Werner
.. , - ...
Anzeige
AW: für mich zwar leider nicht nachvollziehbar, ...
04.01.2024 13:19:19
Mage80
Hallo Werner,

ja. Die zweite von Luschi gesendete Datei hatte das gleiche Problem. Ich habe auch diese Datei nicht geöffnet.

Wie versprochen, habe ich die von Dir zugesandte PQ-Lösung mit 50K (14 Spalten) Datensätzen getestet.

Es hat etwa 5 Minuten gedauert, die Lösung in PQ in meine eigene Datei zu integrieren und die Spalten anzupassen. Die Abfrage der Datei mit PQ war in ca. 40 Sekunden erledigt.

Beide Lösungen liefern genaue und präzise Ergebnisse. Aber es wäre nicht richtig, ein Detail in deiner Lösung auszulassen. Die Tatsache, dass du das Ereignis in zwei Gruppen unterteilt hast, ist mir zuerst aufgefallen. Aber bei der zweiten Gruppe ist der Grund sehr klar und deutlich. Das erhöht die Klarheit und Verständlichkeit des Prozesses.

Die PQ-Lösung von Luschi hat mein Problem gelöst, vielen Dank auch an ihn. Aber ich finde deine Lösung ein bisschen verständlicher.


Nochmals vielen Dank an euch beide, dass ihr mir geholfen habt. Ich wünsche euch ein frohes neues Jahr !



Viele Grüße,
Johann
Anzeige

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige