Excel 2007 duplikate nur im gefilterten

Walter_Haefliger · 12. November 2019 um 05:55

… bereich entfernen

Hallo zusammen
Ich muss immer wieder eine riesige Textdatei in Excel 2007 importieren.
Dir geschieht per VBA

Sub Schaltfläche2_KlickenSieAuf()
ChDrive „T:\NM_Public\E&T\NE_NW_Implement\CMTS CORE AUSLASTUNG\Reports 2011“
dateiname = Application.GetOpenFilename()
Workbooks.OpenText Filename:= _
dateiname _
, Origin:=xlMSDOS, StartRow:=3, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(3, 1), Array(34, 1), Array(48, 1), Array(89, 9), Array(101, 9), _
Array(111, 1), Array(121, 1), Array(132, 1), Array(143, 1), Array(158, 1)), _
TrailingMinusNumbers:=True
'Kopieren
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows(„CMTS Auslastung über 60%.xlsm“).Activate
Range(„A2“).Select
ActiveSheet.Paste

’ GigeFilter
ActiveSheet.Range("$A$1:blush:I$108980").AutoFilter Field:=4, Criteria1:= _
„=*Gige??0“, Operator:=xlOr, Criteria2:="=*Gige??6"
’ grösser60Filter
ActiveSheet.Range("$A$1:blush:I$108980").AutoFilter Field:=9, Criteria1:=">60", _
Operator:=xlAnd
End Sub
Nach der Filterung, sind im Feld2 (Spalte B) doppelte Einträge.

Wie kann ich das VBA ergänzen, so das es mir Ddoppelten Einträge (nur im gefilterten bereich) lösch? Natürlich sollen die Einträge jeweils einmal stehen bleiben.

Mit freundlichen Grüssen

Walti

Reinhard_fb8cb7 · 12. November 2019 um 05:55

Hallo Walter,

Ich muss immer wieder eine riesige Textdatei in Excel 2007
importieren.
Dir geschieht per VBA

teste mal den nachstehenden Code.

Nach der Filterung, sind im Feld2 (Spalte B) doppelte
Einträge.
Wie kann ich das VBA ergänzen, so das es mir Ddoppelten
Einträge (nur im gefilterten bereich) lösch? Natürlich sollen
die Einträge jeweils einmal stehen bleiben.

Nachfragen. Sollen nur die sichtbaren doppelten in B gelöscht werden oder alles doppelte in B?

Löschen bedeutet löschen der ganzen Zeile?

Gruß
Reinhard

Option Explicit

Sub Schaltfläche2\_KlickenSieAuf()
Dim dateiname As String
ChDrive "T"
ChDir "T:\NM\_Public\E&T\NE\_NW\_Implement\CMTS CORE AUSLASTUNG\Reports 2011"
dateiname = Application.GetOpenFilename(("Text Dateien (\*.txt), \*.txt"))
Workbooks.OpenText Filename:= \_
dateiname \_
, Origin:=xlMSDOS, StartRow:=3, DataType:=xlFixedWidth, FieldInfo:= \_
Array(Array(0, 1), Array(3, 1), Array(34, 1), Array(48, 1), Array(89, 9), Array(101, 9), \_
Array(111, 1), Array(121, 1), Array(132, 1), Array(143, 1), Array(158, 1)), \_
TrailingMinusNumbers:=True
'Kopieren
With "CMTS Auslastung über 60%.xlsm"
 ActiveSheet.UsedRange.Copy Destination:=.Range("A2")
 ' GigeFilter
 .Range("$A$1:blush:I$108980").AutoFilter Field:=4, Criteria1:= \_
 "=\*Gige??0", Operator:=xlOr, Criteria2:="=\*Gige??6"
 ' grösser60Filter
 .Range("$A$1:blush:I$108980").AutoFilter Field:=9, Criteria1:="\>60", \_
 Operator:=xlAnd
 '.Activate
End With
End Sub

Walter_Haefliger · 12. November 2019 um 05:55

Hallo Richard

Erst mal vielen Dank für deine Antwort.

nun zu deiner Frage.
Ja, es sollen nur die Sichbaren Daten gefiltert werden und Ja es sollen die ganzen Zeillen gelöscht werden.

Danke und Gruss

Walter Häfliger

Reinhard_fb8cb7 · 12. November 2019 um 05:55

Korrektur
Hallo Walter,

tausche bitte
With „CMTS Auslastung über 60%.xlsm“
in
With Workbooks(„CMTS Auslastung über 60%.xlsm“).Worksheets(„Tabelle1“)

Gruß
Reinhard

Walter_Haefliger · 12. November 2019 um 05:55

Hallo Richard

Ich habe den Code angepasst. Funktioniert trotzdem nicht. Liegt vermutlich daran, dass ich dir nicht geschrieben habe, dass ich gesammten importierten Daten in eine vorformatierte Excel- Datei Kopiere.
Sorry und

Reinhard_fb8cb7 · 12. November 2019 um 05:55

Ja, es sollen nur die Sichbaren Daten gefiltert werden und Ja
es sollen die ganzen Zeillen gelöscht werden.

Hallo Walter,

Option Explicit

Sub Schaltfläche2\_KlickenSieAuf()
Dim dateiname As String
ChDrive "T"
ChDir "T:\NM\_Public\E&T\NE\_NW\_Implement\CMTS CORE AUSLASTUNG\Reports 2011"
dateiname = Application.GetOpenFilename(("Text Dateien (\*.txt), \*.txt"))
Workbooks.OpenText Filename:= \_
dateiname \_
, Origin:=xlMSDOS, StartRow:=3, DataType:=xlFixedWidth, FieldInfo:= \_
Array(Array(0, 1), Array(3, 1), Array(34, 1), Array(48, 1), Array(89, 9), Array(101, 9), \_
Array(111, 1), Array(121, 1), Array(132, 1), Array(143, 1), Array(158, 1)), \_
TrailingMinusNumbers:=True
'Kopieren
With Workbooks("CMTS Auslastung über 60%.xlsm").Worksheets("Tabelle1")
 ActiveSheet.UsedRange.Copy Destination:=.Range("A2")
 ' GigeFilter
 .Range("$A$1:blush:I$108980").AutoFilter Field:=4, Criteria1:= \_
 "=\*Gige??0", Operator:=xlOr, Criteria2:="=\*Gige??6"
 ' grösser60Filter
 .Range("$A$1:blush:I$108980").AutoFilter Field:=9, Criteria1:="\>60", Operator:=xlAnd
 .Activate
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 Formel = "=IF(COUNTIF(B3:B$108980,B3)=1,1,"""")"
 With .Range("H3:H108980")
 .SpecialCells(xlCellTypeVisible).Formula = Formel
 .Value = .Value
 .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 .ClearContents
 End With
 Application.Calculation = xlCalculationAutomatic
 Application.S
End With
End Sub

Gruß
Reinhard

Walter_Haefliger · 12. November 2019 um 05:55

Hallo Richard

Jetzt kommt die Fehlermeldung „keine Zellen gefunden“ es hat aber doppelte Einträge in Spalte B

Gruss

Walti

Reinhard_fb8cb7 · 12. November 2019 um 05:55

Jetzt kommt die Fehlermeldung „keine Zellen gefunden“ es hat
aber doppelte Einträge in Spalte B

Hallo Walter,

kannst du eine Kopie der mappe machen, diese abspecken, 100.000 Datensätze brauchen wir nicht, Zellinhalte in unnötigen Spalten auch entfernen, dann hochladen mit fileupload o.ä., s. FAQ:2606

Gruß
Reinhard

Walter_Haefliger · 12. November 2019 um 05:56

Hallo Richard

Ich habe dir eine Kopie eines Teils der Daten plus ein Bild (damit du eine Ahnung der Zeillenanzahl bekommst)hochgeladen.
http://www.pic-upload.de/view-12389943/bild.jpg.html

http://www.file-upload.net/download-3974299/Kopie-de…

Gruss

Walter
P.S. bin bis 03.01.2012 nichmehr erreichbar.

Thomas_Ramel · 12. November 2019 um 05:56

Grüezi Walti

Jetzt kommt die Fehlermeldung „keine Zellen gefunden“ es hat
aber doppelte Einträge in Spalte B

Hmmm, wenn ich mir das Bild und die hochgeladene Datei ansehe, dann ist das für Spalte B wohl der Fall.

Aber diese Zeilen unterscheiden sich dann in anderen Spalten, wenn ich das richtig sehe.
Welche soll denn da jeweils übernommen und welche soll als Duplikat gelöscht werden?

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Walter_Haefliger · 12. November 2019 um 05:56

Grüezi Tomas

Es soll einfach immer die Erste Zeille,mit doppeltem Wert in „B“ erhalten bleiben. die andern Filterkriterien werden ja von allen Zeillen erfüllt.

Gruss

Walti

Thomas_Ramel · 12. November 2019 um 05:56

Grüezi Walti

Es soll einfach immer die Erste Zeille,mit doppeltem Wert in
„B“ erhalten bleiben. die andern Filterkriterien werden ja von
allen Zeillen erfüllt.

…das zeigt dein Bild aber ganz anders…

In Spalte D ist nach 2 Kriterien gefiltert - wenn da beide pro Duplikat in Spalte B vorkommen muss eine Entscheidung getroffen werden.

Dasselbe gilt auch für die Werte in Spalte I - die unterscheiden sich da auch jedesmal.

…oder geht es dir ‚nur‘ darum eine Auflistung der Namen in Spalte B zu erhalten, die zur momentan gesetzten Filterung passen?

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Walter_Haefliger · 12. November 2019 um 05:56

Grüezi Thomas

In Spalte „B“ stehen die Stationsnamen. Wir wollen nur sehen ob in einer Station die NSI_NAME welch mit „Gige 0“ oder „Gige 6“ enden in DOWN_UTILIZATION eine Wert grösser „60“ haben.
Deshalb brauchen wir Stationsnamen nur einmal.

Gruss

Walti

P.S. bin erst am 03.01.2012 wieder erreichbar

Thomas_Ramel · 12. November 2019 um 05:56

Grüezi Walti

In Spalte „B“ stehen die Stationsnamen. Wir wollen nur sehen
ob in einer Station die NSI_NAME
welch mit „Gige 0“ oder „Gige 6“ enden in DOWN_UTILIZATION
eine Wert grösser „60“ haben.
Deshalb brauchen wir Stationsnamen nur einmal.

Hmmm, dann kopiert doch diese Einträge in einen gesonderten Bereich, z.B. ein eigenes Tabellenblatt und löscht dann die Duplikate dort in dieser Spalte weg.

Das geht beides mit dem Spezialfilter ganz gut und ab xl2007 kannst Du über das Ribbon ‚Daten‘ die Duplikate auch ganz gezielt entfernen lassen.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Walter_Haefliger · 12. November 2019 um 05:56

Hallo Thomas

Das habe ich schon per Makro versucht. Ab einer gewissen länge der Liste, kopiert es nicht alle Daten. Dafür setzt es Leerzeillen ein.

Gruss

Walti

Thomas_Ramel · 12. November 2019 um 05:56

Grüezi Walti

Das habe ich schon per Makro versucht. Ab einer gewissen länge
der Liste, kopiert es nicht alle Daten. Dafür setzt es
Leerzeillen ein.

Das muss dann aber an den Daten liegen - der Spezialfilter arbeitest da äusserst zuverlässig, das kann ich aus eigener Erfahrung sagen.

Allenfalls hilft dir (ohne auf den Spezialfilter zurückzugreifen) das folgende noch:

In einer Hilfsspalte die folgende Formel:

=B1B2

Und dann nach Spalte B sortieren und nach der Hilfsspalte auf WAHR filtern.

Die eingeblendeten Zeilen der Unikate in Spalte B liegen dann als Block untereinander.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 12. November 2019 um 05:57

Hallo Walter, grüezie Thomas,

Das habe ich schon per Makro versucht. Ab einer gewissen länge
der Liste, kopiert es nicht alle Daten. Dafür setzt es
Leerzeillen ein.

Das muss dann aber an den Daten liegen - der Spezialfilter
arbeitest da äusserst zuverlässig, das kann ich aus eigener
Erfahrung sagen.

mal eine Frage an euch beide, mein Excel 2007 steigt regelmäßig aus wenn ich da in diesen 108.000 Zellen Formeln eintragen lasse
D.h. wenn diese Codezeile der Formelzuweisung bearbeitet wird so ist dann jedes Mal z.B. kein Umschalten mehr zwischen VB-Editor und Excel
möglich, die Anzeige wird dann zu weißem Bildschirm oder ein weißer Bildschirm mit Teilbildchen der Editor bzw. Excelansicht.

Schalte ich in der taskleiste auf z.B. das Browserfenster so dauert das lange und der Seitenaufbau wenn ich in w-w-w ein neues Brett/Beitrag anklicke dauert recht lange.
Rufe ich mit dem Affengriff den Taskmanager auf so kommt der auch erst sehr verzögert. Beende ich im TM dann Excel so dauert es auch lange bis das umgesetzt wird.

Natürlich habe ich dann den gleichen Code mit nur 10.000 Zellen getestet, geht recht schnell, nicht blitzschnell.
Erhöhe ich die Zellanzahl auf 50.000, dann 90.000
dann dauert es schon, aber das sind nur so geschätzte maximal 30-60 Sekunden.

Habt ihr diese Problematik in XL 2007 nicht?

Zum Spezialfilter kann ich nur das bestätigen was Thomas sagt
ABER ich hab das immer nur mit wenig Datenmengen, max. 1000 Zeilen getestet. Scheinbar muß ich das mal im Zuge dieser Anfrage mal mit 1.000.000 Datenzeilen testen.

Gruß
Reinhard

Thomas_Ramel · 12. November 2019 um 05:57

Grüezi Reinhard

mal eine Frage an euch beide, mein Excel 2007 steigt
regelmäßig aus wenn ich da in diesen 108.000 Zellen Formeln
eintragen lasse

…hast du denn eine Datei gefunden/erhalten mit so vielen Datensätzen oder selbst eine gemacht?

Ich habe meine Formel von weiter oben über 135’000 Zeilen per Doppelklick aufs Ausfüllkästchen eingefügt, das ging eigentlich recht fix.

D.h. wenn diese Codezeile der Formelzuweisung bearbeitet wird
so ist dann jedes Mal z.B. kein Umschalten mehr zwischen
VB-Editor und Excel
möglich, die Anzeige wird dann zu weißem Bildschirm oder ein
weißer Bildschirm mit Teilbildchen der Editor bzw.
Excelansicht.

Vielleicht war da gerade sonst noch viel los auf deinem Rechner?
Ist das auch in einer neu gestarteten Instanz so der Fall?

Wie genau hat die Formel die Du eingefügt hast denn gelautet?

Natürlich habe ich dann den gleichen Code mit nur 10.000
Zellen getestet, geht recht schnell, nicht blitzschnell.
Erhöhe ich die Zellanzahl auf 50.000, dann 90.000
dann dauert es schon, aber das sind nur so geschätzte maximal
30-60 Sekunden.

Habt ihr diese Problematik in XL 2007 nicht?

Mit VBA habe ich das (noch?) nicht geprüft, aber das reine Einfüllen über Auto-Ausfüllen hat wie gesagt gut funktioniert.

Zum Spezialfilter kann ich nur das bestätigen was Thomas sagt
ABER ich hab das immer nur mit wenig Datenmengen, max. 1000
Zeilen getestet. Scheinbar muß ich das mal im Zuge dieser
Anfrage mal mit 1.000.000 Datenzeilen testen.

Der braucht wohl einen Moment - je nach Menge der Daten die als Ergebnis herauskommen, aber stabil und in der Regel flink ist der Spezialfilter schon.

Ich habe damit in der Firma schon ‚selbstfilternde‘ Listen gebaut, die automatisch nach Einfügen eines Buchstabens filtern, klappt hervorragend und schnell.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 12. November 2019 um 05:57

Grüezie Thomas,

mal eine Frage an euch beide, mein Excel 2007 steigt
regelmäßig aus wenn ich da in diesen 108.000 Zellen Formeln
eintragen lasse

…hast du denn eine Datei gefunden/erhalten mit so vielen
Datensätzen oder selbst eine gemacht?

nichts von alledem, nicht mal an der Mappe des Anfragers ausprobiert.
Probiert habe ich in einer neuen leeren Mappe.
Allein nur mit .Formula da eine Formel eintragen lassen in einer Spalte.

Bis 90.000 Zellen/zeilen in der Spalte ging es gut, dauerte etwas aber okay. Bei 108.xxx Zellen/Zeilen in der Spalte verabschiedete sich mein XL 2007.
Du kannst sicher sein, da ich ja nach einmaligem Erlebnis, es erst mit 10.000 , dann mit 50.000, dann mit 90.000 Datensätzen probierte habe ich Excel für 108.xxx genug Zeit gelassen.

Aus der Erinnerung her weiß ich genau, ich hab mal um zu wissen wie lang da was in XL 2007 dauert alle 1 Million Zellen einer Spalte oder waren es mehrere Spalten, mit Werten füllen lassen.
Das dauerte halt wie erwartet aber funktionierte.

Deshalb erstaunt es mich daß mein Xl 2007 sich bei schlappen 100.000 Zellen verabschiedet.
Beim jetzigen Erkenntnisstand (naja, Erkenntnis ist was anderes *gg*)
werde ich das mal genauer testen.

Aber das mache ich sehr widerwillig. Von mir aus gesehen ist es so, teste ich den gleichen Code für 90.000 Zellen so klappt das.
Teste ich den Code für 108.000 Zellen steigt Excel aus.
Was also soll es bringen wenn ich eruiere daß bei mir 93456 Zellen noch klappen aber 93457 Zellen nicht mehr? ((

Wäre das bei euch beiden auch, okay, dann würde ich bei xlam.ch nachschauen ob es da Limitationen gibt.
Aber vorstellen daß es da in dem Fall Limitationen gibt kann ich mir nicht.
Die Entwickler von XL 2007 wissen doch daß mal einer wie ich kommt der per Vba eine ganze Spalte, von mir aus auch alle Zellen mit Formeln füllt.
Dafür darf ja Excel eine Zeitlang brauchen, aber abstürzen?

Ist das auch in einer neu gestarteten Instanz so der Fall?

Leider ja.

Wie genau hat die Formel die Du eingefügt hast denn gelautet?

Formel = „=IF(COUNTIF(B3:B$108980,B3)=1,1,“""")"
With .Range(„H3:H108980“)
.SpecialCells(xlCellTypeVisible).Formula = Formel

wobei das nicht ganz stimmt. Anfangs habe ich die Formel in alle Zellen von
.Range(„H3:H108980“)
eingefügt. Dann kam es zu den Abstürzen.
Um da die zellenmenge zu reduzieren kam ich auf
.SpecialCells(xlCellTypeVisible).Formula

FroWei ^ Gruß
Reinhard

Thomas_Ramel · 12. November 2019 um 05:57

Grüezi Reinhard

mal eine Frage an euch beide, mein Excel 2007 steigt
regelmäßig aus wenn ich da in diesen 108.000 Zellen Formeln
eintragen lasse

…hast du denn eine Datei gefunden/erhalten mit so vielen
Datensätzen oder selbst eine gemacht?

nichts von alledem, nicht mal an der Mappe des Anfragers
ausprobiert.
Probiert habe ich in einer neuen leeren Mappe.
Allein nur mit .Formula da eine Formel eintragen lassen in
einer Spalte.

Ach so, OK.

Du kannst sicher sein, da ich ja nach einmaligem Erlebnis, es
erst mit 10.000 , dann mit 50.000, dann mit 90.000 Datensätzen
probierte habe ich Excel für 108.xxx genug Zeit gelassen.

Mein xl2007 ist da auch ausgestiegen mit der Meldung, dass der Vorgang mit den zur Verfügung stehenden Ressourcen nicht ausgeführt werden kann.

Wäre das bei euch beiden auch, okay, dann würde ich bei
xlam.ch nachschauen ob es da Limitationen gibt.
Aber vorstellen daß es da in dem Fall Limitationen gibt kann
ich mir nicht.

Wie gesagt, auch hier ist das so der Fall.

Ist das auch in einer neu gestarteten Instanz so der Fall?

Leider ja.

Habe ich nun soeben geprüft und kann es bestätigen.

Wie genau hat die Formel die Du eingefügt hast denn gelautet?

Formel = „=IF(COUNTIF(B3:B$108980,B3)=1,1,“""")"
With .Range(„H3:H108980“)
.SpecialCells(xlCellTypeVisible).Formula = Formel

Definitiv eine zu komplexe Formel für einen solch grossen Bereich würde ich mal sagen. Auch wenn ich die Berechnung vor dem Eintragen auf ‚Manuell‘ setze fliegt mir Excel um die Ohren…

Damit aber gings im Nu auch ohne die Berechnung auf ‚Manuell‘ zu setzen:

Formel = "=B1B2"

FroWe und GlüNeJa

Thomas Ramel

MVP für MS-Excel -