Excel - Programm/Formel für automatisches löschen und verschieben von Zellen

[whispers]

Hallo Zusammen

Ich habe auf Arbeit eine Tabelle, bei welcher ich Personen anhand Ihrer Referenznummer auf Ihren Partner in der Tabelle suchen soll, passende Partner zusammenschieben und alte löschen muss...

Klingt super uninteressant und ja, dass ist es auch. Zumindest aus Sicht der Arbeit her. Mein Gedanke war es ob es dafür nicht eine Formel gibt oder vielleicht irgendeinen Code, welcher dies für mich erledigt.

Da es schwer zu erklären ist, findet ihr im Anhang eine stark gekürzte Excel Datei als Beispiel (mit Arbeitsauftrag).

Habe jetzt schon einiges probiert, aber komme nicht so ganz draus.

Danke im Voraus für eure Hilfe und Grüsse

whispers

Excel-Beispiel:
Anhang anzeigen Excel Tabelle.xlsx

 

[MickH74]

Das Ausfüllen der Felder kann man mit einem SVerweis machen. Aber du kannst mit einer Formel keine Zeilen löschen. Damit würdest du ja auch den Verweis löschen.
Was du vor hast, würde ich mit einem VBA-Makro umsetzen.

Gruß,
Mick

 

[whispers]

Danke für die super schnelle Antwort. Hatte genau den selben Gedankengang. Leider kenne ich mich mit Makros = 0 aus. Daher bin ich auch an meine Grenzen gestossen. Leider kommt so eine Tabelle regelmässig mit ca. 300-700 Namen und dachte daher vllt. nimmt sich wer der kleinen Aufgabe an <3 ._.

grüsse

 

[7H0M45]

Dann ist das die Gelegenheit für dich die basics von VBA zu lernen

Das ganze ist eigentlich sehr einfach, also gut als einsteigeraufgabe zu bewältigen.

Beschäftige dich mal mit dem cells befehl, der for ... to ... schleife und der do until schleife.

wenn du dann noch bisschen ahnung vom umgang mit variablen hast ist das ganze sehr einfach.

 

[whispers]

Dankee, gut zu wissen, wo anfangen ._.

Werd mich mal mit VBA-Makros auseinander setzen.

Grüsse

 

[7H0M45]

Falls du hilfe brauchst, einfach melden

Habs mal getestet, bei mir sinds 34 Zeilen code (mit zwischenkommentaren), wobei ich das immer recht großzügig mach.

Aber es ist wirklich sinnvoll das selbst zu beherrschen

 

[whispers]

Verstehe ich, finde es auch eine spannende Aufgabe. Vielleicht kann ich ihn später ja mal posten, damit einer drüber schauen kann. wie gesagt, das ist für meine Chefin und ich möchte dort kein Fehler einbauen...

Muss mich erstmal reinlesen und testen. Hatte noch nie vorher mit VBA-Makros zu tun ._.

Grüsse und danke für die Hilfe

Ergänzung (24. Februar 2016)

Darf ich fragen, welche befehle du verwendet hasst? kenn mich eben kaum aus und bräuchte einen kleinen wink in die richtige Richtung

oder reichen die drei oben erwähnten? ._.

 

[7H0M45]

Wie bereits gesagt, verwendet wird:

Der Cells Befehl (der wichtigste in Excel VBA)

die Do loop Schleife

die for to schleife

und die If Bedingung natürlich

Ansonsten verwende ich innerhalb einer Do loop schleife immer den DoEvents befehl. Der ist nicht unbedingt nötig, macht das ganze aber meiner meinung nach stabiler, da es bei großen Datenmengen sonst vorkommen kann das Windows denkt Excel wäre abgestürzt.

Den Rest darfst du dir per makro recorder raussuchen

 

[halwe]

Ich tendiere immer eher zu Lösungen zunächst ohne VBA, mit Programmcode macht man in Excel eine zweite Baustelle auf und das Ganze wird unübersichtlicher.

Ich sehe auch hier eine Variante ohne Code:
1. Du schreibst die Daten der Partner über Verweisformeln in die beiden Ehefrau-Spalten.
2. Du machst eine extra Spalte (ggf. verborgen) und schreibst dort über eine Such- oder eine Verweisformel rein, ob diese Person als Ehepartnerin auftaucht. Oder eben einfach den Namen des Partners wie unter Punkt 1.
3. Nach dieser extra Spalte kannst du nun zusammen mit der Anrede "Frau" filtern und so alle Frauen mit Ehepartner mit einem mal rauslöschen (nachdem du die Verweisergebnisse aus 1. durch ihre Werte ausgetauscht hast).

Einzig bei gleichgeschlechtlichen Partnerschaften müsstest du dann noch mal extra schauen.

Welche Variante du wählst, hängt wohl davon ab, ob du eher VBA oder eher Verweis- und Suchformeln lernen willst. Letzteres könnte etwas schneller gehen.

Viele Grüße, Halwe

 

[Janush]

Nun ja, da ja hier Zeilen geloescht werden sollen, wirds mit Formeln schon etwas schwieriger. Und ob Formeln in versteckten Spalten der Uebersichtlichkeit zutraeglich sind, kann man auch anzweifeln. Da loescht im laufe der Zeit mal jemand ne Zelle per hand und die Formeln geben irgendwas falsches aus.

 

[halwe]

Meine Anleitung ist entsprechend Aufgabenstellung zunächst für eine einmalige Umsetzung gedacht. Da stören die verborgenen temporären Spalten ebenso wenig, die die Gefahr, dass sich "über die Jahre" die Formeln verschieben könnten.

 

[whispers]

Danke Halwe für deinen Tipp. Leider werde ich die Excel-Arbeitsmappe nicht selber behalten, sondern zur Weiterbearbeitung weiterreichen. Das heisst mein Lösungsweg sollte sich bestenfalls danach in Luft auflösen, bzw. es müssen andere Personen immer noch mit der Excel-Datei Arbeiten können, ohne dass sie auf irgendwas achten müssen.

Mir geht es lediglich darum, den Arbeitsschritt (Namen Suchen, Namen Verschieben und Zeilen löschen) so zu machen, dass die Datei danach genauso ist, als hätte ich es von Hand gemacht.

Glaube da gibt es keine Formel für nehme ich mal an, ansonsten immer her damit ._.

 

[halwe]

Also: Meine Anleitung arbeitet mit Formeln, weil irgendwie müssen ja die fehlenden Namen eingetragen werden und die Filterbedingungen für das Löschen hergestellt werden. Aber wenn die Verweise per Formel da sind (also die Partnerinnen bei ihrem Mann stehen) und du die überschüssigen Ehepartnerinnen gelöscht hast, hindert dich ja niemand, (1) die zwei Hilfsspalten wieder zu löschen und (2) die Ehefrau-Verweise mit Kopieren-Einfügen durch die festen Werte zu ersetzen.

Nur wenn jetzt angenommen in der gleichen Tabellen noch 1000 Personen dazukämen (ohne eingetragenen Namen des Partners) müsstest du die Formeln erneut eintragen.

Viele Grüße, Halwe

 

[7H0M45]

Leider werde ich die Excel-Arbeitsmappe nicht selber behalten, sondern zur Weiterbearbeitung weiterreichen. Das heisst mein Lösungsweg sollte sich bestenfalls danach in Luft auflösen, bzw. es müssen andere Personen immer noch mit der Excel-Datei Arbeiten können, ohne dass sie auf irgendwas achten müssen.

Du wirst auf jedenfall eine Art Anleitung aufstellen müssen was mit der Excel Datei gemacht werden darf und was nicht. Und wenn das in der Spalte Nebendran steht. Selbst ein Makro braucht, egal wie dynamisch du es programmierst irgendwelche Fixpunkte (zB Inhalt der ersten Zeile einer Spalte). Diese Fixpunkte dürfen auf keinen Fall verändert werden. Es müssen also für alle ersichtlich irgendwelche Regeln erstellt werden!

 

[halwe]

Na ja, wenn er selbst nur die Partnerinnen einträgt und bereinigt, braucht der Nachfolger das nicht mehr zu wissen, wie es zustande gekommen ist. Egal ob Makro oder Verweisformeln, der Adressat sieht das nicht mehr, da stehen einfach die richtigen Namen drin.

Makrobehaftete Dateien würde ich aber momentan ohnehin niemandem weitergeben wollen.

Viele Grüße, Halwe

 

[Janush]

Ganz richtig und deswegen waere der beste weg ein AddIn welches auf Knopfdruck diese Excel Mappen automatisch "bereinigt". Kommt halt drauf an, wieviel manueller Aufwand da jedes mal reinfliesst und mit wievielen solcher Tabellen in Zukunft zu rechnen ist. An sowas kann man sich richtig schoen austoben, wenn es denn die Muehe wert ist

Aber jetzt mal Butter bei die Fische. Wie lange sitzt Du da jedes Mal dran wenn da bis zu 700 Namen in der Liste sind? Selbst wenn Du da Formeln reinfummelst dauert das doch ewig.

 

[whispers]

Hey Janush

Freut mich, dass auch du versuchst mir hier zu helfen. Nun aber zu deiner Frage...

Aber jetzt mal Butter bei die Fische. Wie lange sitzt Du da jedes Mal dran wenn da bis zu 700 Namen in der Liste sind? Selbst wenn Du da Formeln reinfummelst dauert das doch ewig.

Ich bekomme circa alle 2 Wochen so eine Tabelle. Arbeite auf einem Einwohneramt und daher kommen solche Anfragen häufiger. Für eine Liste benötige ich je nachdem 1-3 Std. (je nach Liste) da ich jede einzelne Position bissher von Hand überprüfen durfte. Bin jetzt circa 1 Jahr dabei und langsam wird mir das ganze zu blöd. Aufwand gegenüber Ertrag ist im 21. Jahrhundert einfach nicht zu rechtfertigen.

Brauche dabei glaube ich nicht erwähnen, dass es mehr als langweilig ist, jede Person von Hand zu überprüfen.

Wäre echt dankbar, wenn mir jemand einen Makro zeigen könnte, welches mir diese Aufgabe abnimmt. Habe mich jetzt bisschen reingelesen in die ganze Makro-Thematik. Ist aber schwer genau das zu finden was ich benötige ohne eine Vorahnung zu haben. Habe jedesmal das Gefühl ich lerne 100-Tausend dinge, welche mir aber bei meinem eigentliche Problem nicht weiterhelfen.

Soo, genug geredet. Vielleicht hat ja gerade jemand einen passenden Makro parat, welchen auch andere Mitarbeiter ohne viel Vorahnung benutzen können... ?!

Beste Grüsse
whispers

 

[7H0M45]

Private Sub CommandButton1_Click()
Dim lngY As Long
Dim lngY2 As Long
Dim lngX As Long
Dim lngLetzteZeile As Long

lngY = 2
lngX = 1

'Letzte Zeile finden
Do Until Cells(lngY, lngX) = ""
  DoEvents 'Damit er sich nicht aufhängt.
  lngY = lngY + 1
Loop
lngLetzteZeile = lngY - 1

'Alle Zeilen durchlaufen und prüfen ob eine Referenz vorhanden
lngY = 2
Do Until lngY = lngLetzteZeile
  DoEvents
  If Cells(lngY, 2) <> "" Then
    'Es ist eine Referenz eingetragen, deshalb durchsuchen ob es den namen gibt
    For lngY2 = 2 To lngLetzteZeile
      If Cells(lngY2, 1) = Cells(lngY, 2) Then
        'Referenznummern stimmen überein, also namen in Zeile lngY kopieren
        Cells(lngY, 5) = Cells(lngY2, 3)
        Cells(lngY, 6) = Cells(lngY2, 4)
        'Zellen sind kopiert, also die Zeile mit der Ehefrau löschen
        Rows(lngY2).Delete
        'Da eine Zeile gelöscht wurde muss der Zähler zurückgestellt werden
        If lngY >= lngY2 Then lngY = lngY - 1 'Es wurde eine Zeile unter lngY gelöscht also muss er eins zurückrücken
        lngLetzteZeile = lngLetzteZeile - 1 'Die letzte zeile ist jetzt weiter oben
        Exit For 'For schleife beenden
      End If
    Next lngY2
  End If
  lngY = lngY + 1
Loop
End Sub

Das ist meine version. ist sicher noch verbesserungswürdig, aber dafür mit geringem zeitlichen einsatz

Wichtig an dieser stelle wäre:
Ist das immer eine neue Excel Datei oder wird die liste in der Alten datei erweitert?

 

[whispers]

Private Sub CommandButton1_Click()
Dim lngY As Long
Dim lngY2 As Long
Dim lngX As Long
Dim lngLetzteZeile As Long

lngY = 2
lngX = 1

'Letzte Zeile finden
Do Until Cells(lngY, lngX) = ""
  DoEvents 'Damit er sich nicht aufhängt.
  lngY = lngY + 1
Loop
lngLetzteZeile = lngY - 1

'Alle Zeilen durchlaufen und prüfen ob eine Referenz vorhanden
lngY = 2
Do Until lngY = lngLetzteZeile
  DoEvents
  If Cells(lngY, 2) <> "" Then
    'Es ist eine Referenz eingetragen, deshalb durchsuchen ob es den namen gibt
    For lngY2 = 2 To lngLetzteZeile
      If Cells(lngY2, 1) = Cells(lngY, 2) Then
        'Referenznummern stimmen überein, also namen in Zeile lngY kopieren
        Cells(lngY, 5) = Cells(lngY2, 3)
        Cells(lngY, 6) = Cells(lngY2, 4)
        'Zellen sind kopiert, also die Zeile mit der Ehefrau löschen
        Rows(lngY2).Delete
        'Da eine Zeile gelöscht wurde muss der Zähler zurückgestellt werden
        If lngY >= lngY2 Then lngY = lngY - 1 'Es wurde eine Zeile unter lngY gelöscht also muss er eins zurückrücken
        lngLetzteZeile = lngLetzteZeile - 1 'Die letzte zeile ist jetzt weiter oben
        Exit For 'For schleife beenden
      End If
    Next lngY2
  End If
  lngY = lngY + 1
Loop
End Sub

Das ist meine version. ist sicher noch verbesserungswürdig, aber dafür mit geringem zeitlichen einsatz

Wichtig an dieser stelle wäre:
Ist das immer eine neue Excel Datei oder wird die liste in der Alten datei erweitert?

WOW. Danke <3

Es ist immer eine neue Datei. Mal schauen obs so funktioniert wie ichs mir vorstelle

Dein Code ist fast perfekt. es fehlt noch, dass immer der Mann auf der Linken Seite steht und die Frau rechts, egal wer zuerst in der Liste kommt.

 

[halwe]

Hallo,

ja, wenn das eine häufigere Tabelle ist, dann liefe auch die Verweisvariante etwas anders.
Ich habe bei einer ähnlichen Aufgabenstellung die Tabelle dupliziert, die auszuwertenden Spalten (bei dir A-D und G) aus der (auswechselbaren) Quelltabelle gezogen (kopieren, Verknüpfung einfügen) und mit den Verweisformeln dann die neuen Werte (in deinem Fall Name/Vorname Ehefrau + Zusatzspalte Ehepartner, dem die Frau zugeordnet ist) eingetragen. Auch den Filter könnte man schon vordefinieren.
Der einmalige Aufwand je neue Tabelle reduziert sich dann auf 1. Auswertetabelle aufrufen, 2. Ergebnis kopieren und in neue Tabelle (damit die Formeln raus sind).
Du könntest in diesem Fall sogar deinen Kollegen direkt die Auswertetabelle bereitstellen, so dass sie die bereinigten Daten ohne deine Hilfe jederzeit abrufen können und das Ganze an dir vorbeigeht, wenn nur die Quelltabelle am richtigen Ort mit richtigem Namen vorliegt.

Allerdings, wenn du wirklich Ambitionen zur Makroentwicklung hast und dir die Jungs hier so gut helfen, dann sollte das tatsächlich Vorrang haben, denn damit kannst du künftig noch ein paar Sachen mehr machen, als mit Formeln .

Viele Grüße, Halwe