Excel: Doppelte Einträge in einer Zeile finden

Hallo,

habe schon viel gelesen über Spezialfilter usw. aber noch nicht das richtige für mein Problem gefunden.

Ich möchte doppelte Einträge (Buchstabenkombinationen) in einer Zeile finden und markieren oder deren Anzahl auflisten.

Bsp:

A1 B1 C1 D1 E1

Wir Ba Ber Hei Hol

Wenn nun in dieser Zeile "Ber" mehrfach vorkommt, soll dies entweder farblich hervorgehoben werden oder in einer Hilfsspalte "F1" durch "1" oder sonstiges gekennzeichnet werden.

 

Du kannst folgende Formel verwenden:

Code:

=ZÄHLENWENN($A$1:$A$5;A1)>1

Wobei $A$1:$A$5 der Bereich mit allen Zellen ist und A1 die erste Zelle.

Entweder machst Du es mit einer bedingten Formatierung mit Formel ist und der gewünschten Färbung. Dann werden alle Werte angezeigt, die mehrfach vorkommen.

Oder Du machst es mit der Hilfsspalte. Dann wird Dir so ein WAHR ausgegeben, wenn der Wert mehrfach vorkommt bzw. ein FALSCH bei einmaligen Werten. Da könnstest Du mit einer Wenn-Anweisung natürlich auch eine andere Rückgabe (1) ausgeben.

Gruss
Bruno

 

Danke für die Antwort aber die trifft es nicht.

Ich möchte ja A1 bis E1 quer rüber in Bezug setzen und nicht nur senkrecht schauen, ob A1 doppelt vorkommt.

Es könnte ja auch B1 oder C1 doppelt vorkommen.

Beispiel:

zählenwenn(A1:C1;"Wir") gibt mir aus, dass "Wir" einmal vorkommt.

Aber so habe ich ja nur einen Wert der Zeile geprüft und die anderen vernachlässigt.

Die Formel müsste erkennen, wenn irgendeiner der Einträge doppelt ist und dann meinetwegen "2" ausgeben.

 

Du willst doch nicht nur in insgesamt 5 Zellen schauen...

- es sind wohl mehr Zellen in einer Zeile, und mehr als nur eine Zeile zu prüfen ?
- sollen die Vergleiche zeilenweise stattfinden (Mehrfachvorkommen nur in dieser Zeile), oder...
... Mehrfachvorkommen eines Wortes in allen anderen Zeilen auch prüfen?

Mit einem Makro könnte man die Mehrfachvorkommen farbig markieren.

Gruss Urs

 

Hallo,
ich habe mal ein Makro geschrieben die ermöglicht die doppelte Zeilen zu zeigen bzw. zu löschen. Ich prüfe die Werte in Spalte A, 15 Stellen lang. Die rutine kann aber angepasst werden.
SG. aus Salzburg, Florin

PS. mein erste Beitrag

Code:

Sub showLMX()
On Error GoTo showLMX_Error
Dim RowNumber As Long, Line As Long, i As Long, arrWert() As Variant
Dim Wert1 As String

RowNumber = Cells(Rows.Count, "A").End(xlUp).Row 'Anzahl der Rows
ReDim arrWert(0) 'Array initialisieren
    
  'Step 1 - Alle Zeilen verarbeiten
  For Line = 1 To RowNumber
    Wert1 = Mid(Range("A" & Line).Value, 1, 15) '15 Stellen prüfen
    'Wert1 = Range("A" & Line) 'Komplette Zeileninhalt prüfen
    
      'Step 2 - LMX Vergleichen und löschen
      For i = 0 To UBound(arrWert) 'Array durchgehen
        'Array ist leer, also Wert merken
        If arrWert(i) = "" Then
          arrWert(i) = Wert1
          ReDim Preserve arrWert(UBound(arrWert) + 1)
          Exit For
        End If
          
        'Gelesene LMX ist noch mal vorgekommen und wurde in der Array gespeichert
        'die Zeile wird angezeigt
        If Wert1 = arrWert(i) Then
          
          Debug.Print Rows(Line & ":" & Line).Select
          Select Case MsgBox("Soll die Zeile " & Line & " gelöscht werden? ", _
                    vbYesNoCancel Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Zeile löschen?")
          
            Case vbYes
               Selection.Delete Shift:=xlUp
               Line = Line - 1
               RowNumber = RowNumber - 1
               Exit For
            Case vbNo
             
            Case vbCancel
               Exit Sub
             
          End Select
        End If
      Next i
      
    'RowNumber = RowNumber - 1
  Next Line


showLMX_Exit:
 Exit Sub

showLMX_Error:
 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure showLMX of Modul Modul1"
 Resume showLMX_Exit

End Sub

 

Naja... da war ja auch einmal die nicht unvernünftige Idee mit dem Markieren der Zellen...

Diesen Code so in den VB-Editor kopieren >

Code:

Option Explicit

Sub MehrfachEintraege()

Dim rngRow As Range, colStart, colEnd, rowNum, actCell, i, numCol, valCell

'xxxxxxx hier Deine Spaltennummern eintragen > A=1, B=2, C=3....
colStart = [COLOR="Red"]1[/COLOR]        'linkes Ende des zu prüfenden Bereichs
colEnd = [COLOR="red"]10[/COLOR]         'rechtes Ende
'xxxxxxx

            'aktive Zeilennummer
rowNum = ActiveCell.Row
            'zu prüfenden Bereich vermessen
Set rngRow = ActiveSheet.Range(Cells(rowNum, colStart), Cells(rowNum, colEnd))
            'alte Farben zurücksetzen
rngRow.Cells.Interior.ColorIndex = xlColorIndexNone
Cells(rowNum, colStart).Select

            'prüft nacheinander alle Zellen
For i = 1 To rngRow.Columns.Count
    numCol = ActiveCell.Offset(0, i - 1).Column
    valCell = ActiveCell.Offset(0, i - 1).Value
            'von zu prüfender Zelle aus weiter prüfen
    For Each actCell In rngRow.Cells
            'wenn Zelle weiter rechts ist, und gleicher Wert hat,
            'und keine Farbe hat, und nicht leer ist
        If actCell.Column > numCol And _
                actCell.Value = valCell And _
                actCell.Interior.ColorIndex = xlColorIndexNone And _
                actCell.Value <> "" Then
            actCell.Interior.ColorIndex = i + 2
        End If
    Next
Next i

End Sub

...aber die beiden roten Zahlen Deiner Tabelle anpassen.


Funktion >

- Markierung irgendwo in die zu prüfende Zeile setzen und Makro starten

- in der gewählten Zeile wird auf Mehrfacheinträge geprüft

- bei jedem zweiten, dritten usw Vorkommen wird diese Zelle in einer bestimmten Farbe hinterlegt

- beim nächsten Wort genau so, aber mit einer anderen Farbe hinterlegt (max = 54 Farben)


Gruss Urs

 

QUrs2:

Wie sieht denn das Makro ergänzt aus, wenn ich die Zeile B5 bis G5 prüfen lassen möchte?

Sorry bin anscheinend ein bissel dumm, wenn ich es ergänze macht er garnix ;-)


'xxxxxxx hier Deine Spaltennummern eintragen > A=1, B=2, C=3....
colStart = 1 'linkes Ende des zu prüfenden Bereichs
colEnd = 10 'rechtes Ende
'xxxxxxx

 

Hallo Gandhi,

Das Makro geht davon aus, dass ein mehrzeiliger Datenblock besteht.
Die Daten stehen untereinander, in jeder Zeile in den gleichen Spalten.

Bei einem Bereich zB B5:G20 , wären die Zahlen so zu ändern >
colStart = 2 .......... 'linkes Ende des zu prüfenden Bereichs... B = 2.Spalte
colEnd = 7 ........... 'rechtes Ende...... G = 7.Spalte

...dann wird in einer der Zeilen 5 bis 20, je nachdem welche Zeile den Fokus hat, geprüft...
...ob innerhalb dieser Zeile Mehrfacheinträge sind.

Wenn die zeilenweise Prüfung, automatisch in allen Zeilen durchgeführt werden soll, müsste das Makro mit den obersten und untersten Zeilennummern ergänzt werden.

Wenn innerhalb des ganzen Bereiches, in allen 125 Zellen, geprüft werden soll, ob das "wir" in Zelle B5 nochmals vorkommt, kann das dieses Makro nicht, es müsste umgeschrieben werden.

Gruss Urs

 

....habs probiert: Es klappt wunderbar.

Somit kann ich, wie gewollt, einzelne Zeilen auf Doppeleinträge prüfen.

Hervorragend, vielen Dank!

 

Freut mich auch, wenn es hilfreich war.
Hier noch eine kleine, freiwillige Verbesserung >

Wenn die Zellmarkierung im Blatt versehentlich in einer Zeile ausserhalb der Zeilen mit den Daten liegt, wird dort unsinnig markiert...

Du kannst im Code diese roten Zeilen/Zeichen einfügen...
... dann wird zuerst geprüft, ob die Markierung auch innerhalb des Bereichs liegt.

Code:

Option Explicit

Sub MehrfachEintraegeInEinerZeile()

Dim rngRow As Range, colStart, colEnd, [COLOR="Red"]rowStart, rowEnd,[/COLOR] rowNum, actCell, i, numCol, valCell

'xxxxxxx hier Deine Spaltennummern eintragen > A=1, B=2, C=3....
[COLOR="red"]'...und die Zeilennummern des Bereichs[/COLOR]
colStart = 1        'linkes Ende des zu prüfenden Bereichs
colEnd = 10         'rechtes Ende
[COLOR="red"]rowStart = 4        'oberste Zeile des Nutzbereiches
rowEnd = 7          'unterste Zeile[/COLOR]
'xxxxxxx

[COLOR="red"]ThisWorkbook.Sheets("MitZeile").Select[/COLOR]
            'aktive Zeilennummer
rowNum = ActiveCell.Row
[COLOR="red"]            'wenn Markierung ausserhalb TabellenZeilen
If rowNum < rowStart Or rowNum > rowEnd Then
    MsgBox "Zell-Marke liegt ausserhalb des gültigen Zeilenbereichs !    " & _
            vbCr & vbCr & "Marke innerhalb Tabelle setzen und wiederholen...", _
            vbOKOnly, "Achtung..."
    Exit Sub
End If[/COLOR]
            'zu prüfenden Bereich vermessen
Set rngRow = ActiveSheet.Range(Cells(rowNum, colStart), Cells(rowNum, colEnd))
            'alte Farben zurücksetzen
rngRow.Cells.Interior.ColorIndex = xlColorIndexNone
...
...
...
...

Die Zahlen für colStart, colEnd, rowStart und rowEnd an Deine Tabelle anpassen.
In Zeile "ThisWorkbook.Sheets("MitZeile").Select" das Wort "MitZeile" durch den Namen Deines Arbeitsblattes ersetzen.

Gruss Urs

 

.....hm.....und wenn ich nun immer die gleichen Bereiche in einer Tabelle überprüfen wollte, dann könnte ich dem Makro doch bestimmt auch mitgeben, dass es mir auf einen Schlag die Bereiche

B2 bis B8

D2 bis D8

B10 bis B18

D10 bis D18

durchsucht anstatt nur die eine Zeile in der ich mich befinde, oder?

 

...man kann alles machen... solange man will, weil man den dahinter stehenden Sinn begreift.
Auch verschieden Spaltenbereiche pro Zeile... allerdings wird es dann sehr aufwändig.

Du müsstest schon einmal genau beschreiben, oder, besser, einen ScreenShot zeigen oder Deine Datei hier anhängen, damit man begreift, was Du genau erreichen möchtest.
Immer wieder noch ein Zusatzkriterium... da werden wir zu Ostern noch nicht fertig sein.

Gruss Urs

 

Deine Beispiel-Bereiche sind übrigens keine Zeilen-Bereiche, sondern Spalten-Bereiche..... Was nun ?

Um mehrere Bereiche gleichzeitig zu bearbeiten (aber jeden Einzel-Bereich für sich färben ?) gibt es 2 Möglichkeiten >

1. Alle Bereiche werden einzeln, zB als "C10:K10", in den Makro-Code geschrieben.
Nachteil > Wenn sich in der Tabelle etwas ändert, muss auch das Makro geändert werden.

2. Mit gedrückter Steuerungstaste werden alle zu prüfenden Bereiche im Tabellenblatt markiert, das Makro liest das dann aus.

Wie es gemacht werden soll, musst DU entscheiden.

Ansonsten > Angaben wie das Blatt aussieht, wie umfangreich es ist, ob es immer so bleibt, oder laufend geändert wird, ob es nur ein zu bearbeitendes Blatt ist, oder mehrere.............

Gruss Urs

 

Hallo nochmal,

will das Ganze ja nun auch nicht ins Unendliche treiben.

Ich erstelle für eine Schule einen Stundenplan und die Abkürzungen sind die Abkürzungen der Lehrernamen.

An der Einteilung der Tabelle ändert sich nix, weil es jeden Tag bei 6 Stunden bleibt. Das Makro dient also dazu schnell zu erkennen, ob jemand an einem Tag zur gleichen Stunde doppelt verplant wurde.

Die Lösung mit gedrückter Strg-Taste reicht eigentlich völlig aus, da ich am Ende der Wochenplanung das Makro drüber laufen lasse, um doppelte Einträge an einem Tag zu finden.

Somit bin ich glücklich und zufrieden und bedanke mich nochmal für die ausführliche Unterstützung!

 

...Dein Makro kann das aber noch nicht, dazu müsste ich es ergänzen.

Sehe ich das so richtig >
- die 6 Spalten sind die 6 Stunden
- die 6 Zeilen sind die 6 Wochentage, mit jeweils einer Leerzeile dazwischen

> ich hatte absichtlich das erste Vorkommen nicht gefärbt, erst dessen Wiederholungen.
Da ich jetzt weiss um was es geht... müssten doch alle Vorkommen gleich gefärbt sein ?

Kein Problem, das Makro so zu ändern. Aber, damit ich nicht weiter spekulieren muss >

- mach eine Kopie der Mappe
- entferne alle Daten und Namen, die die Welt nichts angehen - lass aber einige Spieldaten drin... zum Verständnis
- benenne die Datei von "DeineDatei.xls" um in "DeineDatei.xls.txt" (> das Forum weist xls-Dateien zurück)
- und hänge diese "txt"-Datei an Deinen Beitrag an.

Gruss Urs