Excel - zwei Probleme

Computer: Software & Programmierung Office Software
1

Hallo,

ich verwalte die Mitgliederdatei eines bundesweit tätigen Verbandes für mein Bundesland. Hierzu bekomme ich allmonatlich eine Excel Liste mit rund 1.000 Mitgliedern, die ich an die Regionalgruppen weiterleiten muss.

Problem 1:

Ausgetretene Mitglieder werden weiter mitgeführt, man erkennt sie daran, dass am Ende der jeweiligen Datenzeile in der Spalte mit der Überschrift „Ausgetreten am“ ein Datum befindet. Bevor ich die Liste weiterleite, muss ich die ausgetretenen aber entfernen. Das mache ich bisher manuell.

Kann man das automatisieren? Also eine Routine einbauen, die folgendermaßen aussieht:

Gehe zu Zeile 1. Wenn V1 anders als „“, dann lösche Zeile 1
Gehe zu Zeile 2. Wenn V2 anders als „“, dann lösche Zeile 2
Gehe zu …

Problem 2:

Ich muss die Daten, wie schon erwähnt, an Regionalgruppen weiterleiten. Groteskerweise sind die nicht ihn der Datei erfasst, ich muss sie allmonatlich wieder neu ermitteln.

Kann man auch das automatisieren? Mit einer Routine einbauen, die etwa so aussieht:

Gehe zu Zeile 1. Wenn F1 = 12345, dann kopiere Zeile 1 in Region A
Wenn F1 = 67890, dann kopiere Zeile 1 in Region B
Wenn F1 = 98765, dann kopiere Zeile 1 in Region BC
Gehe zu Zeile 2. Wenn …

Wobei mit „Region …“ eine andere Datei (die schon angelegt ist) sein kann oder auch nur ein neuer Absatz auf der Seite. Wobei ersteres natürlich besser wäre, weil ich dann die Dateien vor Weiterleitung nicht mehr aufteilen muss.

Allerdings: Es geht um rund 900 Postleitzahlen und 42 Regionen. :smile:

Machbar ist das sicher. Aber geht das für einen nicht unerfahrenen Excel-Nutzer, der aber kein ausgebildeter Programmierer ist?

Das zweite Problem ist wohl das härtere?!

In gespannter Erwartung,
und mit Dank im Voraus für jede brauchbare Hilfe,

Viele Grüße
Carsten

2

Hallo

Problem 1:

Ausgetretene Mitglieder werden weiter mitgeführt, man erkennt
sie daran, dass am Ende der jeweiligen Datenzeile in der
Spalte mit der Überschrift „Ausgetreten am“ ein Datum
befindet. Bevor ich die Liste weiterleite, muss ich die
ausgetretenen aber entfernen. Das mache ich bisher manuell.

Kann man das automatisieren? Also eine Routine einbauen, die
folgendermaßen aussieht:

Gehe zu Zeile 1. Wenn V1 anders als „“, dann lösche Zeile 1
Gehe zu Zeile 2. Wenn V2 anders als „“, dann lösche Zeile 2
Gehe zu …

Wenn du das als Access-Tabelle hättest, könntest du die ganz einfach rausfiltern. Du könntest die Daten in Excel auch kopieren und in eine Access-Tabelle einfügen. Da könntest du die ausgetretenen Mitglieder blitzartig rausfiltern.

Du müsstest nur den Cursor unter „abgemeldet am“ in ein gewünschtes - also leeres - Feld setzen und auf das Symbol mit dem Blitz-Filter klicken. Dann wären alle Mitglieder, die dort kein leeres Feld haben, rausgefiltert. Und wenn du auf das Filtersymbol klickst, sind alle wieder da.

Problem 2:

Ich muss die Daten, wie schon erwähnt, an Regionalgruppen
weiterleiten. Groteskerweise sind die nicht ihn der Datei
erfasst, ich muss sie allmonatlich wieder neu ermitteln.

Woher weißt du denn, welches Mitglied in welche Region gehört? Gehören bestimmte PLZ zu bestimmten Regionen, oder wie läuft das?

Und wieso musst du sie allmonatlich alle wieder neu ermitteln? Wechseln da so schnell die Mitglieder bei eurem Verein?

Kann man auch das automatisieren? Mit einer Routine einbauen,
die etwa so aussieht:

Gehe zu Zeile 1. Wenn F1 = 12345, dann kopiere Zeile 1 in
Region A
Wenn F1 = 67890, dann kopiere Zeile 1 in Region B
Wenn F1 = 98765, dann kopiere Zeile 1 in Region BC
Gehe zu Zeile 2. Wenn …

In F1 steht die Postleitzahl, oder?

Allerdings: Es geht um rund 900 Postleitzahlen und 42
Regionen. :smile:

Das ist mit Access auch kein Problem. Da müsstest du nur ein für allemal eine Tabelle herstellen, die die Postleitzahlen den jeweiligen Regionen zuordnet, danach hast du da nicht mehr viel Arbeit mit.

Bevor ich hier aber großartig was zu schreibe, sag bitte erst mal, ob Access für dich eine Möglichkeit darstellt. Das kann man übrigens hinterher auch wieder nach Excel umwandeln, wenn es unbedingt Excel sein muss. Das geht sogar ganz prima.

Viele Grüße
Thea

3

Hallo Carsten,

unsere Excel-Spezialisten Franz und Reinhard (und …) wissen sicher noch elegantere Lösungen. Aber bei einmal monatlich würde ich einfach die Datei nach der Spalte mit dem Austrittsdatum sortieren und dann die Ausgetretenen als Block löschen.

Zu Problem 2:
Hierfür würde ich SVERWEIS nehmen. Neue Spalte in die Ursprungsdatei, =Sverweis(„Feld der PLZ“;„Datei mit Aufteilung PLZ in Region“;„Anzahl Spalten zwischen PLZ und Region“;0)

Und dann das ganze nach der neuen Spalte sortieren oder „Auto-Filtern“. Evtl. noch eine neue Datei anlegen mit 42 Tabellenblättern und „Wenn(Ursprungsdatei-Region=1;A1;“") usw.

Vielleicht hilft dir hier auch der folgende Artikel weiter: /t/programmierung-in-excel/3366804

Viel Erfolg

Karin

4

Hallo Karin,

Aber bei einmal monatlich würde ich einfach die Datei nach der Spalte mit dem
Austrittsdatum sortieren und dann die Ausgetretenen als Block
löschen.

das ist typisch so eine Antwort, wo man sich vor den Kopf schlägt und sagt: Dass ich da nicht selbst drauf gekommen bin! So einfach! Danke!

Zu Problem 2:
Hierfür würde ich SVERWEIS nehmen. Neue Spalte in die
Ursprungsdatei, =Sverweis(„Feld der PLZ“;„Datei mit Aufteilung
PLZ in Region“;„Anzahl Spalten zwischen PLZ und Region“;0)

Und dann das ganze nach der neuen Spalte sortieren oder
„Auto-Filtern“. Evtl. noch eine neue Datei anlegen mit 42
Tabellenblättern und „Wenn(Ursprungsdatei-Region=1;A1;“") usw.

Das habe ich noch nicht begriffen.

Vielleicht hilft dir hier auch der folgende Artikel weiter:
/t/programmierung-in-excel/3366804

Nein. Welcher der Beiträge könnte denn hilfreich sein?

Grüße
Carsten

5

Hi Carsten,

das ist typisch so eine Antwort, wo man sich vor den Kopf
schlägt und sagt: Dass ich da nicht selbst drauf gekommen bin!
So einfach! Danke!

*ggg* ich gebe gerne Männern kleine Klapser auf den Hinterkopf *ggg*

Zu Problem 2:

Ich nahm deiner Frage nach an, dass du eine 2. Datei hast, in der die PLZ den Regionen zugeordnet sind, z.B. Datei „Regionen.xls“
PLZ Region
12345 1
13678 1
14589 2
14590 2
34567 4
usw.

In der monatlichen Datei ist die PLZ z.B. in Spalte 5

Dann machst du in der Hilfsspalte in der monatlichen Datei den Befehl =sverweis(A5;Regionen.xls!A:B;1;0). Falls #NV vorkommt, ist eine PLZ nicht angelegt.

Und dann das ganze nach der neuen Spalte sortieren oder
„Auto-Filtern“.

Evtl. noch eine neue Datei anlegen mit 42
Tabellenblättern (für jede Region) und „Wenn(Ursprungsdatei-Region=1;A1;“") usw.

Das ist jetzt von mir sicher keine elegante Lösung.
Z.B. die Region steht jetzt in Spalte 10, dann würde ich im Tabellenblatt „Region1“ in Zelle A1 schreiben =wenn(Ursprungsdatei!A$10=1;A1;"") und die Formel nach rechts und nach unten kopieren. Und dann für jede Region das A$10=x anpassen. Dann hast du in allen neuen Tabellenblättern die Daten der jeweiligen Region, allerdings mit Leerzeilen dazwischen (eben, wo die Region nicht übereinstimmt). Kann aber ja auch sortiert werden.

Aber bitte jetzt nicht die „Regionen-Datei“ jeden Monat neu anlegen bzw. ändern, sondern der „Ursprungsdatei“ immer den gleichen Namen geben :wink:

/t/programmierung-in-excel/3366804

In dem Artikel wird ja nach dem Namen „Wiese“ gesucht und dann die Zeile in ein neues Blatt kopiert. Statt „Wiese“ wären es bei dir eben die Regionen. Mit ein bisschen Spielerei könntest du es vielleicht für dich anpassen. Allerdings kenne ich mich mit VBA nicht aus.

Ich hoffe, ich habe mich jetzt verständlicher ausgedrückt.

Liebe Grüße

Karin

6

unsere Excel-Spezialisten Franz und Reinhard (und …) wissen
sicher noch elegantere Lösungen. Aber bei einmal monatlich
würde ich einfach die Datei nach der Spalte mit dem
Austrittsdatum sortieren und dann die Ausgetretenen als Block
löschen.

Danke dir Karin,
Franz, die unds und ich freuen sich über die Erwähnung als Spezialisten:smile:
Dummerweise mag ich Bayern München überhaupt nicht *würg*, mußte denen aber heute die Daumen drücken damit meine Eintracht Frankfurt bei Uefa mitspielt:frowning:
Um diese Seelenwanderung zu verdauen oder zu mindern musste ich mich alkoholisch betäuben, deshalb fühle ich mich grad Kopfmäßig außerstande das Makro zu basteln, was da Zeilen löscht und dann an die 42? Regionen per Outlook die Tabellen verschickt.
Gruß
Reinhard

7

Hallo Reinhard,

ich gelte ja bei uns im Betrieb als die Office-Spezialistin (aber auch nur, weil ich hier immer so kompetente Antworten auf meine Hilfeschreie bekomme *g*) … aber bei deinem und Franz’ Wissen und Hilfsbereitschaft werde ich immer xxx (sprachlos, neidisch, blass, ehrfürchtig, klein, … konnte mich nicht auf das richtige Wort festlegen).

Dummerweise mag ich Bayern München überhaupt nicht *würg*,
mußte denen aber heute die Daumen drücken damit meine
Eintracht Frankfurt bei Uefa mitspielt:frowning:

Mitteltiefes Beileid … kannst ja sehen, wo ich herkomme *g*

Um diese Seelenwanderung zu verdauen oder zu mindern musste
ich mich alkoholisch betäuben, deshalb fühle ich mich grad
Kopfmäßig außerstande das Makro zu basteln, was da Zeilen
löscht und dann an die 42? Regionen per Outlook die Tabellen
verschickt.

… dazu wäre ich nicht einmal mit glasklarem Verstand ansatzweise in der Lage. Aber ich freue mich auf deine Lösung (auch wenn mein Vorschlag dann als steinzeitlich herauskommt :wink:)

Liebe Grüße

Karin

8

zu 1

Ausgetretene Mitglieder werden weiter mitgeführt, man erkennt
sie daran, dass am Ende der jeweiligen Datenzeile in der
Spalte mit der Überschrift „Ausgetreten am“ ein Datum
befindet. Bevor ich die Liste weiterleite, muss ich die
ausgetretenen aber entfernen. Das mache ich bisher manuell.
Kann man das automatisieren? Also eine Routine einbauen, die
folgendermaßen aussieht:
Gehe zu Zeile 1. Wenn V1 anders als „“, dann lösche Zeile 1
Gehe zu Zeile 2. Wenn V2 anders als „“, dann lösche Zeile 2

Hi Carsten,
Alt+F11, Einfügen—Modul, Code reinkopieren, ggfs anpassen, Editor schliessen.

sub entfernen()
dim zei as long
for zei = range("A65536").end(xlup).row to 1 step -1 'ggfs A anpassen
 if range("V" & zei) "" then rows(zei).delete
next zei
end sub

Gruß
Reinhard