habe folgendes Problem: in Liste #1 habe ich tausende von Lieferanten nach Nummern im Format 111 mit dazugehörigem Namen, der Straße, der PLZ und dem Ort; in Liste #2 habe ich nur einen Bruchteil der Lieferanten (trotzdem sehr viele!), auch nach Nummern, aber diesmal im Format 000000111 und nur mit dazugehörigem Namen.
Wie müßte eine Formel lauten, die anhand der Nummer (mit unterschiedlichen Formaten in den beiden Listen…) automatisch die Adressen aus der „großen“ Liste in der „kleinere“ Liste übernimmt??
habe folgendes Problem: in Liste #1 habe ich tausende von
Lieferanten nach Nummern im Format 111 mit dazugehörigem
Namen, der Straße, der PLZ und dem Ort; in Liste #2 habe ich
nur einen Bruchteil der Lieferanten (trotzdem sehr viele!),
auch nach Nummern, aber diesmal im Format 000000111 und nur
mit dazugehörigem Namen.
Wie müßte eine Formel lauten, die anhand der Nummer (mit
unterschiedlichen Formaten in den beiden Listen…)
automatisch die Adressen aus der „großen“ Liste in der
„kleinere“ Liste übernimmt??
Hi Heike,
Tabellenblattname: Klein
A B
1 nr name
2 1 1 1 müller
3 2 2 2 schmidt
4 5 5 5 meier
5 3 3 3 freidmann
Tabellenblattname: Gross
A B C D E
1 nr name strasse plz ort
2 000000109 n1 s1 p1 o1
3 000000110 n2 s2 p2 o2
4 000000111 n3 s3 p3 o3
5 000000112 n4 s4 p4 o4
6 000000555 n5 s5 p5 o5
Nach Durchlauf des Codes sieht dann Tabelle Klein so aus:
Tabellenblattname: Klein
A B C D E
1 nr name
2 1 1 1 müller s3 p3 o3
3 2 2 2 schmidt
4 5 5 5 meier s5 p5 o5
5 3 3 3 freidmann
Alt+F11, Einfügen —Modul, Code reinkopieren, Editor schliessen.
Start in Excel mit Extras–Makro–Makros–Ausführen…
Option Explicit
Sub tt()
Dim zeiK As Long, wsK As Worksheet, wsG As Worksheet, Such As String, spa As Integer
Dim Bereich As Range
Set wsG = Worksheets("Gross")
Set Bereich = wsG.Range("A2:E" & wsG.Range("A65536").End(xlUp).Row)
With Worksheets("Klein")
For zeiK = 2 To .Range("A65536").End(xlUp).Row
Such = Right("000000000" & Replace(.Cells(zeiK, 1), " ", ""), 9)
For spa = 3 To 5
On Error Resume Next
.Cells(zeiK, spa) = Application.WorksheetFunction.VLookup(Such, Bereich, spa, 0)
On Error GoTo 0
Next spa
Next zeiK
End With
End Sub
Hallo Heike,
wie ich das verstehe, sieht eine Tabelle(liste 2) so aus:
000000111 Heinz Meier
000000221 Ulrike Müller
die zweite (liste 1) sieht so aus:
111 Heinz Meier Berliner Str. 12 41109 Bonn
221 Ulrike Müller Mainzer Str. 13 40005 Münster
nun willst du jene adresse in die erste tabelle einfügen, ok?
ich wuerde folgendes machen: in der kleinen tabelle, (die mit den vielstelligen Nummern), eine weitere spalte neben den zahlen einfügen und in der ersten zelle folgende formel einfügen: =TEIL(A1;6;9)
A1 steht für die zelle in der 000000111 steht, 6 für die Stelle in der die erste zahl die gewüscht ist auftaucht (im beispiel erste der drei 1) und 9 (im beispiel letzte der drei 1).
mit doppelklick auf das kleine quadrat (rechts unten in der zelle) die formel für alle zellen in der spalte kopieren.
anschließend via SVERWEIS die adresse von der großen tabelle in die kleine einfügen.