Daten filtern nach ID-Nummern

Question

Hallo Community,

in einem umfangreichen Tabellenblatt sind Personendaten anhand einer achtstelligen ID-Nummer identifizierbar. Diese ID-Nummern befinden sich alle untereinander in der selben Spalte. Die ID-Nummern kommen entweder ein- oder zweimal vor. Von der achtstelligen Nummer sollen nur die ersten sieben (einschließlich ggf einer führenden Null) für eine anschließende Prüfung herangezogen werden: Eine Funktion soll prüfen, ob diese ersten sieben Ziffern identisch sind und falls ja, die jeweils untere von den beiden Zeilen zu löschen oder zu markieren.
Ist das umsetzbar?

VG,
Sebastian

Answer 1

Hallo Sebastian,

verwende in Zeile1 einer Hilfsspalte folgende Formel und kopiere sie bis ans Tabellenende.

=SUMMENPRODUKT((LINKS(A1:A1000;7)=LINKS(A1;7))*1)

Bezüge sind anzupassen.
Nun kannst Du nach den Werten 1 der Hilfsspalte filtern und das Filterergebnis löschen.

Gruß
Rainer

Answer 2

Korrektur:

die Formel muss so aussehen

=SUMMENPRODUKT((LINKS(A$1:A$1000;7)=LINKS(A1;7))*1)

Gruß
Rainer

Answer 3

Das funktioniert leider nicht. Die erste Ticketnummer steht in H5 also habe ich statt A H geschrieben und die Formel runtergezogen..
Ich verstehe schon das Funktionsargument nicht. Warum LINKS=LINKS und das wiederum als Argument einer Funktion zur Matrixmultiplikation?
Sorry wenn ich solche Fragen stelle, aber sitze schon ewig an diesem Problem und würde auch gern nachvollziehen, welche Funktionen ich benutze.

Answer 4

Hallo Sebastian,

versuch es mal so:

=SUMMENPRODUKT((LINKS(H$5:H13;7)=LINKS(H13;7))*1)

Mit bedingter Formatierung kannst du dir dann alle Zelle markieren, die größer 1 sind.

Von der achtstelligen Nummer sollen nur die ersten sieben (einschließlich ggf einer führenden Null) für eine anschließende Prüfung herangezogen werden

Das macht die Funktion LINKS (siehe hierzu auch die Excel-Online-Hilfe).

SUMMENPRODUKT wird hier für den Vergleich der Matrix genutzt. Näheres kannst du hier nachlesen: SUMMENPRODUKT.

Gruß

M.O.

Answer 5

LINKS(H5;7) gibt mir bei Nummern mit führender Null leider nur die letzten sieben ohne die führende Null wieder. Die ID-Nummern sind in einem benutzerdefinierten Format "00000000" und werden auch so angezeigt. Kann es sein, dass Excel sie trotzdem nur als siebenstellig interpretiert?

Answer 6

Hallo,

für die Zelle H5 muss die Formel natürlich richtig wie folgt aussehen:

=SUMMENPRODUKT((LINKS(H$5:H5;7)=LINKS(H5;7))*1)

Und dann nach unten ziehen.

Gruß

M.O.

Answer 7

Hallo Sebastian,

nach Deiner letzten Info lautet die Formel für die Zeile 5 der Hilfsspalte so:

=SUMMENPRODUKT((LINKS(H$5:H$1000;7)=LINKS(H5;7))*1)

Sie funktioniert aber nur, wenn die Zahlen in Spalte H reiner "Text" sind und nicht etwa nur mit "00000000" formatiert sind.

Denn logischer Weise gibt es ja keine Ganzzahlen mit führender Null.

Die Formel vergleicht jede Zelle der Spalte H wie oft die ersten sieben Stellen der Zellwerte in der Gesamtmatrix vorkommen.

Da Du schriebst,

Die ID-Nummern kommen entweder ein- oder zweimal vor.

ist das Formelergebnis immer 1 oder 2.

Es ist nun Dir überlassen ob Du per Filter die Zeilen mit 1 oder mit 2 ausschließen willst.

Falls es noch Probleme gibt stelle bitte eine abgespeckte Beispielmappe, bei der auch sensible Daten verfälscht sein können, zur Verfügung. Die Werte der Spalte H sollten jedoch format- und wertemäßig dem Original entsprechen.

Gruß
Rainer

Answer 8

Hallo,

eine Möglichkeit wäre eine zusätzliche Hilfsspalte einzufügen, in der du ID-Nummern von Zahlen in Text umwandelst und zwar mit:
=TEXT(H5;"00000000")

Auf diese Hilfsspalte kannst du dann die Summenprodukt-Formel anwenden.

Gruß

M.O.

Answer 9

OK, da lag schon mal ein Problem: Ich hatte die Nummern als achtstellige Zahl definiert. Aber wenn ich sie als Text formatiere, muss ich knapp 900 nummern manuell eine Null voranstellen, da sie ansonsten nur siebenstellig angezeigt werden und damit das Filterkriterium nicht erfüllen

Answer 10

Danke, M.O., du scheinst mir immer einen Schritt voraus zu sein. :D Ich denke, das wäre dann die einzige Möglichkeit, oder?