Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
944to948
944to948
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sverweis

Forumthread: Sverweis

Sverweis
28.01.2008 13:15:00
Lilli
Hallo Excel-Experten,
ich habe zwei Tabellenblätter die ich zu einem zusammen fassen möchte. Das funktioniert mittels dem Sverweis auch relativ gut ist allerdings relativ umständlich (auf grund der Masse der Daten).
Formel: =WENN(ZEILEN($1:1)>ZÄHLENWENN(B:B;"206190045");"";INDEX(A:A;KKLEINSTE(WENN(B$1:B$1000="206190045";ZEILE($1:$1000));ZEILE(A1))))
Nun möchte ich dieses Sverweis irg.wie mittels Vba automatisieren oder eine ähnliche Funktion mittels Vba erstellen.
Meine Tabellenblätter sehen in etwa so aus:
1.Blatt | * 2.Blatt
auftr.nr. | arbeitsstunde | *auftr.nr. | leistung
2000456 | 3 | * 2000456 | p000
2000456 | 5 | * 2110455 | p081
2110455 | 1 | *2110455 | p053
2110451 | 2 |
und heraus kommen soll dies in einem Blatt
auftr.nr. | arbeitsstunde | leistung
2000456 | 3 | p000
2000456 | 5 |
2110455 | 1 | p081
2110455 | | p053
2110451 | 2 |
Ich habs schon versucht mittels Recorder aufzunehmen:

Sub Makro18()
Selection.FormulaArray = _
"=IF(ROWS(R1:R)>COUNTIF(C[-3],R[1]C[-2]),"""",INDEX(C[-4],SMALL(IF(R1C[-3]:R1000C[-3]=R[ _
1]C[-2],ROW(R1:R1000)),ROW(RC[-4]))))"
Selection.AutoFill Destination:=Range("E1:E29"), Type:=xlFillDefault
Range("E1:E29").Select
End Sub


Nun möchte ich das er automatisch durchläuft, auftragsnummern zuordnet
und mir die Spalte wenn kein Wert vorhanden auch leer lässt
Hilfe!

Anzeige

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sverweis
28.01.2008 13:40:00
Jan3
Hi Liili,
Eine Beispielmappe mit den beiden Tabellenblätter wäre sinnvoll.
Jan

AW: Und jetzt?
28.01.2008 14:59:42
Chris
Sorry Lilli,
aber was soll man jetzt damit anfangen?
Da stehen die Werte nicht wie beschrieben drin. Mach mal ne kurze, verständliche Erklärung dazu, damit man weiß, was du genau willst.
Gruß
Chris

AW: Und jetzt?
28.01.2008 15:16:00
Lilli
Hallo Chris,
doch sie stehen drin.
Bsp. gleich die 1. Auftragsnummer(206370022) der Tabelle1 kommt ganze 57 mal vor
die gleiche Auftragsnummer in Tabelle2 nur 2 mal
ich möchte beide Tabellen zusammen gefasst haben, mehr nicht. Die Auftragsnummern doppeln sich bzw. kommen in der anderen Tabelle gar nicht vor.
ich möchte die Auftragsnummer stehen haben und den dazugehörigen Datensatz (auch mit leeren Spalten falls nicht vorhanden) und das ganze automatisiert irg.wie
denn jede auftragsnummer neu dem sverweis zuordnen bzw. feststellen ob die auftragsnummer überhaupt existiert - ist manuell relativ umständlich (bei der datei handelt es sich auch nicht um echtdaten - das wären ein paar mehr...)

Anzeige
AW: Und jetzt?
28.01.2008 15:26:34
Chris
Servus Lilli,
soweit so gut.
Aber:
Du hast in Tabelle2 die Nummer 2 mal stehen mit AU-18 und NI0520619001. In Tabelle1 stehen keine Ahnung wie viel.
Was soll dann zu welchem Wert zugeordnet werden. Bekommt jeder Wert in Tabelle1 das AU... und NI... zugeordnet, oder wie soll das dann aussehen?
Das ist mir immer noch nicht so klar.
Gruß
Chris

Anzeige
AW: Und jetzt?
28.01.2008 15:43:22
Lilli
die Auftragsnummern zu einander sollen zugeordnet werden
d.h.
Bsp.
ich hätte in SpalteA 57 mal die Auftragsnummer stehen, in SpalteB 57 mal die dazugehörigen Arbeitsstunden und in SpalteC nur 2 Leistungen und die restlichen 55 Felder sollten leer bleiben (oder ausgenullt)
und dann sollte die nächste Auftragsnummer kommen - so das ich eine Übersicht habe was genau zu diesem Auftrag gehört (Leistungen und Arbeitsstunden sind außer der Auftragsnummer unabhängig von einander)

Anzeige
AW: Und jetzt?
28.01.2008 16:10:00
Chris
Sorry Lilli,
versteh ich trotzdem nicht.
Du hast sagen wir in Tabelle1 57 Auftragsnummern mit 57 verschiedenen Arbeitsstunden stehen und in Tabelle2 2 Leistungen zu diesen Auftragsnummern.
Sollen dann neben allen 57 Einträgen die Leistungen stehen, oder was.
Vielleicht steh ich ja am Schlauch, aber ich versteh's nicht.
Gruß
Chris

Anzeige
AW: Und jetzt?
28.01.2008 16:16:56
Lilli
ja genau. es sollen neben den 57 nur insgesamt 2 Leistungen stehen und sich nicht wiederholen
Hauptsache sie sind einmal der auftragsnummer zugeordnet
Tut mir leid besser kann ichs einfach nicht erklären

AW: Und jetzt?
28.01.2008 21:04:44
Chris
Servus Lilli,
hab auch noch eins:

Sub suche()
Dim suche As Range, Finde As Range
Dim letzte As Long
Dim i As Integer, nxt As Integer
Set Finde = Sheets(1).Range("A:A")
letzte = Sheets(2).Range("A65536").End(xlUp).Row
letzte1 = Sheets(1).Range("A65536").End(xlUp).Row
For i = 2 To letzte
Set suche = Finde.Find(what:=Sheets(2).Cells(i, 1), LookAt:=xlWhole)
If Not suche Is Nothing Then
If suche.Offset(0, 2) = "" Then
suche.Offset(0, 2) = Sheets(2).Cells(i, 2)
Else
For nxt = suche.Row To letzte1
If Sheets(1).Cells(nxt, 1) = suche And Sheets(2).Cells(i, 2)  Sheets(1).Cells(nxt,  _
3) Then
If Sheets(1).Cells(nxt, 3) = "" Then
Sheets(1).Cells(nxt, 3) = Sheets(2).Cells(i, 2)
Exit For
End If
Else
Exit For
End If
Next nxt
End If
End If
Next i
End Sub


Makro in Modul Tabelle1.
Gruß
Chris

Anzeige
genau das!
29.01.2008 08:18:00
Lilli
ganau das war es was ich meinte.
ein ganz ganz ganz großes Danke kann ich nur sagen :)
Gruß Lil

AW: gern geschehen. o.T
29.01.2008 15:07:36
Chris
.

AW: gern geschehen. o.T
30.01.2008 11:52:00
Emma
Sag mal könntest du mir noch klären wie dieser code funktioniert (auskommentieren).
Wäre echt super lieb!
Vllt. kann ich mir nächstes mal selbst so etwas schreiben wenn ich hintergestiegen bin bzw. hab einen größeren Ansatz (irg.wo muss ich ja mal anfangen)

Anzeige
AW: Und jetzt?
30.01.2008 13:05:00
Chris
Servus,

Sub suche()
Dim suche As Range, Finde As Range ' Variablendeklaration für die Suchvariablen
Dim letzte As Long, letzte1 As Long ' letzte beschriebene zeile
Dim i As Integer, nxt As Integer ' Laufvariablen
Set Finde = Sheets(1).Range("A:A") ' Suchrange
letzte = Sheets(2).Range("A65536").End(xlUp).Row ' letzte in Sheet2, spalte A
letzte1 = Sheets(1).Range("A65536").End(xlUp).Row  letzte in Sheet1, spalte A
For i = 2 To letzte ' Suchschleife Suchwerte stehen in Spalte A (Sheet1), gesucht wird der  _
Zelleintrag aus Spalte A sheet (2)
Set suche = Finde.Find(what:=Sheets(2).Cells(i, 1), LookAt:=xlWhole) ' suche festlegen ( _
what:=)
If Not suche Is Nothing Then ' wenn gefunden
If suche.Offset(0, 2) = "" Then' dann, wenn 2 Spalten neben dem Suchbegriff (spalteC),  _
kein Eintrag ist
suche.Offset(0, 2) = Sheets(2).Cells(i, 2) ' dann Wert aus spalte B aus Sheet(2) in  _
C Shet(1)eintragen
Else ' wenn nix frei
For nxt = suche.Row To letzte1 ' in Sheet(1) freie zelle in Spalte C suchen, wenn  _
der Suchbegriff passt und der aktuelle B-Wert noch nicht vorhanden ist in Spalte C Sheet(1), dann
If Sheets(1).Cells(nxt, 1) = suche And Sheets(2).Cells(i, 2)  Sheets(1).Cells( _
nxt,  _
3) Then
If Sheets(1).Cells(nxt, 3) = "" Then
Sheets(1).Cells(nxt, 3) = Sheets(2).Cells(i, 2) ' eintragen
Exit For ' Unterschleife verlassen
End If
Else ' wenn A-wert nicht gleich und B-wert in C schon vorhanden, dann auch raus
Exit For
End If
Next nxt
End If
End If
Next i ' durchlaufen bis letzter zelleintrag überprüft
End Sub


Hoffe, du verstehst es.
Gruß
chris

Anzeige
AW: Sverweis
28.01.2008 15:09:00
Jan3
Hi,
So richtig komme ich nicht mit Deinem Beispiel klar.
Nach welchem Prinzip soll bei der Vielzahl der gleichen AuftragsNr. die Stunden und die Leistungen in einem dritten Tabellenblatt zusammengeführt werden?
Jan

AW: Sverweis
28.01.2008 15:25:00
Lilli
Hallo Jan3,
nach gar keinen wirklichen Prinzip
ein Auftrag hat n-Leistungen und n-Arbeitsstunden, daher doppeln sie sich so seltsam
ich möchte nun nachvollziehen können welche Arbeitsstunden und Leistungen genau auf einem Auftrag gebucht wurden und daher die Sortierung nach Auftrag.
Also
links die Spalte Auftrag und rechts dann die anderen beiden, wenn kein Wert in einer Spalte vorhanden ist, dann die Zelle leer lassen
Danke für die Hilfe

Anzeige
AW: Sverweis
28.01.2008 15:50:00
Ulf
Hallo Lilli
hab mal gebastelt
Ich hoffe du meinst das so ?

Sub verbinden()
Dim Bereich1
Dim Bereich2
Dim MyArray
Dim i As Long, x As Long, z As Long
Dim LZ1 As Long, LZ2 As Long
LZ1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
LZ2 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
Bereich1 = Sheets(1).Range("A2:B" & LZ1)
Bereich2 = Sheets(2).Range("A2:B" & LZ2)
ReDim MyArray(LZ1, 3)
MyArray(0, 0) = Sheets(1).Cells(1, 1)
MyArray(0, 1) = Sheets(1).Cells(1, 2)
MyArray(0, 2) = Sheets(2).Cells(1, 2)
For i = 1 To LZ1 - 1
MyArray(i, 0) = Bereich1(i, 1)
MyArray(i, 1) = Bereich1(i, 2)
For x = 1 To LZ2 - 1
If Bereich1(i, 1) = Bereich2(x, 1) Then
MyArray(i, 2) = Bereich2(x, 2)
Exit For
End If
Next x
Next i
Sheets(3).Range("A1:C" & LZ1 + 1) = ""
Sheets(3).Range("A1:C" & LZ1 + 1) = MyArray
End Sub


mfg Ulf

Anzeige
AW: Sverweis
28.01.2008 16:04:00
Lilli
ja und auch wieder nein.
in SpalteC wieder holen sich ungewollt meine Leistungen.
Bsp.
206370022 hab ich zwar in 57 Zeilen Arbeitsstunden aber nur 2 Leistungen
es ist wichtig das ich die 57 Stunden des Auftrags ausgegeben bekomm und dazu nur 2 Leistungen und den Rest der Spalte bis zu nächten Auftragsnummer irg.wie leer gelassen wird oder ausgenullt.
Bei deiner Variante wiederholt sich leider die erste Leistung in dem Auftrag und die zweite wird erst gar nicht dem Auftrag zu geornet.
Tut mir leid das ich es nicht besser beschreiben kann.
Und noch mal Danke... ich muss das irg.wie heut noch hin gebastelt bekommen

Anzeige
AW: Sverweis
28.01.2008 19:33:55
Ulf
Hallo Lilli
Ich habe jetzt was zusammengestümpert.
Ich denke mal du meinst das so.

Sub verbinden()
Dim x1 As Long, x2 As Long, x3 As Long, y, z
Dim LZ1 As Long, LZ2 As Long, LZ3 As Long, Bereich
Application.ScreenUpdating = False
Sheets(3).Activate
LZ1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
LZ2 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
Bereich = Sheets(1).Range("A1:B" & LZ1)
Sheets(3).Range("A1:C" & LZ1 + LZ2 + 1) = ""
Sheets(3).Range(Cells(1, 1), Cells(LZ1, 2)) = Bereich
Sheets(3).Cells(1, 3) = Sheets(2).Cells(1, 2)
For x2 = 2 To LZ2
y = 0
For x1 = 2 To LZ1 + LZ2
If Sheets(3).Cells(x1, 1) = Sheets(2).Cells(x2, 1) And Sheets(3).Cells(x1, 3) = ""  _
Then
Sheets(3).Cells(x1, 3) = Sheets(2).Cells(x2, 2)
y = y + 1
Exit For
End If
Next x1
If y = 0 Then
LZ3 = Sheets(3).Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(3).Cells(LZ3, 1) = Sheets(2).Cells(x2, 1)
Sheets(3).Cells(LZ3, 3) = Sheets(2).Cells(x2, 2)
End If
Next x2
Range("A2:C" & LZ3 + 1).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A1").Select
Application.ScreenUpdating = True
End Sub


mfg Ulf

Anzeige
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige