Excel Spalten vergleichen "alle"

Question

Hallo,

hab eine Frage an die "Excel-Profis".

Ich habe eine Excel Tabelle mit Spalte A und Spalte B.
In beiden steht jeweils Text/Wörter.

Ich würde nun gerne prüfen welche Wörter von Spalte A auch in Spalte B vorkommen.

Aber nicht nur der direkte Vergleich zwischen A1 und B1, A2 und B2 etc. sondern komplett.

Der Wert A1 soll geprüft werden ob er in B1, B2, B3, B4, bis B18668 vorkommt. Genauso dann der Wert A2, A3, A4... A18668.

Gefunden Übereinstimmungen sollen dann einzeln jeweils in Zelle C1, C2, C3 usw. aufgelistet werden


Schönen Gruß
clockwork

Answer 1

Hallo,

=WENN(ZÄHLENWENN(B:B;A1) > 0; ZÄHLENWENN(B:B;A1); "")

in C1 schreiben und runterkopieren.

MfG
Klaus

Answer 2

Hi Klaus,

danke für die schnelle Reaktion. Leider krieg ich in den Zellen wo ich das reinkopiere keinen Wert zurück. Die Zellen bleiben leer.

Falls wichtig ist, in allen Zellen der Spalte A und B sind teilweise mehrere Wörter drin es sind Firmennamen. Die Zellen beinhalten auch manchmal Sonderzeichen wie & oder Klammern etc.

Prüft Deine "Abfrage" jedes einzelne "Wort" das mit Leerzeichen getrennt ist in den Zellen? Oder ist das evtl. dann überhaupt gar nicht machbar?

Answer 3

Hallo,

Formel in C1:

=INDEX(B:B;KKLEINSTE(WENN(ZÄHLENWENN(A$1:A$100;B$1:B$100)>0;ZEILE($1:$100));ZEILE()))

mit STRG/SHIFT/ENTER gleichzeitig abschließen und runterkopieren.

oder normal:

=WENN(ISTFEHLER(VERGLEICH(A1;$B$1:$B$100;0));"";A1)

Zellbezüge sind anzupassen

Gruß

Paul1

Answer 4

Hallo,

Zelle C1: =ZÄHLENWENN(B:B; A1) oder alternativ =ZÄHLENWENN(B$1:B$999; A1)
durchsucht die gesamte Spalte B bzw. den angegebenen Bereich (B$1:B$999) und zählt, wie oft der Wert der Zelle A1 darin vorkommt. Entweder gar nicht (0-mal) oder n-mal (n > 0), aber das Ergebnis ist in jedem Fall eine Zahl.

Dabei spielt es keine Rolle, was in Spalte A für "Suchbegriffe" stehen und was in Spalte B bei den "durchsuchten" Werten alles vorkommt, einschließlich Leerzellen und Fehlerwerten. Groß- oder Kleinschreibung wird nicht berücksichtigt. Zahlen gelten offenbar als gleich, egal ob sie als Zahl oder als Text formatiert sind, selbst dann, wenn vor der Zahl ein Apostroph steht.

Wichtig ist allerdings - gerade bei Texten - dass sie (bis auf Groß- oder Kleinschreibung) vom ersten bis zum letzten Zeichen exakt übereinstimmen, einschließlich vorangestellter und nachfolgender Leerzeichen!

Wenn sich nach diesen Hinweisen kein Erfolg einstellen will, dann füge doch in Deiner Spalte A einfachen mal einen Wert hinzu, kopiere die Zelle einmal irgendwohin in die Spalte B und teste eine der Formeln (ggf. den Suchbereich anpassen):

1a) =WENN(ZÄHLENWENN(B:B; A1) > 0; ZÄHLENWENN(B:B; A1); "[nicht gefunden]")
bzw.
1b) =WENN(ZÄHLENWENN(B$1:B$999; A1) > 0; ZÄHLENWENN(B$1:B$999; A1); "[nicht gefunden]")

oder

2a) =WENN(ZÄHLENWENN(B:B; A1) > 0; ZÄHLENWENN(B:B; A1); "")
bzw.
2b) =WENN(ZÄHLENWENN(B$1:B$999; A1) > 0; ZÄHLENWENN(B$1:B$999; A1); "")

für diesen Wert. Eigentlich müsste sich dann die Anzahl 1 ergeben, aber allerwenigstens müsste bei 1a), 1b) das Ergebnis [nicht gefunden] angezeigt werden.

MfG
Klaus

Answer 5

…gibt es da nicht GLÄTTEN() mit dem man wenigstens das eine und andere Leerzeichen erschlagen kann..?

Zudem an clockwork:
Mit den Formeln hast du ein Reihe von Ja-Nein Antworten. Was meinst du damit anstellen zu können?
Oder mehr: was soll das Endresultat der ganzen Übung sein?

Eric March

Answer 6

Erst mal danke für die Hilfe Klaus/Paul!
Werde das gleich ausprobieren.

@Eric
Die Spalten beinhalten Firmennamen. Kopiert sind Sie aus 2 verschiedenen Excel Tabellen in eine.

In Spalte A ist ein Kundenstamm in Spalte B eine Liste von Firmen die in einem "Verband" sind. Das Ziel: Ich möchte einfach nur prüfen welche Firmen die im Verband sind schon bei uns im Kundenstamm angelegt sind.

Answer 7

@Paul:
Hab das hier gerade probiert.

=INDEX(B:B;KKLEINSTE(WENN(ZÄHLENWENN(A$1:A$100;B$1:B$100)>0;ZEILE($1:$100));ZEILE()))

Hab das in C1 kopiert und er gibt mir als "ergebnis" in C1 dann
den Inhalt von Spalte B1. Der Inhalt von Spalte B1 steht tatsächlich auch in A9296. Hat er also als Übereinstimmung gefunden und mir in C1 angezeigt, das ist Super!

Aber wenn ich die Formel nach unten kopiere kommt bei den drauf folgenden Zellen nur "#ZAHL!".

Bin wahrlich kein Excel Profi... hab ich was falsch gemacht?

Habe Die Formel in C1 kopiert mit Strg+V und danach Strg+Shift+Enter gedrückt wie Du geschrieben hast und dann die Zellen nach unten kopiert (mit der Maus gezogen).

Answer 8

Hallo,
Du hast nichts falsch gemacht!
Wenn "#ZAHL!" geschrieben steht, dann hat er keine genauen Übereinstimmungen mehr gefunden.

Die Formel überprüft nur genaue Übereinstimmungen, wenn z.B. in einer Zelle der Spalte A 2 Wörter stehen und in einer Zelle der Spalte B nur ein Wort davon übereinstimmt, dass kann die Formel nicht.

Die Formel ist keine Erfindung von mir näheres würde ich empfehlen

hier

nachzulesen.

Die Meldung "#ZAHL" könnte man der einfachheithalber mit bedingter Formatierung unsichtbar machen.


Gruß

Paul1

Answer 9

Hallo Paul, hab noch mal was probiert.

Die eine Übereinstimmung die er gefunden hat sieht als Beispiel so aus.

In Spalte A9296 steht: "Auto, D-40202 STADT"
In Spalte B1 steht: "49323 Auto Blabl"

Mit der Formel kommt als Ergebnis in C1: "49323 Auto Blabl"

Also das steht genau so da (ok sind andere Wörter aber Leerzeichen, Komma etc. genau so.).

Das heißt er hat das Wort "Auto" durch die Formel in beiden Spalten erkannt und mir den ganzen Wert von Spalte B in C geschrieben.
Was ja auch Super ist!

Habe nun Testhalber jetzt eine weitere Zeile unten angefügt und ein Wort in die Zeile A18666 geschrieben das auch in Spalte B34 steht.

Wenn ich die Formel dann nach unten kopiere, kommt trotzdem in C2 z. B. nur "#ZAHL!". Also findet er diesen TestWert nicht, woran könnte das liegen? Bzw. sah für mich dann so aus als ob ich irgend was falsch machen würde.

Danke auf jeden Fall für die Mühe!

Answer 10

Habe noch was probiert.
Wenn ich das Wort wo er scheinbar gefunden hat, in B1 umbennene:

Erscheint mit der Formel in C1 dann der "geänderten" Wert.
Obwohl das Wort wie im Beispiel zuvor jetzt in B1 "Auuto" heißen würde und in A9296 nach wie vor "Auto".

Also.

In Spalte A9296 steht: "Auto, D-40202 STADT"
In Spalte B1 steht: "49323 Auuto Blabl"
In Spalte C1 kommt jetzt durch die gleiche Formel das Ergebnis: "49323 Auuto Blabl".

Dann kann dann doch irgendwo etwas scheinbar nicht stimmen.