Verwijderen van duplicaten

[gaggie]

Heb een test xslm waarbij ik op tabblad database de duplicaten eruit wil halen, deze mogen niet verwijderd maar moeten gecleared worden.
Daar heb ik een stukje code voor gevonden maar krijg steeds een fout melding.

Krijg steeds Fout 1004 tijdens uitvoering.
Door de toepassing of door object gedefineerde fout.

Regel die begint met:
.Range("A2:M" & lastrow).Clear Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)

de code is:

Sub verwijder_duplicaten_database()

    Dim lastrow As Long

    With ThisWorkbook.Worksheets("Database")
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A2"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lastrow = 1
        End If

        'Array(1, 2, 16) means 1 - for A, 2 for B and 16 for P columns
        .Range("A2:M" & lastrow).Clear Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13)
        End With
End Sub

Ik voeg het testbestandje toe. Op tabblad 3 staat hetzelfde als op tabblad database dit om het steeds te kunnen kopieëren.

Mvg.Bekijk bijlage database en historie.xlsm

Gaggie

 

[Niels28]

Je kunt de .removeduplicates niet vervangen door .clear
Deze functie verwijdert de duplicaten en maakt niet de cellen leeg.


Niels

 

[gaggie]

Beste Niels28

Is er misschien wat anders, ik wil dat het gecleard wordt en niet verwijderd.

Gaggie

 

[Niels28]

Wat is de reden dat het niet verwijderd mag worden? wat heb je aan lege regels in je database, dit kan weer andere problemen opleveren.

Niels

 

[gaggie]

Als ik van dat tabblad iets verwijderd, wordt op een ander tabblad waar index formules staan ook aangepast en dan krijg ik allemaal ### tekens

 

[Niels28]

Dan lijk het mij verstandig die index formule aan te pakken en niet het verwijderen.
Geeft het bereik in de index formule een naam ipv verwijzen naar cellen

Niels

 

[gaggie]

Hoe doe ik dat?
Zo ziet de index formule eruit.

=INDEX(Database!C$1:C$10000;SOMPRODUCT((Database!$A$1:$A$10000=$A11)*(Database!$B$1:$B$10000=$B11)*RIJ($1:$10000));1)

 

[Niels28]

wat doe die formule?

 

[gaggie]

Haalt waardes uit de database.
Hij zoekt of $a11 en $B11 in die regel voorkomt en haalt dan de waardes op.
Ik kan nog de 10000 aanpassen naar een kleiner getal, bv 3000

 

[Niels28]

die formule kan niet kloppen daarom gaat het mis, je zoek of de naam in kolom A en de naam in kolom B hetzelfde zijn en dan wil je de waarde uit C.
Als er nu 2x een overeenkomst is wordt de rij die je zoekt met 2 vermenigvuldig.


Niels

 

[Niels28]

Volgens mij bedoel je dit:
Formule afsluiten met ctrl-shift-enter

=INDEX(Database!A1:M21;VERGELIJKEN($A$11&$B$11;Database!$A$1:$A$21&Database!$B$1:$B$21;0);3)

nog wel even je bereik aanpassen.

Niels

 

[gaggie]

ik wil die formule best eens proberen, maar die andere heb ik van SNB gekregen en dat werkt.

 

[Niels28]

Ik heb het even nagekeken en wat mij daar op valt is dat je toen geen dubbele waarde in de database had staan.
Maar als het werkt wat is dan het probleem

Niels

 

[gaggie]

als ik regels verwijder van tabblad Database dan wordt die index formule aangepast dan wordt 10000 verminderd met het aantal regels wat je bij Database verwijderd.
voor de rest werkt die regel perfekt.

Daarom wilde ik graag Clear gebruiken.

 

[Niels28]

Mijn vraag was retorisch,

Je vind die formule heilig dus een andere oplossing is niet goed.
Ik ga er verder ook niet tegen in want SNB is veel verder in excel dan ik en hou het
er dan ook op dat ik niet weet wat die formule dan doet.


Niels

 

[gaggie]

in ieder geval bedankt, zal vanavond toch die van jou nog eens proberen in een testbestand

 

[Niels28]

Omdat ik er toch niet tegen kan om er niet tegenin te gaan
Zie waarom ik zeg dat die formule niet werkt in je huidige bestand.

Dit is het bestand van je originele vraag waar SNB op antwoordde.
Pas de cel aan die ik aangegeven heb en zie wat er fout gaat.


Bekijk bijlage example filled in for the first two person.xlsm

Door de dubbele waardes in je database valt het rijnummer op een gegeven moment buiten je index.


Niels

 

[gaggie]

en als je jouw formule dan in tabblad groepsoverzicht zet.
daar waar die nummers beginnen in rij 11. E11 t/m J11
Als jij daar die formule zou kunnen neerzetten en een paar kolommen doortrekken dan kan ik kijken hoe dat nou gedaan wordt.

 

[Niels28]

Voor deze keer, het is jouw bestand en jouw vraag dus dat had je zelf ook kunnen doen.


Bekijk bijlage example filled in for the first two person.xlsm


Niels

 

[gaggie]

Dat lijkt inderdaad te werken.

=INDEX(Database!$A$1:$K$56;VERGELIJKEN($A11&$B11;Database!$A$1:$A$56&Database!$B$1:$B$56;0);KOLOM()-2)

Na Index database!$A$1:$K$56 ik neem aan dat dat het totale bereik is van de database
Het vergelijken is duidelijk maar wat doet de ;0
Daarna wordt het wazig KOLOM()-2)

Als je wilt graag een kleine uitleg dan snap ik in ieder geval wat het doet.

En je had het over een naam geven aan je bereik?

Gaggie