Duplikate in einer Spalte in separater Spalte markieren ausser 1. Auftreten

Hallo liebes Forum.

Ich suche eine Möglichkeit in einer Tabelle Duplikate zu markieren mit Ausnahme des ersten Auftretens (d.h. bei 10 identischen Einträgen werden 9 markiert). Die Duplikate befinden sich nur in einer Spalte.
Die Markierung der Duplikate soll in einer separaten Spalte erfolgen.
Ich habe verschiedene Ansätze versucht allerdings bisher ohne Erfolg.

Bitte um Hilfe

 

Mit PostgreSQL, sollte aber in M$SQL auch so gehen:

Code:

test=*# select * from silberstreif ;
 i   
---   
 2
 3
 5
 4
 2
 1
 3
 2
 1
 6
 4
 8
 5
 3
 9
 6
 5
(17 rows)

Time: 0,177 ms
test=*# select *, case when row_number() over ( partition by i) > 1 then '*' else '' end as marker from silberstreif ;
 i | marker
---+--------
 1 |
 1 | *
 2 |
 2 | *
 2 | *
 3 |
 3 | *
 3 | *
 4 |
 4 | *
 5 |
 5 | *
 5 | *
 6 |
 6 | *
 8 |
 9 |
(17 rows)

 

Zumindest MSSQL braucht neben PARTION BY auch noch ein ORDER BY:

Code:

select *, case when row_number() over (partition by i order by i) > 1 then '*' else '' end as marker from silberstreif;

Das funktioniert dann aber auch.

 

Cool ;-)

 

Magisch!

Danke

 

Schade zu früh gefreut.

Unter MSSQL ist das Ergebnis wie erwartet, ich dachte ich könnte das selbe auch unter Excel machen leider bekomme ich da einen Syntax Error:

Code:

Sub updatedaten1()  

    Dim cn As Object
    Dim rs As Object
    Dim strConnection As String
    Dim strSQL As String

Debug.Print Now()

    Set cn = CreateObject("ADODB.CONNECTION")

' innerhalb von Excel mittels SQL-Abfrage suchen

    'Den Treiber bekanntgeben
    strConnection = "DRIVER={Microsoft Excel Driver (*.xls)}; ReadOnly=0; DBQ=" & ThisWorkbook.FullName

    Sheets("testtab").Range("A4:CZ60000").ClearContents                              ' Zielbereich löschen

    With cn
'Datenverbindung öffnen
        .Open strConnection

'Abfragestring zusammenbasteln und Abfrage starten
            strSQL = "select *, case when row_number() over (partition by ObjectName order by ObjectName) > 1 " & _
            "then '*' else '' end as marker FROM [Basis$A3:CZ60000]"

        Set rs = CreateObject("ADODB.RECORDSET")
        With rs
            .Source = strSQL
            .ActiveConnection = strConnection
            .Open
            Sheets("testtab").Range("A4").CopyFromRecordset rs              ' Ergebnis einfügen
            .Close
        End With
    End With

'ADO-Verbindung kappen
    cn.Close

    Set cn = Nothing
    Set rs = Nothing
Debug.Print Now()

End Sub

Welche Einschränkungen gibts denn da? Andere SQL-Befehle funktionieren ja auch ...

 

Ähm, ja.

Das sind sog. Common Table Expressions, kommen von Oracle, kann M$SQL, PostgreSQL und sicher auch einige andere 'richtige' Datenbanken. Spielzeug wie MySQL kann es nicht, und vermutlich zählt Excel auch in die Kategorie.

Ich überleg mal nach einer anderen Lösung, ...

 

Ich bin mir nicht sicher ob Excel SELECT * mag, ich würde die Spalten schon explizit nennen. Außerdem klammer ich die CASE-Schleife gerne.

Heißt deine Tabelle in SQL wirklich [Basis$A3:CZ60000] ? - Klingt nach Excel

Ansonsten kannst du immernoch eine Sicht in SQL erstellen und diese durch Excel abfragen.

PS: In Excel selbst kann man natürlich noch einfach eine Formel nehmen die auf die vorhergehende Spalte zurück greift.

 

Klasse, danke!

 

Naja, so die richtig zündende Idee hab ich nicht. In PG könnte ich noch die versteckte Spalte ctid verwenden, die je Row noch mal einen eindeutigen Wert liefert, aber das hat Excel sicherlich nicht.

Wozu brauchst Du das denn einklich? Und warum Excel? Und mit Excel kannst doch mit Verweisen arbeiten, oder? Ich hab von Excel keinen Plan, sorry.

 

Excel 'mag' SELECT * auch wenn ich später die Spalten explizit nenne werde alleine schon weil da noch mehr Bedingungen ´reinkommen.
CASE hab´ ich probehalber eingeklammert (vor CASE und nach END) leider mit dem selben Ergebnis:


Und ja, es ist Excel wg. offline Bedienung. Da liegt auch der Knackpunkt mit der 'Sicht': Das Excel-Sheet wird einmal von der MSSQL-Datenbank gefüllt, verlässt dann abgekoppelt den Orbit und landet bei Schreibtischtätern die dann auf verschiedene Werte filtern wollen und dabei wechselweise je nach Filter die Duplikate ein-/ausblenden wollen. Da es sich um >10k Datensätze handelt ist SQL innerhalb Excel der Turboboost.

 

Ja, ich hatte schon Lösungen mit Matrizenformeln bei deren Anwendung man das Bartwachstum der Anwender beinahe schon hören konnte ...

 

Also wenn die Daten von SQL kommen und in Excel weiter verwendet werden gibt es drei Wege.

A) Die Spalte, die Datensätze als doppelt markiert, kommt schon aus SQL und ist in Excel statisch. Dann würde ich in SQL eine Sicht auf die Tabelle machen die diese Spalte mitliefert und diese dann in Excel mit SELECT * abfragen.

B) Die Daten werden in Excel bearbeitet und die Markierung kann sich verändern. Dann würde ich in Excel mit Formeln arbeiten die die vorhergehende Spalte mit der selben Spalte, eine Zeile darüber vergleicht.

C) Die Daten werden zwar in Excel verändert, die Information soll aber unverändert bleiben. Dann könnte man auch mit Excel Formeln arbeiten, die Daten aber einmal in ein anderes Tabellenblatt mit der Option "nur Werte einfügen" kopieren. Das ist ein bischen unschön, geht aber.

 

A fällt aus weil in Excel dynamisch gefiltert/sortiert werden soll
B ist ein guter Ansatz, dazu müsste allerdings die nächste darüberliegende sichtbare Zelle verglichen werden: '=WENN(UND(A1=A2;TEILERGEBNIS(3;A2)>0);"x";"")' liefert da im gefilterten Zustand falsche Ergebnisse
C: So habe ich mir das auch vorgestellt, allerdings braucht Ihr dazu alle Informationen für die Abfragen:

Code:

ObjectName   Anzahl TAGs    PercentNotRequired    PercentSat    Stiction    NLI    RPI     OscillationIndex
Name1
Name1
Name2
Name2
Name3
Name3
Name4
Name4

1. Alle Datensätze wo z.B. Spalte3>0,1 und dann
   
2. die Anzahl aller 'ObjectName' in 'Anzahl TAGs' eintragen vielleicht so
   SELECT ObjectName,
   (SELECT COUNT(ObjectName) AS 'Anzahl TAGs'
   FROM Urtabelle AS CR2
   WHERE (ControllerResults.ObjectName = ObjectName)) AS 'Anzahl TAGs'
   FROM Urtabelle AS ControllerResults
   
3. Nun sollen alle Duplikate von 'ObjectName' verworfen werden

Das Ergebnis in ein separates Blatt zu schreiben ist kein Problem.
Gibts für sowas eine Lösung?

 

Du fürhst einfach die Abfrage für sagen wir Spalte A-Y aus so das Excel diese Spalten mit den Daten aus SQL in der richtigen Reihenfolge befüllt (siehe deine Ursprüngliche Abfrage). Wenn Spalte A dann die Grundlage für Doppelte Datensätze bildet machst du in Spalte Z, Zeile 2 eine Formel

Code:

=WENN(B1=A1;"Doppelter Datensatz";"")

Das kopierst du runter auf alle Zeilen. Dann alles markieren und nur die Werte in das nächste Tabellenblatt kopieren. Wenn sich dann die Sortierung ändert, bleibt die Markierung gesetzt.

Alternativ kann man auch das erste Tabellenblatt umsortieren oder Zeilen löschen und im Anschluss die Formel neu runter kopieren. Ich kenn ja nicht den Sinn hinter den Daten und den Doppelungen.