Spalten vergleichen mit "WENN(ZÄHLENWENN(...)"

Hallo liebe Excelianer,
ich stoße mal wieder an meine Grenzen und hoffe sehr, dass ihr mir helfen könnt.
Ich habe zwei Spalten - die Daten der Spalten werden aus anderen Tabellen ausgelesen und sollten weitgehend gleich sein. Es kann aber vorkommen, dass Daten abweichen, oder nicht vorhanden sind.
Hier die Formel:
=WENN(ZÄHLENWENN(_aus;I9)=0;I9;"identisch")
Der Bereich "_aus" ist von mir definiert und bezeichnet die erste zu vergleichende Spalte. Diese Formel bewirkt aber, dass eine "Auswertung" über die gesamte Spalte passiert. Mir wäre es lieber, wenn die Daten direkt untereinander aufgelistet wären und nciht zwischendurch immer 10 Zeilen mit "identisch" stehen.
Meine Ergebnisse sehen dann etwa so aus:
ABC Anton ## ABB Anton ## ABB ## identisch
DEF Detlef ## DEF Detlev ## identisch ## Detlev
GHI Gustav ## GHI Gustav ## identisch ## identisch
JKL Johannes ## JKL Johannes ## identisch ## identisch
MNO Martin ## REF Roland ## REF ## Roland
PQR Paul ## STU Stephan ## identisch ## Stephan
STU Sven ## vWX Volker ## identisch ## identisch
VWX Volker ## identisch ## identisch
YZA Yvonne ## identisch ## identisch
Die verglichenen Daten benötige ich noch für ein anderes Sheet. Da alle Daten variabel sind, möchte ich nirgendwo etwas "fest" eintragen.
Also möchte ich gerne auf den Zelleninhalt verweisen.
Gibt es nun die Möglichkeit, dass alles unmittelbar nacheinander aufgelistet werden kann, oder dass ich in meine andere Zelle irgendwie schreiben kann, dass nur Zellen deren Inhalt "identisch" ist, ausgelesen werden?
Ich hoffe, ich konnte es annähernd erklären und dass ihr mir vielleicht helfen könnt?!
Viele Grüße und schonmal ein schönes Wochenende!
Tanja

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Hi Tanja,
eine ordentliche Excel-Lösung habe ich auch nicht hinbekommen.
Die Formeln in Sp. G:H sind die von David, die sin den Spalten J:K habe ich hier gefunden:
http://www.excelformeln.de/formeln.html?welcher=84
Das das alles nicht das ist, was du brauchst, habe ich die Werte in M:N per Makro ermitteln lassen:

ABC

Anton

ABB

Anton

ABB

Detlev

ABB

Detlev

ABB

DEF

Detlef

DEF

Detlev

REF

Roland

REF

Roland

Detlev

GHI

Gustav

GHI

Gustav

#ZAHL!

Stephan

REF

Roland

JKL

Johannes

JKL

Johannes

#ZAHL!

Stephan

MNO

Martin

REF

Roland

PQR

Paul

STU

Stephan

STU

Sven

VWX

Volker

VWX

Volker

YZA

Yvonne

Formeln der Tabelle

Zelle	Formel
G1	{=INDEX(D$1:D$7;KKLEINSTE(WENN(ISTNV(VERGLEICH(D$1:D$7;A$1:A$9;0)); ZEILE(D$1:D$7); ""); ZEILE()))}
H1	{=INDEX(E$1:E$7;KKLEINSTE(WENN(ISTNV(VERGLEICH(E$1:E$7;B$1:B$9;0)); ZEILE(E$1:E$7); ""); ZEILE()))}
J1	{=INDEX(D:D;KKLEINSTE(WENN(ZÄHLENWENN(A$1:A$100;D$1:D$100)=0;ZEILE($1:$100)); ZEILE()))}
K1	{=INDEX(E:E;KKLEINSTE(WENN(ZÄHLENWENN(B$1:B$100;E$1:E$100)=0;ZEILE($1:$100)); ZEILE()))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Und hier ist das Makro: Option Explicit Sub vgl() Dim lngA As Long, lngD As Long Dim arrXA, arrXB, arrYA, arrYB, arrEA(), arrEB() Dim zzD As Long, varZ, bolN As Boolean, zzE As Long With Application lngA = Cells(Rows.Count, 1).End(xlUp).Row arrXA = .Transpose(Cells(1, 1).Resize(lngA)) arrXB = .Transpose(Cells(1, 2).Resize(lngA)) lngD = Cells(Rows.Count, 4).End(xlUp).Row arrYA = .Transpose(Cells(1, 4).Resize(lngD)) arrYB = .Transpose(Cells(1, 5).Resize(lngD)) ReDim arrEA(1 To lngD), arrEB(1 To lngD) For zzD = 1 To lngD bolN = True varZ = .Match(arrYA(zzD), arrXA, 0) If Not IsNumeric(varZ) Then zzE = zzE + 1 arrEA(zzE) = arrYA(zzD) arrYA(zzD) = "" bolN = False End If varZ = .Match(arrYB(zzD), arrXB, 0) If Not IsNumeric(varZ) Then zzE = zzE - bolN arrEB(zzE) = arrYB(zzD) arrYB(zzD) = "" bolN = True End If Next zzD Cells(1, 16).Resize(zzE) = .Transpose(arrEA) Cells(1, 17).Resize(zzE) = .Transpose(arrEB) End With End Sub Nebenbei: In deiner Beispieltabelle sind die Werte in den Spalten A und D sortiert,
kein Wert tritt doppelt auf.
Das Mako braucht diese Eigenschaft nicht.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Option Explicit Sub vgl2() Dim lngA As Long, lngD As Long Dim arrXA, arrXB, arrYA, arrYB, arrEA(), arrEB() Dim zzD As Long, varZ, bolN As Boolean, zzE As Long Const lngUeb As Long = 8 ' 8 Zeilen Überschrift, Daten ab Zeile 9 With Application lngA = Cells(Rows.Count, 2).End(xlUp).Row - lngUeb ' Spalte B arrXA = .Transpose(Cells(lngUeb + 1, 2).Resize(lngA)) ' Spalte B arrXB = .Transpose(Cells(lngUeb + 1, 3).Resize(lngA)) ' Spalte C lngD = Cells(Rows.Count, 9).End(xlUp).Row - lngUeb ' Spalte I arrYA = .Transpose(Cells(lngUeb + 1, 9).Resize(lngD)) ' Spalte I arrYB = .Transpose(Cells(lngUeb + 1, 10).Resize(lngD)) ' Spalte J ReDim arrEA(1 To lngD), arrEB(1 To lngD) For zzD = 1 To lngD bolN = True varZ = .Match(arrYA(zzD), arrXA, 0) ' suche in Sp. I If Not IsNumeric(varZ) Then ' wenn kein Treffer zzE = zzE + 1 ' neue Ergebniszeile arrEA(zzE) = arrYA(zzD) arrYA(zzD) = "" bolN = False End If varZ = .Match(arrYB(zzD), arrXB, 0) ' suche in Sp. J If Not IsNumeric(varZ) Then ' wenn kein Treffer zzE = zzE - bolN ' evtl. neue Ergebniszeile arrEB(zzE) = arrYB(zzD) arrYB(zzD) = "" bolN = True End If Next zzD Cells(lngUeb + 1, 11).Resize(zzE) = .Transpose(arrEA) ' Spalte K Cells(lngUeb + 1, 12).Resize(zzE) = .Transpose(arrEB) ' Spalte L End With End Sub

Sub vgl3() Dim lngA As Long, arrXA, arrXB, lngD As Long, arrY, arrE Dim zzD As Long, bolN As Boolean, varZ, zzE As Long Const lngUeb As Long = 8 ' Anz. Überschriftzeilen, Daten darunter With Application lngA = Cells(Rows.Count, 2).End(xlUp).Row - lngUeb ' Spalte B arrXA = .Transpose(Cells(lngUeb + 1, 2).Resize(lngA)) ' Spalte B arrXB = .Transpose(Cells(lngUeb + 1, 3).Resize(lngA)) ' Spalte C lngD = Cells(Rows.Count, 9).End(xlUp).Row - lngUeb ' Spalte I arrY = Cells(lngUeb + 1, 9).Resize(lngD, 2) ' Spalten I:J ReDim arrE(1 To lngD, 1 To 2) ' für das Ergebnis For zzD = 1 To lngD bolN = True varZ = .Match(arrY(zzD, 1), arrXA, 0) ' suche in Sp. I If IsNumeric(varZ) Then ' wenn Treffer arrXA(varZ) = "" ' Treffer löschen Else ' wenn kein Treffer zzE = zzE + 1 ' neue Ergebniszeile arrE(zzE, 1) = arrY(zzD, 1) bolN = False End If varZ = .Match(arrY(zzD, 2), arrXB, 0) ' suche in Sp. J If IsNumeric(varZ) Then ' wenn Treffer arrXB(varZ) = "" ' Treffer löschen Else ' wenn kein Treffer zzE = zzE - bolN ' evtl. neue Ergebniszeile arrE(zzE, 2) = arrY(zzD, 2) bolN = True End If Next zzD End With Cells(lngUeb + 1, 11).Resize(zzE, 2) = arrE ' Ergebnis is Spalten K:L End Sub

Beliebteste Forumthreads (12 Monate)

	A	B	C	D
1	Spalte 1	Spalte 2	Zusammenführung
2	a	a	a	Formel runterkopieren
3	b	c	b
4	c	e	c
5	d		d
6	e	i	e
7	f	k	f
8	g	m	g
9	h	o	h
10	i		i
11			k
12			m
13			o

verwendete Formeln
Zelle	Formel
C2	{=WENN(ZEILE(A1)>SUMME(WENN(A$2:B$99<>"";1/ZÄHLENWENN(A$2:B$99;A$2:B$99)));"";WENN(ZEILE(A1)>SUMME(WENN(A$2:A$99<>"";1/ZÄHLENWENN(A$2:A$99;A$2:A$99)));INDEX(B$2:B$99;MIN(WENN((B$2:B$99<>"")(ZÄHLENWENN(C$1:C1;B$2:B$99)=0);ZEILE($1:$98))));INDEX(A$2:A$99;MIN(WENN((A$2:A$99<>"")(ZÄHLENWENN(C$1:C1;A$2:A$99)=0);ZEILE($1:$98))))))}

	A	B	C
1	Spalte 1	Spalte 2	Abweichungen
2	a	a
3	b
4	c	d
5	d	o
6	e	e
7	f	f
8	g	a
9	h	h
10	i	x

verwendete Formeln
Zelle	Formel
M1	{=INDEX($D$1 :$D$7;KKLEINSTE(WENN(ISTNV(VERGLEICH($D$1 :$D$7;$A$1 :$A$9;0));ZEILE($D$1 :$D$7);"");ZEILE()))}
N1	{=INDEX($E$1 :$E$7;KKLEINSTE(WENN(ISTNV(VERGLEICH($E$1 :$E$7;$B$1 :$B$9;0));ZEILE($E$1 :$E$7);"");ZEILE()))}
M2	{=INDEX($D$1 :$D$7;KKLEINSTE(WENN(ISTNV(VERGLEICH($D$1 :$D$7;$A$1 :$A$9;0));ZEILE($D$1 :$D$7);"");ZEILE()))}
N2	{=INDEX($E$1 :$E$7;KKLEINSTE(WENN(ISTNV(VERGLEICH($E$1 :$E$7;$B$1 :$B$9;0));ZEILE($E$1 :$E$7);"");ZEILE()))}
M3	{=INDEX($D$1 :$D$7;KKLEINSTE(WENN(ISTNV(VERGLEICH($D$1 :$D$7;$A$1 :$A$9;0));ZEILE($D$1 :$D$7);"");ZEILE()))}
N3	{=INDEX($E$1 :$E$7;KKLEINSTE(WENN(ISTNV(VERGLEICH($E$1 :$E$7;$B$1 :$B$9;0));ZEILE($E$1 :$E$7);"");ZEILE()))}
N4	{=INDEX($E$1 :$E$7;KKLEINSTE(WENN(ISTNV(VERGLEICH($E$1 :$E$7;$B$1 :$B$9;0));ZEILE($E$1 :$E$7);"");ZEILE()))}

definierte Namen
Name	Bezieht sich auf	Tabelle
kk	=INDEX(Tabelle1!D:D;KKLEINSTE(WENN(ISTNV(VERGLEICH(Tabelle1!$D$1:$D$7&Tabelle1!$E$1:$E$7;Tabelle1!$A$1:$A$9&Tabelle1!$B$1:$B$9;0));ZEILE(Tabelle1!$1:$7));ZEILE(Tabelle1!A1)))

Spalten vergleichen mit "WENN(ZÄHLENWENN(...)"

23
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

1 Forumthread zu ähnlichem Thema

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Spalten vergleichen mit "WENN(ZÄHLENWENN(...)"

23 Beiträge zum Forumthread Beiträge zu diesem Forumthread

1 Forumthread zu ähnlichem Thema

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

23
Beiträge zum Forumthread

Beiträge zu diesem Forumthread