Microsoft Excel

Herbers Excel/VBA-Archiv

Sortieren per Makro


Betrifft: Sortieren per Makro von: May
Geschrieben am: 10.08.2018 17:25:46

Hallo Excel Experten,

ich hoffe Ihr könnt mir hier weiterhelfen.
Mein Problem ist folgendes:

Es gibt eine Reihe von Daten aus einer Maschine, diese sind mit einem Zeitstempel versehen.
Aufgezeichnet wird z.B. von 6:59 Uhr am 9.8.18 bis zum 10.8.18 um 6:58.

Mein Macro macht bisher alles was erforderlich ist um diese Daten auszuwerten,
was ich allerdings nicht schaffe ist es die Daten so zu sortieren das in meinem Excel File es zb. ab Spalte H2 mit 6:59 Uhr beginnt vom vorherigen Tag bis ca. Mitternacht aufsteigend bis 23:59 Uhr und dann mit dem aktuellen Tag also z.B. 00:01 Uhr bis um 6:59 Uhr zu sortieren. Das leidige ausschneiden und an der benötigten Stelle einfügen ist nervenaufreibend, da dadurch des öfteren die Formatierung sowie Zellbezüge verloren gehen.

Sortieren von 0:00 bis 23:XX Uhr kann ich auch per Makro, aber eben das sortieren so wie ich es benötige leider nicht, da ich keinen Bezug durch ein Datum herstellen könnte, da in der Quelldatei keines mit angegeben wird.

Über jede Idee wie man dieses Problem angehen könnte bin ich Dankbar.

Grüße

May....

PS: speziell an Hajo, dieser hat mit seinen Ansätzen hier im Forum mir schon oft, wenn auch noch kein offizieller User, das Leben erleichtert.

  

Betrifft: AW: Sortieren per Makro von: ChrisL
Geschrieben am: 10.08.2018 17:44:17

Hi May

Addiere Datum und Zeit, damit solltest du sortieren können.

cu
Chris


  

Betrifft: AW: Sortieren per Makro von: May
Geschrieben am: 11.08.2018 09:06:39

Hallo Chris,

ich hatte schon versucht beides zusammen zu bringen, aber ohne nennenswerten Erfolg. Ich habe ja in meiner Quelldatei keine Datumsangabe dabei.
Hättest Du oder jemand anderes einen Lösungsansatz für mich?

Wie müsste ich das in VBA realisieren? Bzw. wie müsste der dazu gehörige Code aussehen, denn mit der Aufzeichnung gehts definitiv nicht, da der Tagessprung in den Daten variabel ist.

Grüße
May


  

Betrifft: AW: Sortieren per Makro von: Daniel
Geschrieben am: 11.08.2018 09:14:26

HI
dann musst du eine Hilfsspalte hinzufüen, in welcher du per Formel allen Uhrzeiten, die kleiner sind das der Aufzeichnungstart, eine 1 hinzuaddierst und dann nach dieser Hilfsspalte sortierst.
Gruß Daniel


  

Betrifft: und wie wäre es... von: Oberschlumpf
Geschrieben am: 11.08.2018 09:14:29

Hallo,

...mit einer Bsp-Datei per Upload?

Ciao
Thorsten


  

Betrifft:   von:  
Geschrieben am: 11.08.2018 10:58:27

 


  

Betrifft: AW:   von: May
Geschrieben am: 11.08.2018 14:36:03

https://www.herber.de/bbs/user/123272.zip

Bitte schön


Grüße


  

Betrifft: AW:   von: Sandra
Geschrieben am: 11.08.2018 15:04:07

Hallo May

Ich habe mir die Datei angeschaut und komme zu dem Schluss, dass ohne die Einbindung eines Datums dies nicht möglich sein wird. Schließlich weiß Excel nicht zu welchem Datum die Uhrzeit gehört.

Selbst wenn die Rohdaten in der richtigen Reihenfolge vorliegen sollten wird diese nach Ausführung einer Sortierung nicht mehr wiederherstellbar sein.

Gruss

Sandra


  

Betrifft: AW:   von: May
Geschrieben am: 11.08.2018 15:13:19

Hallo Sandra,

ich spielte auch mit dem Gedanken, über eine Art Filter zu arbeiten, mit einem Makro. Man schaut wo der Übergang von 23 zu 0 ist. dann alles ab 0:00 bis zur Zeile 2 ausschneiden und am Ende der Datei bzw. nach dem letzten Eintrag (Übergang von 6:xx zu 00:00) wieder einfügen.


Im Moment sehe ich wie man so schön sagt den Wald vor lauter Bäumen nicht. vielleicht kann mir doch der ein oder andere mir weiter helfen.

Lieben Gruß
May


  

Betrifft: AW:  geht nicht - gibts nicht von: Fennek
Geschrieben am: 11.08.2018 16:14:10

Hallo John,

wie gesagt, es bedarf einer Hilfsspalte, die die Tage numeriert. Eine Formel mit =r >& r-1 sollte helfen.

mfg

(da müssen aber die Formelspezialisten ran, mit VBA könnte ich es, aber bei dieser Aufgabe sind Formeln einfacher)


  

Betrifft: AW:   von: May
Geschrieben am: 11.08.2018 17:07:06

Fennek,

in der Original Datei hängt ein komplettes Makro, das diese Aufgabe mit bewältigen soll. In diesem File werden so nach und nach alle Formeln ersetzt. Es gibt bei uns immer wieder Spezialisten die das Original File zerschießen. Das hoch geladene dient ja nur zur Verdeutlichung was ich benötige. Denn ich bekomme in VBA es zwar hin der Reihe nach zu sortieren aber nicht den Fall den ich benötige.
Bist Du sicher das ich da mit einer Formel das angehen sollte?

Gruß


  

Betrifft: AW:  Hilfspaltenformel wäre wirklich einfach ... von: neopa C
Geschrieben am: 11.08.2018 17:16:28

Hallo May,

... z.B. in J2 so:

=KÜRZEN(J3;0)+(H2 < H3)+H2 

und Formel durch Doppelklick nach unten kopiert. Aus der VBA-Lösung halte ich mich allerdings prinzipiell heraus.

Gruß Werner
.. , - ...


  

Betrifft: AW:  Hilfspaltenformel wäre wirklich einfach ... von: May
Geschrieben am: 11.08.2018 17:27:16

Hallo neopa C,

damit rechne ich die Zeit um, wenn ich mich nicht irre? könnte funktionieren. Evtl. kann ich daraus ein Algorithmus basteln, mit dem ich nach den Kriterien
Filtern kann um danach zu sortieren.

Wenn nun noch jemand einen VBA Ansatz hat um mir auf die Sprünge zu helfen.. wäre das WE gerettet

Danke an alle Helfer, im Voraus

Grüße



  

Betrifft: AW:  Sichern! von: Matthias L
Geschrieben am: 11.08.2018 17:27:05

Hallo

Es gibt bei uns immer wieder Spezialisten die das Original File zerschießen
Die Antwort ist Sichern !!! .

Denn:
Nicht jeder Excelnutzer kann auch mit Excel umgehen.

Gruß Matthias


  

Betrifft: AW:  Sichern! von: May
Geschrieben am: 11.08.2018 17:31:20

Rate Mal was die "Spezialisten" mit dem File machen, nach dem sie es zerschossen haben...

Richtig, sie sichern es!..

Deswegen soll das ganze ja von einem Macro erledigt werden. Die Datei ist am Ende so klug das sich das Macro selbst löscht.
Dann muss ich nicht ständig das File wieder herausgeben und kann meinen anderen Pflichten nach kommen

Grüße


  

Betrifft: Das kann der "DAU" aber auch verhindern ! owT von: Matthias L
Geschrieben am: 11.08.2018 18:15:35




  

Betrifft: AW: Das kann der "DAU" aber auch verhindern ! owT von: May
Geschrieben am: 12.08.2018 08:23:17

Hallo Matthias L,

wenn Du nichts konstruktives zu der Problemlösung beizutragen hast, dann bitte ich Dich derartige Kommentare einfach zu unterlassen.
Es ist nicht hilfreich wenn Du dich derart äusserst, oder implizierst das alle anderen "Dumm" sind, weil sie nicht sichern oder was auch immer nach Deiner Ansicht nach richtig wäre.
Du kennst die näheren Umstände nicht warum, oder weshalb etwas auf eine bestimmte Art gelöst werden soll, daher solltest Du lieber gar nichts sagen, wenn Du nichts zum Thema beitragen kannst.


  

Betrifft: Unterstellungen sollte man vermeiden, also ... von: Matthias L
Geschrieben am: 12.08.2018 10:05:56

Vorsicht!

Zitat
oder implizierst das alle anderen "Dumm" sind

Ich lass mir hier nichts derartiges unterstellen.
Das hast Du geschrieben, nicht ich.

Viel Erfolg noch beim Sichern.


  

Betrifft: AW: Unterstellungen sollte man vermeiden, also ... von: Oberschlumpf
Geschrieben am: 12.08.2018 10:34:27

...dann solltest du dich klar + deutlich so ausdrücken, dass nur eine Interpretation deiner Aussage möglich ist, Matthias^^

Ich weiß, was May meinte. Dein Hinweis mit "DAU...usw" kam auch bei mir so an, dass du May für dumm hältst.

Ja, reg dich jetzt wieder ab. Nun weiß ich ja, dass du es SO nicht gemeint hast. Aber es kann halt auch SO verstanden werden.

Um Missverständnisse zu vermeiden, könntest du z Bsp anstelle von "owT" eben eine genauere Erklärung dessen, was du meinst, hinzufügen.

Ciao
Thorsten


  

Betrifft: das war klar und deutlich ... owT von: Matthias L
Geschrieben am: 12.08.2018 10:44:10




  

Betrifft: da du ja nicht... von: Oberschlumpf
Geschrieben am: 12.08.2018 11:08:24

...der Einzige mit dieser Meinung bist, ist "klar + deutlich" wohl doch etwas Anderes^^


  

Betrifft: klar und deutlich ... (§ 187 StGB) von: Matthias L
Geschrieben am: 12.08.2018 12:15:50

... ist das was ich schreibe und schreiben werde.
Was Andere dann daraus machen ist etwas Anderes.

Ich habe hier nur darauf explizit hingewiesen, das man mir nichts
unterstellen darf, was ich nicht geschrieben habe!

Das nennt sich nämlich dann Verleumdung (§ 187 StGB)


  

Betrifft: hach jaa..*seufzzz*...du Armer du..owT von: Oberschlumpf
Geschrieben am: 12.08.2018 13:20:29




  

Betrifft: willst das wohl nur letzte Wort haben ... owT von: Matthias L
Geschrieben am: 12.08.2018 13:44:11




  

Betrifft: AW: willst das wohl nur letzte Wort haben ... owT von: Oberschlumpf
Geschrieben am: 12.08.2018 14:03:43

...haste schon mal über Korrekturlesen bzgl deutscher Grammatik nachgedacht? :-))


  

Betrifft: soll ich mal im Archiv bei Dir suchen ? von: Matthias L
Geschrieben am: 12.08.2018 14:39:38

.


  

Betrifft: AW: soll ich mal im Archiv bei Dir suchen ? von: Oberschlumpf
Geschrieben am: 12.08.2018 15:04:05

wenn du dich dann besser fühlst :-)


  

Betrifft: anscheinend suchst Du ja nur Streit mit mir .. owT von: Matthias L
Geschrieben am: 12.08.2018 15:29:04




  

Betrifft: nö, ich beantworte nur deine Fragen ;-) owT von: Oberschlumpf
Geschrieben am: 12.08.2018 18:20:47




  

Betrifft: Nein, Du suchst nur Streit von: Matthias L
Geschrieben am: 12.08.2018 18:38:10

.


  

Betrifft: oh ohhhHHH!...DAS...boah...ist... von: Oberschlumpf
Geschrieben am: 12.08.2018 18:48:19

...eine UNTERSTELLUNG!!! :-))

Nun krieg dich wieder ein, Matthias.
Von mir aus könnten wir dieses Spiel "ICH passe mich DIR an"...denn ich tu nur das, was du tust :-) ...noch stundenlang weiterspielen....aber ich lass dir mal das ..ähh..."letzte Wort"...huch...jetzt hab ja doch ich wieder was geschrieben :-)
(jetzt bist du wieder dran...oder du verstehst endlich, was ich von Beginn an versucht habe, dir zu "zeigen":

Lass mich doch einfach..ähh..labern :-)...und reagier gar nicht weiter auf meine Texte ;-)

Naaa, schaffst du das??? :-)


  

Betrifft: ich sagte ja, Du suchst nur Streit ... owT von: Matthias L
Geschrieben am: 12.08.2018 19:09:04




  

Betrifft: AW: Dies ist ein Forum! von: Gerd L
Geschrieben am: 12.08.2018 10:32:05

Hallo S.John May!

Fragen ist kostenlos. Du hast keinen Anspruch auf nur angenehme Antworten.

Überdenke bitte beim nächsten Mal deine Level, zumal du offenbar nicht weist,
was "DAU" bedeutet u. in welchen Kontext dieser Begriff hier gestellt worden ist.

Gruß Gerd


  

Betrifft: AW: Dies ist ein Forum! von: May
Geschrieben am: 12.08.2018 12:46:45

Werter Hr. Gerd L,

mir st sehr wohl bekannt was DAU bedeutet, zudem unterstelle ich niemanden etwas.
Ich bat lediglich Hrn. Matthias L seine Kommentare sofern diese nicht Zweckmässig oder Zielführend sind zu unterlassen.
Ich mag zwar nicht wissen was owT bedeutet, oder auch ein niederes Level im Bezug auf der Mitgliedschaft in diesem Forum haben.
Aber ich muss mir auch nicht vorhalten lassen, ein DAU zu sein oder das meine "Spezialisten" DAUs wären.
Ein Forum ist dazu da um sich auszutauschen, um eine Hilfe oder Rat zu suchen.
Einen Anspruch auf angenehme Antworten habe ich sicherlich nicht, aber herabwürdigende Antworten an Menschen die man nicht kennt, sollten in einem Forum auch nichts zu suchen haben.

Ich habe seine Aussage entsprechend empfunden, hätte er sich konkret ausgedrückt, hätte man dies vermeiden können.
Sollten Sie der Ansicht sein meinen Account zu löschen, tun Sie sich keinen Zwang an. Dann suche ich mir wo anders Hilfe.

Mit freundlichen Grüßen


  

Betrifft: zudem unterstelle ich niemanden etwas ? DOCH !! von: Matthias L
Geschrieben am: 12.08.2018 13:52:41

Deine Antwort an mich:

Es ist nicht hilfreich wenn Du dich derart äusserst, oder implizierst das alle anderen "Dumm" sind,

Du unterstellst mir ich hätte das geschrieben. Habe ich aber nicht!
Ich fühle mich hier von Dir persönlich und öffentlich angegriffen.

Und all das nur weil ich Dir empfohlen habe die Datei vorher zu sichern.
Denk mal nach bevor Du um Dich schlägst.


  

Betrifft: owT = ohne weiteren Text. Oft auch oT von: lupo1
Geschrieben am: 12.08.2018 16:09:06




  

Betrifft: AW: so? von: Fennek
Geschrieben am: 11.08.2018 18:57:28

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

Formel:

=wenn(h3>h2;j2+1;j2)



  

Betrifft: AW: ... wahrscheinlich nicht ... von: neopa C
Geschrieben am: 11.08.2018 19:30:32

Hallo Fennek,

... denn Deine Sortierung in Spalte hat noch das Problem, welches May an sich verhindern möchte.

Möglicherweise hast Du auch meinen Beitrag von 17:16 übersehen? Da hatte ich (m)eine Hilfsspaltenformel (für J2) vorgeschlagen.

Dazu zuvor in der Beispieldatei von May die verbundenen Zellen in A:G auflösen. Dann noch die (Differenz-)Formel in Spalte I bis zum Datensatzende (Zeile 4449) ergänzen. Danach meine Hilfsspaltenformel in J2 einfügen und durch Doppelklick nach unten kopieren. Dann Spalte I:J kopieren und nur deren Werte dort wieder einfügen und abschließend H:J nach Spalte aufwärts sortieren.

Gruß Werner
.. , - ...


  

Betrifft: AW: ... wahrscheinlich nicht ... von: May
Geschrieben am: 12.08.2018 08:42:26

Hallo neopa ,

ich habe gestern noch ein wenig mit Deinem Vorschlag ausprobiert, er sortiert die Uhrzeit aber mit der Differenz scheint etwas nicht zu passen. Die Spalte J soll die zeitliche Differenz zwischen den einzelnen Zeitstempeln mit schreiben. Es wird danach gefiltert, Bsp. Abweichung von größer 5 Min ist automatisch eine Störung.

Die Hilfsspalte muss ich aber recht weit nach hinten verschieben,Spalte Z o.Ä. da die Spalten J bis R im original File schon belegt sind.
Würd euch ja gern die Datei geben, aber aus Datenschutz Gründen geht dies leider nicht.
Deswegen das Muster

Grüße May


  

Betrifft: AW: dazu ... von: neopa C
Geschrieben am: 12.08.2018 09:37:05

Hallo May,

... wo Du die Hilfsspalte anordnest ist völlig egal, Du musst lediglich gewährleisten, dass diese auch bis zur letzten gefüllten Datenzeile kopiert wird. Dies gilt auch für Spalte I und vor der Sortierung müssen natürlich die Formeln in Spalte I und der Hilfsspalte sowie anderer Spalten, die auch abhängige Formeln beinhalten, durch ihre Werte ersetzt werden.

Gruß Werner
.. , - ...


  

Betrifft: AW: dazu ... von: May
Geschrieben am: 12.08.2018 12:48:51

Hallo Gerd,

ich werde versuchen das umzusetzen.

Vielen Dank für den Tipp


  

Betrifft: AW: dazu ... von: Robert Werner
Geschrieben am: 13.08.2018 10:48:35

Hallo May,

mit nachstehendem Makro wird eine Hilfsspalte angelegt, in der mit einer Formel die Uhrzeit aus der Spalte H übernommen wird. Sollte diese Uhrzeit vor 06:59 sein wird noch ein Tag dazu addiert. Anschließend wird nach dieser Hilfsspalte sortiert. Ich gehe mal davon aus, dass nur in Deiner Beispieldatei verbundene Zellen waren und in der echten Datei nicht.
Da die Formeln in der Spalte Differenz dann nicht mehr passen (in der Ursprungstabelle sind die Daten ja absteigend sortiert) wird in der Spalte Differenzen eine neue Formel eingetragen. Der Bezug zur Spalte H muss eventuell an Deine Datei angepasst werden. Anschließend wird die Hilfsspalte wieder gelöscht.

Sub Sortieren()
Dim lZ As Long, lS As Long
lS = Cells(1, Columns.Count).End(xlToLeft).Column 'letzte befüllte Spalte
lZ = Range("H" & Rows.Count).End(xlUp).Row        'letzte befüllte Zeile
'Hilfsspalte nach der letzten Spalte anlegen und befüllen
Cells(2, lS + 1).Resize(lZ - 1, 1).FormulaR1C1 = "=RC[-2]+(RC[-2]<""06:59"")"

'Datenbereich nach der Hilfsspalte sortieren
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range(Cells(2, lS + 1).Resize(lZ, 1).Address), SortOn:=xlSortOnValues,  _
Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range(Cells(1, 1).Resize(lZ, lS + 1).Address)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
'in Spalte Differenz Formeln anpasssen
Range("H2").ClearContents
Range("H3").Resize(lZ - 2, 1).FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
Range("H3").Resize(lZ - 2, 1).NumberFormat = "[h]:mm:ss;@"
'Hilfsspalte löschen
Cells(2, lS + 1).Resize(lZ - 1, 1).Clear

End Sub
Gruß
Robert


Beiträge aus dem Excel-Forum zum Thema "Sortieren per Makro"