Werte aus Zellen prüfen, ob in anderer Spalte vorhanden
CBuss
ich habe in der Spalte (A) Werte, die durch Kommata getrennt sind (z.B.
Zelle A2: 123456, 456258, 256389). In einer der Spalte (B) habe ich einzelne
Werte (z.B. B2: 123456).
Nun möchte ich prüfen, ob die einzelnen Werte aus der Zelle A2 in der Spalte
B vorhanden sind.
Geht das überhaupt? Ihr habt bestimmt eine Lösung.
Gruß
Carsten
Bernd P
Falls es Dir reicht, dass die Treffer in Spalte B farblich markiert
werden:
1. Gehe zu Einfuegen / Name / Definieren und definiere den Namen
CREATE_ARRAY mit dem Wert
=AUSWERTEN("{"&WECHSELN($A$2;",";";")&"}")
2. Gehe in Zelle B1 und waehle dann die gesamte Spalte B aus und gib
als bedingte Formatierung als Formel ein:
=VERGLEICH(B1;CREATE_ARRAY;0)
Waehle als Muster zum Beispiel einen gruenen Hintergrund.
Dann werden die Treffer gruen markiert.
Viele Gruesse,
Bernd
Eberhard Funke
> ich habe in der Spalte (A) Werte, die durch Kommata getrennt sind (z.B.
> Zelle A2: 123456, 456258, 256389). In einer der Spalte (B) habe ich einzelne
> Werte (z.B. B2: 123456).
>
> Nun m�chte ich pr�fen, ob die einzelnen Werte aus der Zelle A2 in der Spalte
> B vorhanden sind.
Hallo Carsten,
nach Bernds Mercedes hier mein Volkswagen (f�hrt nur unfallfrei, wenn
*alle* Zahlen in Spalten A und B dieselbe Stellenzahl haben):
Markiere ab B2 abw�rts bis zum letzen Eintrag.
Bedingte Formatierung
=SUMMENPRODUKT(--ISTZAHL(SUCHEN(B2;A$2:A$100;1)))
BTW: Wenn die Bed. Formatierung �ber diesen Bereich nach unten erweitert
wird, dann werden die Leerzellen in Spalte B ebenfalls gef�rbt:
Suchen(12345;Leerzelle;1) --> 1
Suchen(12345;Leerzelle;5) --> 5
Suchen(12345;Leerzelle;6) --> #WERT!
--
Mit freundlichen Gr�ssen Eberhard
XP home XL 2000
Eberhard(punkt)W(punkt)Funke(at)t-online.de
CBuss
"Bernd P" wrote:
Hallo Bernd,
ja, das ist es schon fast. Aber durch den definierten Namen bin ich auf A2
"festgenagelt". Wie prüfe ich jetzt die Zellen A3, A4, usw.?
Hast du evtl. auch eine VBA Lösung?
Gruß
Carsten
Bernd P
meine Loesung funktioniert ja lediglich fuer A2.
Wie hast Du Dir denn die Anzeige von Treffern fuer alle Zellen in
Spalte A vorgestellt?
Viele Gruesse,
Bernd
CBuss
"Bernd P" wrote:
Hi Bernd,
ich noch einmal.
Hier noch mal ein Beispiel:
Spalte A Spalte B
123, 456, 789 123
123, 258, 365 456
147, 258, 369 789
147
258
369
Die Werte aus Zelle A2 und A4 finde ich in der Spalte B wieder.
Bei den Werten aus Zelle A3 (123, 258, 365) finde ich den Wert 365 nicht.
Deshalb möchte ich gerne, dass diese Zelle dann eingefärbt wird.
OK?
Danke und Gruß
Carsten
CBuss
"Bernd P" wrote:
Hallo Bernd,
wenn ein Teil der Zelle in Spalte A nicht in B vorhanden ist, dann soll die
Zelle in Spalte A rot markiert werden.
Gruß
Carsten
Bernd P
Pack die folgende Funktion in ein Makromodul:
Function makearray(s As String) As Variant
makearray = Evaluate("{" & Replace(s, ",", ";") & "}")
End Function
Dann gehe in Zelle A1, markiere dann die gesamte Spalte A und gib als
bedingte Formatierung die Formel ein:
=ISTFEHLER(SUMMENPRODUKT(VERGLEICH(makearray(A1);B:B;0)))*(1-ISTLEER
(A1))
dazu natuerlich als Hintergrund rot auswaehlen.
Viele Gruesse,
Bernd
CBuss
"Bernd P" wrote:
Ach Bernd,
es nimmt kein Ende. Die bedingte Formatierung kann ich im Excel nicht
nutzen, da Array im Excel2007 nicht genutzt werden dürfen. So sagt mir
jedenfals mein Excel.
Gruß
Carsten
Bernd P
Nanu, das wundert mich aber, da es bei mir in Excel 2003 ohne Probleme
laeuft.
Hast Du wirklich die Formel ganz normal nur mit Enter eingegeben?
Viele Gruesse,
Bernd
Eberhard Funke
> es nimmt kein Ende. Die bedingte Formatierung kann ich im Excel nicht
> nutzen, ...
Hallo Carsten,
Deine Zahlen in A2:A5 bzw. B2:B7
A2:A5 (oder noch weiter runter) bedingte Formatierung
Formel ist: =(SUMMENPRODUKT(--ISTZAHL(SUCHEN(B$2:B$7;A2;1)))<1)*(A2>0)
Bernd P
Da muss ein winziger Druckfehler in der Formel sein.
Ich bekam mit dem englischen Excel 2007 eine aehnliche Fehlermeldung,
weil ich ein Leerzeichen zwischen ISTLEER und der nachfolgenden
Klammerauf ( hatte.
Meine deutsche Uebersetzung konnte ich nicht testen:
=ISTFEHLER(SUMMENPRODUKT(VERGLEICH(makearray(A1);B:B;0)))*(1-ISTLEER
(A1))
Aber diese englische Version funktioniert:
=ISERROR(SUMPRODUCT(MATCH(makearray(A1),B:B,0)))*(1-ISBLANK(A1))
Google packt manchmal auch ein Minuszeichen in ueberlange Zeilen.
Schau bitte auch einmal danach.
Viele Gruesse,
Bernd
CBuss
"Bernd P" wrote:
Hallo Benrd, hallo Eberhard,
Danke für Euere Hilfe es funktioniert.
Gruß
Carsten
CBuss
"Bernd P" wrote:
Hallo Bernd,
eigentlich hatte ich ja schon geschrieben, das alles super funktioniert. Nun
sehe ich gerade, dass auch Buchstaben (also nicht nur Ziffern) sowohl in
Spalte A und B enthalten sein können, Dann funzt es leider nicht mehr, und
ich bekomme eine #Wert Fehlermeldung. Und nu?
Gruß
Carsten
Eberhard Funke
> Nun
> sehe ich gerade, dass auch Buchstaben (also nicht nur Ziffern) sowohl in
> Spalte A und B enthalten sein k�nnen, Dann funzt es leider nicht mehr, und
> ich bekomme eine #Wert Fehlermeldung. Und nu?
Hallo Carsten,
soweit ich das aus der Erinnerung rekonsturieren kann + Deine neuen Info:
In Spalte A ab A2 k�nnen in den Zellen Zahlen (verschiedener L�nge)
und/oder Texte und/oder Alphanumerisches (z. B. A1B2C)stehen, getrennt
jeweils von einem *Komma_gefolgt_von_einem_Leerzeichen*, also z. B.
A2: 123, A12, 3456, Hans, Hansi
A3: Hansa
In Spalte B k�nnen diese Begriffe (und andere) vorkommen, aber *nur_einer"
je Zelle, also B2: 12, B3: 12, B4: Hans
Treffer in A2, da 123 (aus B2) vorkommt [die 12 aus B3 gibt keinen Treffer,
ist zwar in A12 und in 123 enthalten aber nicht gleich A12 bzw. 123]. Einen
weiteren Treffer gibt Hans (aus B4).
A3 gibt keinen Treffer: Hans (aus B4 ist zwar in Hansa enthalten, aber
nicht gleich Hansa.
(Hoffentlich verst�ndlich).
Probier's mal mit diesem Code:
Sub Werte_aus_Zellen()
Dim rngB As Range, rngZelle As Range
Dim lngLRA As Long, lngZ As Long, intI As Integer
Dim varX As Variant, intK As Integer
Set rngB = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
lngLRA = Range("A" & Rows.Count).End(xlUp).Row
For Each rngZelle In rngB
For lngZ = 2 To lngLRA
varX = Split(Range("A" & lngZ), ", ")
For intK = 0 To UBound(varX) - 1
If varX(intK) = CStr(rngZelle.Value) Then
Range("A" & lngZ).Interior.ColorIndex = 3
Exit For
End If
Next
Next
Next
End Sub
Das ist jetzt aber keine "bedingte Formatierung", sondern die Farbungen
sind "permanent". Wenn Du also nachtr�glich einen Wert in der Tabelle
�nderst, dann musst Du erst alles auf "Keine Farbe" setzen und dann das
Makro erneut laufen lassen.
Ich habe das nur einen einem kleinen Modell getestet und bin nicht sicher,
ob ich alle Kombionationsm�glichkeiten ber�cksichtigt habe.
Bernd P
Meine Loesung funktioniert auch fuer Zeichenketten (Strings). Du musst
da einen Syntaxfehler in Deiner Datei haben.
Schick mir einmal Deine Email Adresse. Dann sende ich Dir meine
Beispieldatei. Gib aber bitte Deine Excel Version an.
Viele Gruesse,
Bernd
CBuss
"Bernd P" wrote:
Hi Bernd,
hier ist meine Mailadresse: carste...@computacenter.com.
Und ich arbeite mit Excel2007.
Gruß
Carsten
Bernd P
Nur der Vollständigkeit halber: So wird es richtig:
Pack die folgende Funktion in ein Makromodul:
Function makearray(s As String) As Variant
makearray = Split(s, ",")
End Function
Dann gehe in Zelle A1, markiere dann die gesamte Spalte A und gib als
bedingte Formatierung die Formel ein:
=ISTFEHLER(SUMMENPRODUKT(VERGLEICH(makearray(A1);B:B&"";0)))*(1-