Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1804to1808
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zellen vergleichen und ggf löschen

Zellen vergleichen und ggf löschen
15.01.2021 13:16:27
max
Hallo,
ich würde gerne das Eintrittsdatum mit den anderen vielen Daten in der gleichen Zeile vergleichen und falls das Eintr.Datum kleiner ist, den Inhalt des größeren Datums löschen. dh Datum + weitere 2 Zeilen die davor sind.
(siehe screenshot und txt.file)
Userbild
https://www.herber.de/bbs/user/143031.txt
Ich hoffe es war verständlich.
LG
Max

27
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellen vergleichen und ggf löschen
15.01.2021 13:48:46
Günther
Moin Max,
wenn die Daten in der Original-Tabelle geändert werden sollen (nicht empfehlenswert), dann verwende VBA. Soll eine Kopie der Rohdaten entsprechend bearbeitet werden, dann mache ich so etwas mit Power Query.
 
Gruß
Günther  |  mein Excel-Blog
AW: Zellen vergleichen und ggf löschen
15.01.2021 14:15:01
max
Danke Günther für deinen Vorschlag.
Leider kann ich mit Power query nichts anfangen.
Ich kenn mich auch leider nicht so gut aus mit Excel usw.
Ich war so blauäugig und war der Meinung, dass es evtl. mit "wenndann Formel" funktioniert.
Trotzdem danke.
Anzeige
AW: Zellen vergleichen und ggf löschen
15.01.2021 14:38:16
Daniel
Hi
Sinnvoller ist, die Exceldatei hochzuladen und die Inhalte, die gelöscht werden sollen zu kennzeichnen oder auf zwei Blättern Vorher und Nacher darzustellen.
Ich würde hier nach Spalte D eine zusätzliche Spalte einfügen mit der Formel
=$A2&ltD2
Dann filterst du in diese Spalte nach WAHR
Damit sind nur die Zeilen sichtbar, in denen Spalte B:D gelöscht werden sollen, was du bedenkenlos tun kannst, da in gefilterten Tabellen die ausgeblendeten Zeilen nicht bearbeitet werden.
Wenn du mehrere Spalten hast, wiederholt du das für jede Spaltengruppe.
Gruß Daniel
Anzeige
AW: Zellen vergleichen und ggf löschen
15.01.2021 15:16:45
max
Hallo Daniel,
vielen Dank für deinen Vorschlag!
Leider konnte ich keine Excel Datei hochladen, war zu groß.
Ich habe glaube ich über 400 Spalten, das wird lange dauern, bis ich das habe.
LG
Max
AW: Zellen vergleichen und ggf löschen
15.01.2021 15:20:40
Daniel
ein kleiner Ausschnitt, aus dem man erkennt worum es geht, reicht ja.
wenn du dazuschreibst, wie groß die Datenmenge tatsächlich ist, dann genügt das in der regel.
war mein Vorschlag jetzt für dich hilfreich oder nicht?
Gruß Daniel
AW: Zellen vergleichen und ggf löschen
15.01.2021 14:46:07
Yal
Hallo Max,
kurz gemeinsam, aber langsam nochmal lesen:
ich würde gerne das Eintrittsdatum mit den anderen vielen Daten in der gleichen Zeile vergleichen
und falls das Eintr.Datum kleiner ist,
den Inhalt des größeren Datums löschen.
dh Datum + weitere 2 Zeilen die davor sind.
Was will uns der Künstler damit sagen?
Meinst Du:

Für die gegebene Eintrittsdatum in A2 soll beim Vergleich mit Datum in A5:A8
_ Wenn Datum kleiner als Eintrittsdatum, gesamte Zeile komplett löschen
_ Wenn Datum gleich Eintrittsdatum, alle Daten dieser Zeile von Spalte B bis letzte Spalte  _
löschen

?
VG
Yal
Anzeige
AW: Zellen vergleichen und ggf löschen
15.01.2021 15:13:10
max
Hallo YAL,
musste laut lachen :))
nein so war das nicht gemeint. Schau dir mal bitte den Screenshot an.
Die Zeilen sind voneinander unabhängig. In jeder 3. Spalte steht ein anderes Datum wie man sieht. Das Eintrittsdatum (A2) soll mit diesen Daten in den jeweiligen Spalten abgeglichen werden. und falls zBsp. A2 kleiner als D2, dann sollen B2 bis D2 gelöscht werden.
Ich hoffe ich habe es nicht noch komplizierter gemacht.
Lg
max
AW: Zellen vergleichen und ggf löschen
15.01.2021 15:45:00
Yal
Hallo Max,
folgendes sollte es tun (nicht getestet)
Sub test()
Dim r, c
For r = 2 To Me.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For c = 4 To Me.UsedRange.SpecialCells(xlCellTypeLastCell).Row Step 3
With Me.Cells(r, c)
If DateValue(.Text) 
VG
Yal
Anzeige
Korr.: ClearContents mit "s" am Ende. oT
15.01.2021 15:45:53
Yal
AW: Korr.: ClearContents mit "s" am Ende. oT
17.01.2021 14:50:57
max
Hallo Yal,
vielen herzlichen Dank. Ich werde es dann am Montag gleich ausprobieren.
Echt super!
LG
Max
AW: Zellen vergleichen und ggf löschen
15.01.2021 15:57:15
Daniel
Hi
ganz einfache lösung, ohne VBA und sonstige "Tricks"
1. kopiere die Zeile 1 und die Spalte 1 in ein zweites Tabellenblatt (oder kopiere die ganze Tabelle, geht auch)
2. in die Zelle B2 der Tabelle2 kommt folgende Formel:
=WENN(Tabelle1!$A2&ltINDEX(Tabelle1!2:2;1;SPALTE()+VERGLEICH("Datum";B$1:E$1;0)-1);"";Tabelle1!B2)
diese ziehst du dann nach unten und nach rechts bis zum Ende der Tabelle2
3. kopiere die ganze Tabelle2 und füge sie an gleicher Stelle als Wert ein.
arbeite mit der 2. Tabelle weiter.
Gruß Daniel
Anzeige
AW: Zellen vergleichen und ggf löschen
17.01.2021 14:52:22
max
Hallo Daniel,
vielen vielen Dank auch an Dich!
Ihr erleichtert mir die Arbeit dadurch sehr.!
Werde es am Montag gleich testen.
LG
Max
AW: Zellen vergleichen und ggf löschen
15.01.2021 16:19:45
Daniel
Hi
Wenns mit VBA sein soll, dann kopiert man sich die Daten in ein Array und bearbeitet dieses Array in einer Schleife über Zeilen und Spalten.
dann schreibt man das Array zurück.
könnte man zwar auch in der Tabelle direkt nachen, ist aber langsam.
Code hierfür sieht so aus:
Sub Makro1()
Dim arr
Dim z As Long, s As Long
With ActiveSheet.Cells(1, 1).CurrentRegion
arr = .Value
For s = 2 To UBound(arr, 2)
If arr(1, s) = "Datum" Then
For z = 2 To UBound(arr, 1)
If arr(z, 1) 
oder man baut sich die Formellösung, die bereits vorgestellt habe, in VBA nach.
(Tabelle2 bitte vorher anlegen) (Codeformatierung funktioniert wegen der Formel leider nicht korrekt, daher so:
Sub Makro2()
Sheets("Tabelle1").Cells(1, 1).CurrentRegion.Copy Sheets("Tabelle2").Cells(1, 1)
With Sheets("Tabelle2").Cells(1, 1).CurrentRegion
With .Resize(.Rows.Count - 1, .Columns.Count - 1).Offset(1, 1)
.FormulaR1C1 = "=IF(Tabelle1!RC1&ltINDEX(Tabelle1!R,1,COLUMN()+MATCH(""Datum"",R1C:R1C[3],0)-1),"""",Tabelle1!RC)"
.Formula = .Value
End With
End With
End Sub

Gruß Daniel
Anzeige
AW: Zellen vergleichen und ggf löschen
15.01.2021 18:10:13
Yal
Hallo Daniel,
warum der Weg über einen 2-dimensionale Array, wenn eine Excel-Tabelle sowieso bereit ein 2-dimensionale Struktur ist?
Ausser bei der Ermittlung der letzte Zeile bzw Spalte mit UBound anstatt SpecialCells(xlCellTypeLastCell).Column, ist die zusätzliche Bequemlichkeit recht beschränkt.
Version CurrentRegion
Sub Makro1()
Dim z As Long, s As Long
With ActiveSheet.Cells(1, 1).CurrentRegion
For s = 2 To .SpecialCells(xlCellTypeLastCell).Column
If .Cells(1, s) = "Datum" Then
For z = 2 To .SpecialCells(xlCellTypeLastCell).Row
If .Cells(z, 1) 
Version UsedRange
Sub Makro1()
Dim z As Long, s As Long
With ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.UsedRange)
For s = 2 To .SpecialCells(xlCellTypeLastCell).Column
If .Cells(1, s) = "Datum" Then
For z = 2 To .SpecialCells(xlCellTypeLastCell).Row
If .Cells(z, 1) 
Wenn sowieso irgendwo etwas im ersten Zeile und etwas im ersten Spalte vorhanden ist,
dann die Kurzversion von UsedRange:
Sub Makro1()
Dim z As Long, s As Long
With ActiveSheet.UsedRange
For s = 2 To .SpecialCells(xlCellTypeLastCell).Column
If .Cells(1, s) = "Datum" Then
For z = 2 To .SpecialCells(xlCellTypeLastCell).Row
If .Cells(z, 1) 
Alles sehr ähnlich. Aber eben ohne den Umweg über einen Array.
VG
Yal
Anzeige
AW: Zellen vergleichen und ggf löschen
15.01.2021 18:26:29
Daniel
Hi
ja kann man machen bei kleinen Datenmengen, bei großen Tabellen ist der Weg übers Array schneller.
jede Zellwertänderung löst in Excel eine Reihe von hintergrundprozessen aus, die jedes mal ablaufen, wenn du einen Wert änderst.
wenn du direkt in der Tabelle arbeitest, stößt jedes einzelne Löschen diese Hintergrundprozesse an.
schreibe ich das Array als ganzes zurück, laufen diese Prozesse zwar auch, aber nur einmal für alle Zellen gemeisam.
Ist so, wie wenn beim Bäcker der Kunde vor dir jedes seiner 10 Brötchen einzeln bezahlt und dafür jedesmal die Cents in einer Geldbörse zusammen sucht, oder ob er alle auf einmal bezahlt.
einen Teil dieser Hintergrundprozesse kann man ausschalten, aber nicht alle (guckst du GetMoreSpeed)
außerdem muss man dafür sorge Tragen, dass diese Hintergrundprozesse auch wieder eingeschaltet werden, das tun nämlich nicht alle automatisch, und dann funktionieren u.U. nicht mehr alle Dinge wie gewohnt.
weitere Vorteil ist beim Debuggen.
Wenn beispielsweise in der Schleife ein Fehler auftritt den ich korrigieren muss, habe ich, wenn ich direkt in der Datei arbeite, u.U. schon einen Teil der Daten verändert, und muss ggf. die Orignialdaten wieder herstellen, um das Makro nach Korrektur erneut laufen lassen zu können.
passiert das im Array vor dem zurückschreiben (was ja immer der letzte schritt ist), so sind die Originaldaten noch unverändert und man kann direkt das Makro wiederholen.
oder man lässt sich das Array an anderer Stelle ausgeben und überschreibt die Originaldaten erst, wenn man sicher ist dass es korrekt läuft.
oder stellst dir so vor:
Excel ist ein Bürohaus und jede Zelle ist ein Büro mit einer Tafel die den Wert enthält.
jetzt kannst du von Büro zu Büro laufen, oder du schreibst dir erstmal alle Werte auf einen Zettel und arbeitest dann nur noch mit diesem Zettel.
Gruß Daniel
Anzeige
AW: Zellen vergleichen und ggf löschen
15.01.2021 21:36:52
Yal
Hallo Daniel,
ja, das ist schlüssig: Ereignis-Prozeduren und alles was im Hintergrund passiert nur einmal zu triggern, ist performanter.
Beim Debuggen ist es abzuwegen, zwischen die Komplexität von Code und "Rettung" der Stand. Aber die Komplexität war hier nicht ausschlaggebend.
Vielen Dank für deine Erklärung. Es öffnet mir "neue Sichten".
Schönes Wochenende
Yal
AW: Zellen vergleichen und ggf löschen
17.01.2021 16:20:05
max
Eine Frage noch
wie kann ich, die noch vorhandenen ICD-10 Codes, am Ende der Zeile verketten, ohne sie einzeln anzuklicken. dh wenn Feld ist nicht leer, dann soll verkettet werden. (NUR ICD-10)
siehe screenshot. Userbild
Vielen Dank an alle nochmal.
LG
Max
Anzeige
AW: Zellen vergleichen und ggf löschen
18.01.2021 10:55:44
Daniel
Hi
hängt von deiner Excelversion ab.
mit Excel 365 könnte da über TEXTVERKETTEN was gehen, so in der Art (ich hab noch kein 365)
=TextVerketten(Wenn(A$1:J$1="ICD-10";A2:J2);...)
mit ältern Excelversionen ohne Textverketten musst du jede Zelle einzeln anklicken.
dh da gibt es eine alternative, in dem du auf einem zweiten Tabellenblatt die Texte nach rechts immer erweiterst, bis in der letzten Zelle der vollständige Text steht.
dh auf einem dritten Tabellenblatt in Zelle C2:
=Wenn(Und(TAbelle2!C$1="ICD-10";Tabelle2!C2"");B2&", "&Tabelle2!C2;"")&B2
in der letzten Spalte steht dann das vollständige Ergbnis, dass du dann nach Tabelle2 übernehmen kannst.
Gruß Daniel
AW: Zellen vergleichen und ggf löschen
18.01.2021 11:31:28
Yal
Hallo zusammen,
ohne Office 365 baust Du einfach die Funktion TextVerketten als User Defined Function nach:
Public Function TextVerketten(Trennzeichen, Leer_ignorieren As Boolean, TextListe) As String
Dim E
Dim Erg
If IsMissing(Trennzeichen) Then Trennzeichen = ""
For Each E In TextListe
If Not (Leer_ignorieren And E = "") Then Erg = Erg & Trennzeichen & E
Next
TextVerketten = Erg
End Function
Dann so im Blatt verwenden:
{=TextVerketten(;Wahr;Wenn(A$1:J$1="ICD-10";A2:J2;""))}
Matrix-Formel: die geschweifte Klammer werden nicht eingegeben, das Editieren muß mit Shift+Strg+Enter abgeschlossen werden.
VG
Yal
äh, sorry. Unzureichend getestet
18.01.2021 11:39:13
Yal
so wird es besser aussehen:
Public Function TextVerketten(Trennzeichen, Leer_ignorieren As Boolean, TextListe) As String
Dim E
Dim Erg
If IsMissing(Trennzeichen) Then Trennzeichen = ""
For Each E In TextListe
If E = "" Then
If Not Leer_ignorieren Then
Erg = Erg & Trennzeichen & E
End If
Else
Erg = Erg & Trennzeichen & E
End If
Next
TextVerketten = Erg
End Function
VG
Yal
Verflixt, nochmal!!!
18.01.2021 12:01:58
Yal
Jetz' ham'ma's:
Public Function TextVerketten(Trennzeichen, Leer_ignorieren As Boolean, TextListe) As String
Dim E
Dim Erg
Erg = ""
If IsMissing(Trennzeichen) Then Trennzeichen = ""
For Each E In TextListe
If E = "" Then
If Not Leer_ignorieren Then Erg = Erg & Trennzeichen & E
Else
Erg = Erg & Trennzeichen & E
End If
Next
TextVerketten = IIf(Trennzeichen = "", Erg, Mid(Erg, 2))
End Function
Die gute alte Wahrheitstabelle ist doch manchmal sinnvoll!
VG
Yal
AW: Verflixt, nochmal!!!
18.01.2021 13:04:04
max
Danke Yal!
Ihr seid die Besten :)
LG
Max
AW: Zellen vergleichen und ggf löschen
18.01.2021 13:04:33
max
Hallo Daniel,
vielen Dank!
LG
Max
AW: Zellen vergleichen und ggf löschen
19.01.2021 14:59:35
max
Hello again,
könnte ihr mir bitte "wieder" weiterhelfen.
Ich möchte die Formel "..sverweis" nur für jede 2.Spalte übernehmen. (siehe screenshot)
(Es sind ca. 1300 Spalten und 750 Zeilen)
Falls ihr auch noch wisst, wie man die leeren Zellen löscht (grau markiert) und nach oben verschiebt, wäre ich umso dankbarer. (siehe screenshot)
Userbild
LG
Max
AW: Zellen vergleichen und ggf löschen
15.01.2021 18:34:33
Daniel
ergänzend zu meinem zuvor gesagten:
ersetze mal das
.Cells(z, s - 0) = ""
.Cells(z, s - 1) = ""
.Cells(z, s - 2) = ""

durch ein
.Cells(z, s).Offset(0, -2).Resize(, 3).ClearContents

und prüfe, ob das die Laufzeit verbessert, denn hier hast du nur noch 1/3 der schreibenden Zugriffe in der Tabelle.
(Datenmenge groß genug machen)
Gruß Daniel
AW: Zellen vergleichen und ggf löschen
19.01.2021 15:02:32
max
Hello again,
könnte ihr mir bitte "wieder" weiterhelfen.
Ich möchte die Formel "..sverweis" nur für jede 2.Spalte übernehmen.
(Es sind ca. 1300 Spalten und 750 Zeilen)
Falls ihr auch noch wisst, wie man die leeren Zellen löscht (grau markiert) und nach oben verschiebt, wäre ich umso dankbarer. (siehe screenshot)
Userbild
LG
Max
AW: Zellen vergleichen und ggf löschen
20.01.2021 14:04:30
Daniel
HI
Sverweis in jede zweite Spalte übernehmen ohne die dazwischenliegenden zu überschreiben ist etwas aufwendig.
ausgehend vom den Screenshot so:
wichitig ist hierbei die Menüfunktion START - BEARBEITEN - SUCHEN UND AUSWÄHLEN - INHALTE bzw deren Untermenü. Dieses wird in folge mehrfach verwendet, weshalb ich es nur als INHALTE abkürzen werde.
Gemeint ist dann dieser Menüpunkt, welchen du auch über F3 erreichen kannst
1. ganze Zeile 2 markieren
2. INHALTE - Konstanten - Zahlen
3. START - ZELLEN - FORMAT - SICHTBARKEIT - Spalten ausblenden
4. alle Zellen markieren, die die SVerweisformel erhalten sollen
5. INHALTE - nur sichtbare Zellen
6. Formel für die aktive Zelle eintippen und Eingabe mit STRG+ENTER abschließen.
die Formel wird unter Berücksichtigung von relativen und absoluten Zellbezügen in alle markierten Zellen übernommen. Die Bezüge auf ausgeblendete Zellen müssen eingetippt werden.
7. alles markieren und Spalten einblenden.
zu deiner zweiten Frage: das passiert automatisch, wenn du "Zellen löschen" ausführst.
wenn du nicht ganze Zeilen oder ganze Spalten markiert hast, fragt Excel dich dann ob die Zellen nach oben oder nach links nachrücken sollen, da musst du dann das passende auswählen.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige