Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1148to1152
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

Zeilenumbruch und sverweis in Zelle

Zeilenumbruch und sverweis in Zelle
Rainer
Hallo,
ich habe eine Exceldatei erhalten, die ich aufbereiten soll.
In der A-Spalte befinden sich in einzelnen Zellen jeweils Daten, die durch ein /-Zeichen getrennt sind:
Die Daten sind in folgender Reihenfolge eingegeben: Artikelnummer/Lieferantennummer/Preis
Dabei sind teilweise mehrere Datensätze hintereinander abgelegt.
Die Artikelnummer und Lieferantennummer haben keine feste Stellenanzahl, so das die Trennung anhand der "/" erfolgen müsste
Beispiel:
Ursprung:
A1:
001/1111/5EUR
A2:
002/2222/6EUR/003/3333/7EUR
A3:
001/1111/5EUR////007/7777/10EUR/1234/678999/9EUR
Die Daten sollen nun durch einen passenden Zeilenumbruch übersichtlicher werden:
A1:
001/1111/5EUR
A2:
002/2222/6EUR/
003/3333/7EUR
A3:
001/1111/5EUR/
///
007/7777/10EUR/
1234/678999/9EUR
Datensätze, für die keine Artikelnummer/Lieferantennummer angegeben sind sollen möglicht entfernt werden.
In einem zweiten Schritt sollen aus weiteren Tabellenblättern die Artikelbezeichnungen für die Artikelnummern und die Lieferanten für die Liefernatennummern herangezogen werde und in eckige Klammern eingefügt
werden. Da sich alles in einer Zelle befindet, funktioniert sverweis leider nicht.
Der Endzustand soll dann so ausehen:
A1:
001[Artikel001]/1111[Lieferant1111]/5EUR
A2:
002[Artikel002]/2222[Lieferant2222]/6EUR
003[Artikel003]/3333[Lieferant3333]7EUR
A3:
001[Artikel001]/1111[Lieferant1111]5EUR/
007[Artikel007]/7777[Lieferant7777]10EUR/
1234[Artikel1234]/678999[Lieferant678999]/9EUR
Gibt es eine Lösung für dieses Problem? Da eine Umwandlung per Hand sehr aufwendig ist, wäre ich euch dankbar, wenn es eine Makrolösung geben würde.
Viele Grüße
Rainer
AW: Zeilenumbruch und sverweis in Zelle
24.03.2010 10:48:17
Oberschlumpf
Hi Rainer
...ich weiß vielleicht gar keine Lösung, aber...
Um deine Darstellung exceltauglich zu machen, bedarf es auch einiges an Aufwand.
Und ich finde, unnötigen Aufwand.
Könntest du uns nicht eine Bsp-Datei zur Verfügung stellen?
Du hast die Daten doch schon in einer Datei vorliegen.
Zur Not müsstest du vielleicht die Originalwerte gegen Bsp-Werte tauschen. Aber auch das sollte ja nicht so schwer sein, oder?
Vielen Dank.
AW: Zeilenumbruch und sverweis in Zelle
24.03.2010 11:29:18
Rainer
Hallo,
vielleicht kann man die Umwandlung Schrittweise lösen, wenn erst einmal nach jedem dritten "/" ein Zeilenumbruch eingefügt werden würde.
Die Datei kann ich erst heute Abend senden, da ich sie im Moment nicht verfügbar habe.
Rainer
Anzeige
AW: Zeilenumbruch und sverweis in Zelle
24.03.2010 12:12:13
Rudi
Hallo,
teste mal:
Sub tt()
Dim i As Long, arrtmp, arrDaten(), j As Integer, k As Integer, n As Integer
ReDim arrDaten(1 To 3, 1 To 1)
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
arrtmp = Split(Cells(i, 1), "/")
For j = 1 To (UBound(arrtmp) + 1) / 3
n = n + 1
ReDim Preserve arrDaten(1 To 3, 1 To n)
For k = 0 To 2
arrDaten(k + 1, UBound(arrDaten, 2)) = arrtmp((j - 1) * 3 + k)
Next
Next
Next
arrtmp = arrDaten
ReDim arrDaten(1 To 5, 1 To n)
n = 0
For i = 1 To UBound(arrtmp, 2)
If arrtmp(1, i)  "" And arrtmp(2, i)  "" Then
n = n + 1
arrDaten(1, n) = arrtmp(1, i)
'Artikelbezeichnung
arrDaten(2, n) = WorksheetFunction.VLookup(arrtmp(1, i), Sheets("Artikel").Range("A:B"),  _
2, 0)
arrDaten(3, n) = arrtmp(2, i)
'Lieferantenname
arrDaten(4, n) = WorksheetFunction.VLookup(arrtmp(2, i), Sheets("Lieferanten").Range("A:B" _
), 2, 0)
arrDaten(5, n) = arrtmp(3, i)
End If
Next
ReDim Preserve arrDaten(1 To 5, 1 To n)
arrDaten = WorksheetFunction.Transpose(arrDaten)
Worksheets.Add.Cells(1, 1).Resize(n, 5) = arrDaten
End Sub

Gruß
Rudi
Anzeige
AW: Zeilenumbruch und sverweis in Zelle
24.03.2010 14:51:32
Rainer
Hallo Rudi,
vielen Dank für deine Rückmeldung.
Leider bricht der Code mit einer Fehlermeldung/Laufzeitfehler 1004 ab.
Habe ich etwas falsch gemacht?
Rainer
AW: Zeilenumbruch und sverweis in Zelle
24.03.2010 14:58:02
Rudi
Hallo,
mit deinen 3 Beispielzellen lief das bei mir.
Lad mal eine Datei hoch.
Gruß
Rudi
nur Hinweis
24.03.2010 17:04:07
Oberschlumpf
Hallo alle
Das war doch schon zu Beginn mein Reden!
Eine Bsp-Datei ist immer....nicht...schlecht!.
Ciao
Thorsten
AW: nur Hinweis
24.03.2010 17:47:03
Rainer
Hallo!
Soweit ich mich erinnern kann, habe ich eine Datei angekündigt, hatte sie allerdings auf die Schnelle nicht verfügbar. Ich möchte jetzt allerdings nicht über den Sinn von Beispieldateien philosophieren, sondern wäre dankbar, wenn wir uns auf mein Excel-Problem konzentrieren könnten. Rudi hat ja schon einen Ansatz, der bei mir aus unbekannten Gründen nicht funktioniert. Ich hoffe natürlich, dass Rudi sich noch einmal meldet.
@Thorsten: Falls du etwas Konstruktives beitragen kannst, wäre ich dir sehr dankbar. Aber Hinweise oder persönliche Meinungen bringen mich nicht weiter!
Rainer
Anzeige
AW: Zeilenumbruch und sverweis in Zelle
24.03.2010 20:07:12
Rudi
Hallo,
ich bau jetzt keine Fehlerroutinen ein.
Artikel und Lieferanten müssen vorhanden sein.

Sub tt()
Dim i As Long, arrtmp, arrDaten(), j As Integer, k As Integer, n As Integer
ReDim arrDaten(1 To 3, 1 To 1)
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
arrtmp = Split(Cells(i, 1), "/")
For j = 2 To (UBound(arrtmp) + 1) / 3
n = n + 1
ReDim Preserve arrDaten(1 To 3, 1 To n)
For k = 0 To 2
arrDaten(k + 1, UBound(arrDaten, 2)) = arrtmp((j - 1) * 3 + k)
Next
Next
Next
arrtmp = arrDaten
ReDim arrDaten(1 To 5, 1 To n)
n = 0
For i = 1 To UBound(arrtmp, 2)
If arrtmp(1, i)  "" And arrtmp(2, i)  "" Then
n = n + 1
arrDaten(1, n) = arrtmp(1, i)
'Artikelbezeichnung
arrDaten(2, n) = _
WorksheetFunction.VLookup(CLng(arrtmp(1, i)), Sheets("Artikel").Range("A:B"), 2, 0)
arrDaten(3, n) = arrtmp(2, i)
'Lieferantenname
arrDaten(4, n) = _
WorksheetFunction.VLookup(CLng(arrtmp(2, i)), Sheets("Lieferanten").Range("A:B"), 2, 0)
arrDaten(5, n) = arrtmp(3, i)
End If
Next
ReDim Preserve arrDaten(1 To 5, 1 To n)
arrDaten = WorksheetFunction.Transpose(arrDaten)
Worksheets.Add.Cells(1, 1).Resize(n, 5) = arrDaten
End Sub

Gruß
Rudi
Anzeige
AW: Zeilenumbruch und sverweis in Zelle
25.03.2010 09:21:47
Rainer
Hallo Rudi,
vielen Dank für deine Rückmeldung. Ich habe den Fehler abgefangen (on error resume next). Jetzt läuft das makro durch. Ich habe allerdings festgestellt, dass sich die Daten auf einem gesonderten Tabellenblatt in unterschiedlichen Zellen befinden. Tatsächlich sollen sich die Daten jeweils in einer Zelle befinden.
Habe ich etwas falsch gemacht?
Gruß
Rainer
Habe ich etwas falsch gemacht?
25.03.2010 09:36:33
Rudi
Hallo,
nein.
Aber jetzt ist es doch einfach, die Daten wie von dir gewollt zusamenzubauen.
Durch die Struktur mit den Spalten hast du erheblich mehr Möglichkeiten für die weitere Verarbeitung.
Gruß
Rudi
Anzeige
Ergänzung
25.03.2010 10:04:27
Rudi
Hallo,

Sub tt()
Dim i As Long, arrtmp, arrDaten(), j As Integer, k As Integer, n As Integer
Dim vntArt, vntKunde, arrHeader
arrHeader = Array("ArtNr", "Artikel", "LiefNr", "Lieferant", "Preis", "Text")
ReDim arrDaten(1 To 3, 1 To 1)
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
arrtmp = Split(Cells(i, 1), "/")
For j = 1 To (UBound(arrtmp) + 1) / 3
n = n + 1
ReDim Preserve arrDaten(1 To 3, 1 To n)
For k = 0 To 2
arrDaten(k + 1, UBound(arrDaten, 2)) = arrtmp((j - 1) * 3 + k)
Next
Next
Next
arrtmp = arrDaten
ReDim arrDaten(1 To 5, 1 To n)
n = 0
For i = 1 To UBound(arrtmp, 2)
If arrtmp(1, i)  "" And arrtmp(2, i)  "" Then
n = n + 1
'Artikelbezeichnung
vntArt = _
Application.VLookup(CLng(arrtmp(1, i)), Sheets("Artikel").Range("A:B"), 2, 0)
'Lieferantenname
vntKunde = _
Application.VLookup(CLng(arrtmp(2, i)), Sheets("Lieferanten").Range("A:B"), 2, 0)
arrDaten(1, n) = arrtmp(1, i)
arrDaten(2, n) = IIf(IsError(vntArt), "FEHLT!", vntArt)
arrDaten(3, n) = arrtmp(2, i)
arrDaten(4, n) = IIf(IsError(vntKunde), "FEHLT!", vntKunde)
arrDaten(5, n) = arrtmp(3, i)
End If
Next
ReDim Preserve arrDaten(1 To 5, 1 To n)
arrDaten = WorksheetFunction.Transpose(arrDaten)
With Worksheets.Add
.Range(.Columns(1), .Columns(5)).NumberFormat = "@"
.Cells(1, 1).Resize(, 6) = arrHeader
.Cells(2, 1).Resize(n, 5) = arrDaten
With .Cells(2, 6).Resize(n)
.FormulaR1C1 = _
"=TEXT(RC[-5],""000"") &""["" &RC[-4] &""]/"" &RC[-3]&""[""&RC[-2]&""]/""&RC[-1]"
.Value = .Value
End With
.Columns.AutoFit
End With
End Sub

Gruß
Rudi
Anzeige
AW: Zeilenumbruch und sverweis in Zelle
25.03.2010 10:56:43
Rainer
Hallo Rudi,
jetzt funktioniert das makro prima, auch wenn ich noch nicht ganz verstehe wie er funktioniert.
Ich habe noch einmal ein Bild hochgeladen.
Userbild
In der Spalte G habe ich die Daten so eingetragen, wie ich sie benötige. Da die Tabelle in der Zeilen- Spaltenanzahl nicht verändert werden darf wär ich dir sehr dankbar, wenn du dir das noch einmal ansehen könntest.
Viele Dank für deine Mühe
Rainer

332 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige