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

Schnittmenge zweier Spalten

Schnittmenge zweier Spalten
23.06.2014 15:21:15
Gordon
Moin moin,
ich habe zwei Spalten (A1:A10000 und C1:C8000) mit Werten gefüllt. Nun möchte ich gerne die Werte herausfinden, die sich a) in beiden Spalten befinden und b) sich nur in Spalte C befinden. Bisher habe ich das mit zwei Schlleifen abgehandelt, was auch finktioniert, aber recht lange dauert.
Gibt es noch andere Möglichkeiten um so etwas zu bewerkstelligen? Ich hatte es schon mit Union und Intersect versucht, aber irgendwie nicht hinbekommen. Ich wäre sehr dankbar, wenn da jemand eine Antwort für mich hätte.
Danke euch schon mal im vor aus....
Gruß
Gordon

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

Betreff
Datum
Anwender
Anzeige
AW: Schnittmenge zweier Spalten
23.06.2014 15:41:54
Daniel
Hi
Sortiere Spalte A aufsteigend und fügen in Spalte D folgene Formel ein, die du dann soweit runterkopierst wie in C Daten vorhanden sind:
=Wenn(SVerweis(C1;A:A;1;Wahr)=C1;"A und C";"nur C")
durch die Sortierung kann man für die Prüfung den SVerweis verwenden, welcher in sortierten Daten eine wesentlich schnellere Suchmethode anwendet als den Zeilenweisen Vergleich per Schleife.
per Autofilter kannst du dann auf die unterschiedlichen Daten zugreifen oder du sortierst C und D nach D.
und ja, auch per Makro macht man das genauso
(Sortieren , Formel einfügen und Autofilter kann man gut mit dem Recorder aufzeichnen)
Gruß Daniel

Anzeige
AW: Schnittmenge zweier Spalten
24.06.2014 09:51:22
Gordon
Hallo Daniel,
danke dir, aber ich hatte eine reine VBA-Löösung für das Probelm gesucht. Diese wurde mir ja nun von Rudi geliefert. Aber danke dir dennoch für deine Mühe! :-)
Gruß
Gordon

AW: Schnittmenge zweier Spalten
24.06.2014 13:02:49
Daniel
Hi
du kannst jeden Schritt, den du von Hand in Excel ausführst, auch per VBA ausführen.
von daher ist nach meiner Sichtweise jede Excellösung immer auch eine reine VBA-Lösung.
das von mir gezeigte Vorgehen dürfte als VBA nur unwesentlich langsamer sein als das Dictionary von Rudi, hätte diesem gegenüber aber zwei Vorteile:
1. du kannst sie auch schnell mal ohne Makro verwenden.
2. es werden nur Methoden eingesetzt, die jedem Excelanwender, der sich nicht mehr als Anfänger bezeichnet, bekannt sein sollten (Sortieren, SVerweis, Autofilter).
natürlich ist das Dictionary in der fortgeschrittenen Programmierung sehr hilfreich und wichtig, aber man sollte auch lernen, mit den Werkzeugen kreativ umzugehen, die man kennt.
Gruß Daniel

Anzeige
AW: Schnittmenge zweier Spalten
25.06.2014 09:56:47
Gordon
Hallo Daniel,
danke weiterhin für deinen Beitrag. Wie der Makrorecorder funktioniert ist mir bekannt. Auch die von dir genannten Funktionen/Formeln sind mir bekannt. Dennoch suchte ich eine Lösung, die a) die schnellste ist und vorallem b) die rein im Hintergrund abläuft und ohne jegliche Eintragungen (wie z.B. =sverweis()) auf den Sheets auskommt. Daher präferiere ich halt Rudis Lösung.
Dein Lösungsansatz ist sicher richtig, aber er komtm halt nicht dem ganz nahe was ich suchte.
Gruß
Gordon

AW: Schnittmenge zweier Spalten
23.06.2014 16:01:52
UweD
Hallo
als Formellösung mit 2 Hilfsspalten (L:M)
Formeln ab Zeile2 nach unten copieren bis keine Werte mehr kommen...
mit http://www.excelformeln.de/formeln.html?welcher=194
Tabelle1
 ABCDEFGHIJKLM
1Artikel A  Artikel C    in A und CNur in C          22
210  10  10X          1 
320  30  20            2 
430  250  30X          323
540  100  40            4 
650  50  50X          5 
760  200  60            6 
870  220  70            7 
980  300  80            824
1090      90            9 
11100      100X          10 
12110      110            11 
13120      120            12 
14130      130            13 
15140      140            14 
16150      150            15 
17160      160            16 
18170      170            17 
19180      180            18 
20190      190            19 
21200      200X          20 
22210      210            21 
23220      220X          22 
24        250  X           
25        300  X           
26                         
27                         

verwendete Formeln
Zelle Formel Bereich
M1 =MAX(L:L) 
E2 =WENN(ZEILE(A1)>MAX(M:M);"";WENN(ZEILE(A1)>MAX(L:L);INDEX(C:C;VERGLEICH(ZEILE(A1);M:M;0));INDEX(A:A;VERGLEICH(ZEILE(A1);L:L;0)))) 
F2 =WENN(ZÄHLENWENN(A:A;E2)+10*ZÄHLENWENN(C:C;E2)=11;"X";"") 
G2 =WENN(ZÄHLENWENN(A:A;E2)+10*ZÄHLENWENN(C:C;E2)=10;"X";"") 
L2 =WENN(ZÄHLENWENN(A$2:A2;A2)=1;MAX(L$1:L1)+1;"") 
M2 =WENN(UND(ZÄHLENWENN(C$2:C2;C2)=1;ZÄHLENWENN(A:A;C2)=0);MAX(M$1:M1)+1;"") 

Tabellendarstellung in Foren Version 5.49


Gruß UweD

Anzeige
AW: Schnittmenge zweier Spalten
24.06.2014 09:51:48
Gordon
Hallo Uwe,
danke dir, aber ich hatte eine reine VBA-Löösung für das Probelm gesucht. Diese wurde mir ja nun von Rudi geliefert. Aber danke dir dennoch für deine Mühe! :-)
Gruß
Gordon

AW: Schnittmenge zweier Spalten
23.06.2014 16:05:00
Rudi
Hallo,
ca. 0,1 Sek.:
Sub SpaltenVergleich()
Dim i As Integer
Dim arrA, arrC
Dim objA As Object, objC As Object, objAC As Object, objItem
Set objA = CreateObject("Scripting.Dictionary")
Set objC = CreateObject("Scripting.Dictionary")
Set objAC = CreateObject("Scripting.Dictionary")
objAC("Beide") = 0
objC("nur C") = 0
arrA = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
arrC = Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))
For i = 1 To UBound(arrA)
objA(arrA(i, 1)) = arrA(i, 1)
Next
For i = 1 To UBound(arrC)
objC(arrC(i, 1)) = arrC(i, 1)
Next
For Each objItem In objA
If objC.exists(objA(objItem)) Then
objAC(objItem) = 0
End If
Next
For Each objItem In objC
If objA.exists(objC(objItem)) Then
objC.Remove (objA(objItem))
End If
Next
Range("E:F").Clear
If objAC.Count Then
Cells(1, 5).Resize(objAC.Count) = WorksheetFunction.Transpose(objAC.keys)
End If
If objC.Count Then
Cells(1, 6).Resize(objC.Count) = WorksheetFunction.Transpose(objC.keys)
End If
End Sub

Gruß
Rudi

Anzeige
Änderung
23.06.2014 16:31:42
Rudi
besser so:
  For Each objItem In objA
If objC.exists(objItem) Then
objAC(objItem) = 0
End If
Next
For Each objItem In objC
If objA.exists(objItem) Then
objC.Remove (objItem)
End If
Next

Gruß
Rudi

AW: Änderung
24.06.2014 09:34:16
Gordon
Hallo Rudi,
das ist genau das was ich gesucht habe. Funktioniert super! :-)
Dennoch hätte ich ein paar Fragen, damit ich den Code genau verstehe:
1.
Was ist ein "Scripting.Dictionary"?
2.
Warum müssen die Daten erst in ein Array um sie dann in das "Scripting.Dictionary"-Object zu schieben? Hätte man due Range nicht direkt in das "Scripting.Dictionary"-Object eintragen können bzw. warum geht das nicht?
3.
Mit den Begriffen *.Transpose() und *.Keys kann ich leider nichts anfangen, Kannst du mir da eine kurze Erläuterung geben?
Danke dir schon mal für deine Hilfe. Wenn die Fragen doch zu viel sein sollten, brauchst du sie nicht beantworten. Ich werde ansonsten mal versuchen, über Google etwas herauszufinden.
Gruß
Gordon

Anzeige
AW: Änderung
24.06.2014 10:40:42
Rudi
Hallo,
1. Ein Objekt, das Datenschlüssel-Element-Paare speichert.
2. Könnte man, ist aber langsamer.
3. Transpose dreht das Array, .Keys sind die Schlüssel des Dictionary.
Tipp: Schau mal in die Hilfe.
Gruß
Rudi

46 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige