Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Verschiedene Zahlen in einer Spalte auslesen und mit anderer Zelle verknüpfen
#1
Hallo,

bräuchte etwas hilfe, habe zwei Spalten in einer (Spalte A) stehen viele Zahlen getrennt durch unterschiedliche Zeichen in der anderen (Spalte B) nur eine Zahl. Ich suche nun eine möglichst einfache Möglichkeit jede einzelne Zahl aus A jeweils mit der Zahl aus B zu verknüpfen und in zwei Spalten untereinander darzustellen. Am besten den screenshot begutachten, links ist der Ist-Zustand, rechts der Soll-Zustand.
Danke!

?mage
to top
#2
Hallo Frankie,

das sind zu wenige Infos.

Was bedeutet unterschiedliche Zeichen? Kann man sie eingrenzen? Sind es nur die, die man im Screenshot sieht?
Ist der Doppelpunkt immer vorhanden? Beginnen die Zahlen dann immer nach dem Doppelpunkt?

Wieviele Datensätze können es sein?

Kannst Du VBA einsetzen?
Gruß Atilla

Excel 2007
to top
#3
Hallo,

es sieht immer so aus wie auf dem screenshot, erst der : und dann die zahlen mit - und , getrennt. Das einzige was sich ändert sind die Buchstaben und die Anzahl der Stellen der Zahlen.
Maximal 1500 unterschiedliche Zahlen pro Excel Datei, verteilt über unterschiedlich viele Zeilen.

Ich hatte mal einen VBA Kurs, aber wie ich festgestellt habe, ging das meiste was ich gelernt habe ins Nirvana, da ich nie wieder VBA benutzt habe nach dem Kurs.
to top
#4
Hallo Frankie,

dann teste mal folgenden Code:

Code:
Option Explicit

Sub zusammenführen()
   Dim i As Long, j As Long, zZ As Long
   Dim lngZ As Long
   Dim arr
   Application.ScreenUpdating = False
   Columns("D:E").ClearContents  'Inhalte der Spalten D:E löschen zur Aufnahme der neuen Werte
   lngZ = Cells(Rows.Count, 1).End(xlUp).Row
   zZ = 2 ' ab Zeile 2 werden Werte reingeschrieben
   For i = 2 To lngZ
      arr = Split(Replace(Split(Cells(i, 1), ": ")(1), ",", "-"), "-")
      If UBound(arr) Then
         For j = LBound(arr) To UBound(arr)
            Cells(zZ, 4) = arr(j)
            Cells(zZ, 5) = Cells(i, 2)
            zZ = zZ + 1
         Next j
      End If
   Next i
   Application.ScreenUpdating = True
End Sub

Der Code ist sehr einfach und nicht unbedingt die schnellste Variante.
Wenn die Performance nicht stimmen sollte, dann melde Dich noch ein mal.
Gruß Atilla

Excel 2007
to top
#5
Hallo Atilla,

danke für den Einsatz.
Der Code funktioniert soweit, bis auf ein Problem, bei der letzt Zahl wird auch das ) mit übertragen.

Habe jetzt eine Stunde lang auf den Code gestarrt um ihn nachvollziehen zu können, denke bis auf die Sortierung die hinter der arr variable liegt habe ich alles begriffen.
Könntest vielleicht erklären was genau in dieser Zeile passiert und wie ich das ) weg bekomme: Split(Replace(Split(Cells(i, 1), ": ")(1), ",", "-"), "-")

Und noch etwas, oben wird ja der Bereich in dem das script laufen mit bis zur letzten beschriebenen Zeile definiert. Das gibt auch Probleme, weil in der Tabelle auch zwischendrinnen leere Zeilen sein können und dann klappt es nicht mehr, wie könnte mand as noch lösen?
to top
#6
Hallo Frankie,

dann teste folgenden Code:

Code:
Sub zusammenführen()
   Dim i As Long, j As Long, zZ As Long
   Dim lngZ As Long
   Dim arr
   Application.ScreenUpdating = False
   Columns("D:E").ClearContents  'Inhalte der Spalten D:E löschen zur Aufnahme der neuen Werte
   lngZ = Cells(Rows.Count, 1).End(xlUp).Row
   zZ = 2 ' ab Zeile 2 werden Werte reingeschrieben
   For i = 2 To lngZ
      If Cells(i, 1) <> "" Then
         arr = Split(Replace(Split(Cells(i, 1), ": ")(1), ",", "-"), "-")
         If UBound(arr) Then
            For j = LBound(arr) To UBound(arr)
               Cells(zZ, 4) = Replace(arr(j), ")", "")
               Cells(zZ, 5) = Cells(i, 2)
               zZ = zZ + 1
            Next j
         End If
      End If
   Next i
   Application.ScreenUpdating = True
End Sub

Zitat:Könntest vielleicht erklären was genau in dieser Zeile passiert und wie ich das ) weg bekomme: Split(Replace(Split(Cells(i, 1), ": ")(1), ",", "-"), "-")

Der Ausdruck wird von der innersten Klammer nach außen abgearbeitet.
Split trennt Inhalte nach vorgegebenen Zeichen.
Zuerst wird der Zellinhalt nach Doppelpunkt mit folgendem Leerzeichen getrennt. Es entsteht ein Array mit zwei werten.
Auf Dein eingestelltes Beispiel bezogen ist es einmal "(Pl1: " und einmal "T13202-T13202,T13201-T13202)"
Das macht dieser Ausdruck: Split(Cells(i, 1), ": ")
Arrays sind Null basiert, das heißt das erste Element ist das "Nullte" und das zweite ist das "Erste"
Dass ich die Zahlen im zweiten Element brauche sage ich, indem ich hinter dem Ausdruck in Klammern eine 1 schreibe: Split(Cells(i, 1), ": ")(1)

Da die Zahlen mit unterschiedlichen Zeichen getrennt sind, vereinheitliche sie, indem ich Kommata mit Bindestrich ersetze: Replace(Split(Cells(i, 1), ": ")(1), ",", "-")
Nun Splitte ich sie ein letztes mal nach Bindestrich: Split(Replace(Split(Cells(i, 1), ": ")(1), ",", "-"), "-")
Somit habe ich ein Array mit 4 Elementen, die ich in einer Schleife abarbeite.
Die Klammer hatte ich übersehen. Die kann man dann noch einmal mit Replace beim durchlaufen der Elemenete des Array einfach mit "nichts" ersetzen: Replace(arr(j), ")", "")

Damit Leerzellen nicht betrachtet werden, habe ich um das Ganze noch eine IF Abfrage gebaut, die schaut ob die Zelle nicht leer ist.

In der Onlinehilfe findest Du auch Erklärungen zur Split und Replace Funktion.
Wenn Du im Code den Cursor innerhalb eines der Begriffe setzt und die Funktionstaste F1 betätigst, gelangst Du direkt zu den betreffenden Seiten.
Gruß Atilla

Excel 2007
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
Question Zellwert zu Formel in anderer Zelle machen -> 4x4 zu =4x4 o0Julia0o 38 665 23.02.2017, 11:26
Letzter Beitrag: Rabe
  Datum mit Text in einer Spalte Emtec 7 76 21.02.2017, 12:48
Letzter Beitrag: Emtec
  Datenfeld aus Tab 2 mit Tab 1 verknüpfen herr_bert 14 186 20.02.2017, 13:01
Letzter Beitrag: herr_bert
  Excel: Aus einer Spalte mehrere machen, Filtern, Zeitstempelzugehörigkeit Metal008 11 150 17.02.2017, 15:59
Letzter Beitrag: shift-del
Question Wert aus anderer Datei per Feldnamen übernehmen o0Julia0o 12 204 16.02.2017, 12:44
Letzter Beitrag: Peter
  Zahlen aus mehreren Zellen zusammenfügen / Zahlen auch mit 0 beginnend JoergPorsch 3 79 09.02.2017, 22:32
Letzter Beitrag: JoergPorsch
  Letzten Wert eines definierten Bereiches in einer Spalte finden JoergPorsch 8 124 09.02.2017, 21:44
Letzter Beitrag: shift-del
  Kombinationen von einer Spalte in 3 Spalten darstellen Razor1993 3 108 08.02.2017, 19:30
Letzter Beitrag: lupo1
  Zelle auslesen und in Formel ausgeben dertommy 17 239 08.02.2017, 14:20
Letzter Beitrag: dertommy
  Tabellenblatt 1:1 in anderer Datei inkl. Aktualisierung kazzz 3 89 08.02.2017, 12:28
Letzter Beitrag: Palomino

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste