Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1500to1504
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

einfache Fingerübung mit rechts()

einfache Fingerübung mit rechts()
23.06.2016 20:24:26
MB12
Hallo zusammen,
eine Frage zum Feierabend:
In Spalte A habe ich leider wild zusammengewürfelte alphanumerische Ausdrücke. Aus denen möchte ich folgendes extrahieren:
Am Ende gibt es wenige Variationen, z.B:
1234556-11111
2345678-9999
484848-12345
484848-12345(Z)
1-1234567-22222
1-1234567-22222(Z)
toll, nicht? Das ist immer Projektnummer-Maschinennr, vorn und hinten Spezialmarker.
Am Schluß soll nur die Maschinennummer ohne Doppel in Spalte B stehen.
Schritt eins:Filtern nach Z und diese Datensätze löschen
Schritt 2 in Spalte B: =rechts(A2;5)
damit erschlage ich 90%
Schritt 3: Spalte B aufsteigend sortieren, Formel anpassen auf =rechts(A2;4), damit auch das "-" bei den 4-stelligen Maschinennummern weg ist.
Bei der Datei handelt es sich um eine Auswertung der Dauer von Arbeitsschritten (Pivot), die monatlich erweitert wird.
Text in Spalten erscheint mir zu umständlich.
Habt ihr einen Rat für mich?
Danke schön und genießt trotzdem den schönen Abend.
Gruß, Margarete

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: einfache Fingerübung mit rechts()
23.06.2016 21:47:15
Christian
Hallo Margarete,
Schritt-3 kannste dir sparen wenn du bei Schritt-2 zB folgende Formel verwendest:
=RECHTS(A2;LÄNGE(A2)-FINDEN("-";A2;3))
Alternative-1: Du könntest mit:
=WECHSELN(RECHTS(A2;LÄNGE(A2)-FINDEN("-";A2;3));"(Z)";"")
auch direkt die "(Z)" eliminieren
und dann die Doppelten rausschmeißen zB in in Spalte C mit:
http://www.excelformeln.de/formeln.html?welcher=194
Alternative-2: VBA
Alternative-3: ...
Gruß
Christian

Stichwort VBA, genauer - eine UDF, ...
24.06.2016 01:49:04
Luc:-?
…Margarete;
dann ginge das zumindest für dein Bsp mit einer pluralen MatrixFml (über alle Zellen) ggf so:
D1:D6: {=WENN(RECHTS(A1:A6)")";INDEX(VSplit(RECHTS(A1:A6;9);"-";1);;2);"")}
Die UDF VSplit (Version1.1) findest du (sicher!) direkt in einem Archiv-Thread… ;-)
Wenn das Ergebnis numerisch ist, lückenlos und sortiert sein soll, einfach noch KKLEINSTE o.ä. drumherum legen.
Evtl Rückfragen kann ich erst ab Montag beantworten.
Gruß + schöWE, Luc :-?

Anzeige
jetzt hab ich ja die Wahl...
24.06.2016 16:56:09
MB12
Hallo ihr Lieben,
danke für euer Gehirnschmalz. Ich geh dann mal spiel'n - mit den verschiedenen Ansätzen.
Und ach ja: heute im Betrieb habe ich noch festgestellt, dass ich zuerst mal glätten muss, da bei der Eingabe in Access (durch verschiedene Personen)bei einigen exportierten Datensätzen am Ende Leerzeichen drin sind. Wenn man mich die Auswertungen einfach in Access durchführen ließe - das Leben wäre so einfach.
Soweit für heute - Rückmeldung ist versprochen.
Gruß, Margarete

der Berg hat gekreißt
25.06.2016 16:42:49
MB12
Hallo Luc, hallo Christian,
wozu ein freier Samstag alles gut ist – eigentlich wollte ich alles mit Formeln lösen – Christian, deine Lösung 1 habe ich (erweitert mit *1) eingebaut, aber dann hat mich der Ehrgeiz gepackt – also nach viel Googlerei, aufzeichnen, select und activate rauswerfen jetzt VBA wie folgt:
Sub zloeschen_und_glaetten()
Dim i As Long
Dim letzteZeile As Long
Application.ScreenUpdating = False
Sheets("allex").Range("A:A").Copy
Sheets("Tabelle1").Range("A:A").Select
ActiveSheet.Paste
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) Like ("*Z*") Then Rows(i).Delete
Cells(i, 1) = Trim(Cells(i, 1).Value)   'glätten
Next i
letzteZeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Application.Calculation = xlManual   'automatische Berechnung aus
Range("B2:H2").AutoFill Destination:=Range("B2:H" & letzteZeile), Type:=xlFillDefault
Application.Calculation = xlAutomatic  'automatische Berechnung an"
Application.ScreenUpdating = True
End Sub
Der letzte Teil dient dazu, dass die ganzen Formeln immer neu übernommen werden (Zeile 2 wird immer ausgefüllt sein), da die Quelldaten jedesmal neu reinkommen mit mehr oder weniger Datensätzen.
Könnt mich mal jemand loben? (grins)
Es funktioniert, aber falls jemand noch Verbesserungsvorschläge hat- bin immer dankbar, wenn ich dazulernen kann, z.B. benötige ich überhaupt das zweite "letzte Zeile..." in dieser Form?
Hier meine Testdatei:
https://www.herber.de/bbs/user/106523.xlsm
Euch allen noch ein schönes Wochenende
Gruß, Margarete

Anzeige
AW: der Berg hat gekreißt
26.06.2016 09:24:19
Christian
hallo Margarete,
wenn du VBA als Prozedur nutzt, dann würde ich auch gleich die Maschinen-Nr. aus dem String extrahieren und beide Spalten auf einen Schlag füllen.
Bsp: wenn analog zu "1-1234567-22222" mehrere Bindestriche vorkommen können, suchst du mit "FINDEN("-";A2;3)" nach dem ersten "-" ab Position 3.
In meinem Bsp-Code unten wird am Bindestrich aufgetrennt und der letzte Block genommen "vntData(UBound(vntData))".
Option Explicit
Sub TestIt()
Dim lngLR As Long
Dim i As Long, k As Long
Dim strSrc As String
Dim strData() As String
Dim vntData
With Sheets("allex")
lngLR = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim strData(lngLR - 2, 1)
For i = 2 To lngLR
strSrc = Trim(Cells(i, 1))
If Len(strSrc) And InStr(strSrc, "Z") = 0 Then
vntData = Split(strSrc, "-")
strData(k, 0) = strSrc                          ' Quelldaten
strData(k, 1) = vntData(UBound(vntData))        ' Maschine
k = k + 1
End If
Next
End With
Sheets("Tabelle1").Cells(2, 1).Resize(k + 1, 2) = strData
End Sub
Gruß
Christian

Anzeige
Korrektur
26.06.2016 09:46:22
Christian
um die Maschinen-Nr als Zahl zu übergeben, muss das Ziel-Array als Variant deklariert werden.
Option Explicit
Sub TestIt()
Dim lngLR As Long
Dim i As Long, k As Long
Dim strSrc As String
Dim vntDst()
Dim vntMch
With Sheets("allex")
lngLR = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim vntDst(lngLR - 2, 1)
For i = 2 To lngLR
strSrc = Trim(.Cells(i, 1))
If Len(strSrc) And InStr(strSrc, "Z") = 0 Then
vntMch = Split(strSrc, "-")
vntDst(k, 0) = strSrc                       ' Quelldaten
vntDst(k, 1) = vntMch(UBound(vntMch))       ' Maschine
k = k + 1
End If
Next
End With
Sheets("Tabelle2").Cells(2, 1).Resize(k + 1, 2) = vntDst
End Sub
Gruß
Christian

Anzeige
AW: Korrektur - hatte ich noch nicht gelesen
26.06.2016 11:53:15
MB12
Hi Christian,
war so lange an der Antwort gesessen, dass ich deine zweite Antwort vor dem Absenden nicht gelesen hatte - danke schön
Gruß Margarete

AW: Korrektur - hatte ich noch nicht gelesen
26.06.2016 13:30:15
Christian
hier noch mal mit Kommentaren zum besseren Verständnis:
Option Explicit
Sub TestIt2()
Dim lngLR As Long           ' LongLastRow - letzte Zeile
Dim i As Long, k As Long    ' allg. Zähler
Dim strSrc As String        ' StringSource (String der Quell-Tabelle)
Dim vntDst() As Variant     ' VariantDestination (das Ziel-Array)
Dim vntMch As Variant       ' VariantMachine (zur Ermittlung der Maschinen-Nr in strSrc)
With Sheets("allex")
' letzte Zeile mit Eintrag in Tabelle "allex"
lngLR = .Cells(.Rows.Count, 1).End(xlUp).Row
' Dimension des Ziel-Arrays festlegen - beginnt bei 0 (0 bis lngLR - 2, 0 bis 1)
ReDim vntDst(lngLR - 2, 1)
' durchlaufe die Spalte A von "allex" bis zur letzten Zeile
For i = 2 To lngLR
' schneide etwaige Leerzeichen ab beim Eintrag in Zeile i / Spalte A
strSrc = Trim(.Cells(i, 1))
' wenn die Länge von strSrc > 0 und kein "Z" im String "strSrc" vorkommt
If Len(strSrc) And InStr(strSrc, "Z") = 0 Then
' aufsplitten von strSrc in ein Array, getrennt am "-"
' zur Ermittlung der Maschinen-Nr
vntMch = Split(strSrc, "-")
' schreibe den "getrimmten" String in die "1. Spalte" des Ziel-Arrays "vntDst"
vntDst(k, 0) = strSrc
' schreibe die Maschinen-Nr aus vntMch in die "2. Spalte" des Ziel-Arrays
' = letzter Eintrag im Array "vntMch"
vntDst(k, 1) = vntMch(UBound(vntMch))
' erhöhe den Zähler für weitere Einträge im Ziel-Array
k = k + 1
End If
Next
End With
With Sheets("Tabelle2")
' schreibe die Werte des Ziel-Arrays in die Ziel-Tabelle
.Cells(2, 1).Resize(k + 1, 2) = vntDst
' Formeln von Spalte C bis H nach unten ausfüllen
.Cells(2, 3).Resize(, 6).AutoFill .Cells(2, 3).Resize(k, 6)
End With
End Sub
Gruß
Christian

Anzeige
wow, das hätte ich nicht erwartet...,
26.06.2016 15:00:06
MB12
...lieber Christian, dass du mir das Leben so leicht machst!
Wenn ich deine Erläuterungen lese, erscheint plötzlich alles völlig logisch und verständlich.
Ganz lieben Dank nochmal - und ich verspreche, diesen Code weiterzuverwenden- nicht nur in der aktuellen Auswertung. Mein Interesse ist mehr als geweckt.
Grüßle, Margarete

da hast du mir aber einen vorgesetzt!
26.06.2016 11:51:14
MB12
Hallo lieber Christian,
hab herzlichen Dank. Das Problem mit VBA-Anfängern wie mir liegt immer am Verstehen von Codes, die man nicht selbst gebastelt hat.
Jetzt müsste ich deinen – vermutlich viel eleganteren – Code erst mal für alle Datensätze erweitern und erreichen, dass die Maschinennummer wieder zur Zahl wird.
Das mit dem vntData(UBound(vntData)) habe ich soweit halbwegs verstanden, aber beim Rest hakt es schon bei der Deklaration. Und da ich einen stets interessierten Chef habe, wird der auch mal nachfragen, wie ich welches Problem gelöst habe. Zumindest „for the time being“ werde ich wohl mit dem Holpercode leben müssen.
Das angenehme an dieser Auswertung ist, dass es sich max. um ca. 200 DS handelt, und den Einfach-Code kann ich auch auf andere Auswertungen anpassen. Ein ganz anderes Thema wäre im zweiten Schritt, ob ich es mit Datenverbindungen hinbekomme, dass ich die Daten direkt aus Access holen kann. Da muss ich aber unsere IT-Abteilung ins Boot holen.
Übrigens: Die Hürde mit den zwei Bindestrichen hatten ich schon bemerkt, ist aber m.E. gelöst, indem wir in der Formel von rechts rangegangen sind (die Maschinennummer steht immer nach dem letzten Bindestrich).
Nochmal danke und ein schönes Rest-WE ohne Stürme
Margarete
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige