Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1484to1488
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

Adressverweise ohne Leerzeilen

Adressverweise ohne Leerzeilen
27.03.2016 15:09:45
Michael
Hallo Zusammen
Ich habe folgendes Problem:
Für ein Adminsheet auf Google Docs, welches mit einem Adressverzeichnis aufgebaut ist, das für die Verwaltung von Mitgliedern eines Vereins und zur Anschriftengenerierung als Alternative zur Abschreibübung dient, suche ich nach einer Formellösung.
Die Anschriften sind sehr sporadisch und einzeln, daher macht ein Serienbrief kaum sinn, da viel zu umständlich. Ich habe im Sheet eine Lasche mit einer Briefvorlage, bei welcher Briefkopf und Adresszeilen vorhanden sind. Die Einzelnen Anschriftelemente hole ich mittels SVERWEIS, welcher an eine Adressnummer gekoppelt ist, die auf der Vorlage eingegeben werden kann, aus dem Adressverzeichnis. Nun gibt es Firmen als Adresseinträge, welche einen Firmenname besitzen und eine Kontaktperson, genauso aber gibt es Privatpersonen. Zudem gibt es Anschriften, welche mehrere Adresszeilen besitzen. Das Problem hierbei ist, dass bei dem SVERWEIS, durch die teilweise leeren Zellen im Adressverzeichnis auch leere Felder in der Anschrift entstehen.
Nun bin ich auf der Suche nach einer Formel, welche mittels des SVERWEISES die richtige Adresszeile aus dem Adressverzeichnis heraussucht und die Werte, welche in 10 Spalten nebeneinander stehen, untereinander aufreihen und die leeren Zellen auslässt. Zudem gibt es Anordnungen wie Vor- und Nachname, welche im Adressfeld nebeneinander kommen sollen.
Gibt es dazu eine Lösung? Help is warm welcome :)
Beispielfile (sehr reduziert) ist hier: https://docs.google.com/spreadsheets/d/1olrKzuZzlPniyOxGCWC-mewrq7KsJEdpqT8eUdrRFsY/edit?usp=sharing

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Datei kannst Du hier im Forum hochladen ...
27.03.2016 15:56:55
...
Hallo Michael,
... und warum soll die Excelversion ohne Relevanz sein?
Unabhängig davon ist es mir so, als ob ich Deine Aufgabe schon irgendwo vor kurzem wo anders gelesen hätte. Wenn dem so sein sollte, solltest Du zumindest Linkverweis(e) auf die anderen Foren einstellen.
Gruß Werner
.. , - ...

AW: Datei kannst Du hier im Forum hochladen ...
27.03.2016 16:45:17
Michael
Hallo Werner
Ja, die selbe Frage habe ich auf einem anderen Forum (http://www.office-loesung.de/p/viewtopic.php?f=166&t=715438&sid=319f8eb401c13b9f4fd6afa2dd439245) bereits veröffentlicht, leider ohne Antwort.
Ich habe das Testfile als Google Sheet gespeichert, da das normale File auch auf Google Docs liegt. Die Irrelevanz der Excel-Version schien mir daher als sinnvoll.
Würde mich über eine Lösung des Problems freuen.
LG
Michael

Anzeige
AW: vielleicht ...
27.03.2016 16:54:26
...
Hallo Michael,
... ich will mich aber nicht bei google anmelden, also müsstest Du schon Deine Datei hier hochladen, damit ich es mir anschauen kann.
Gruß Werner
.. , - ...

AW: eine Möglichkeit ...
27.03.2016 18:36:31
...
Hallo Michael,
... ganz eindeutig ist mir allerdings Dein Anliegen noch nicht.
Ich interpretiere es mal so: Wenn Du in Deinem Adressen-Tabellenblatt in Hilfsspalten die wesentlichsten Verkettungen bereits vornimmst und danach in Deine "Briefvorlage" mittels einer einfachen VERWEIS() die relevanten Daten aus den Hilfsspalten ziehst.
Als Beispiel folgende Hilfsspaltenformel in Adressen!N2: =GLÄTTEN(D2&" "&E2&" "&F2) und diese nach unten kopieren. Dann reicht in Brief!B7: =VERWEIS($K$16;Adressen!A:A;Adressen!N:N)
Analog gehst dann für die anderen Daten vor.
Gruß Werner
.. , - ...

Anzeige
AW: eine Möglichkeit ...
27.03.2016 19:33:46
Michael
Hallo Werner,
danke für deinen Input. Mein Problem dabei ist, dass es leere Zeilen gibt die nicht übertragen werden sollen, so wie es beim klassischen Serienbrief dynamisch funktionert.
Beispiel:.............Firma...............................Privat
Adresszeile 1.....IT Contact GmbH................Andreas Müller
Adresszeile 2.....Herr Meier......................... Musterstrasse 120
Adresszeile 3.....Rennweg 2.........................DE - 104555 Berlin
Adresszeile 4.....DE - 10233 Berlin................
...
Die Hilfsspalte ist sicher keine schlechte Idee um dann die Adressteile, welche auf einer Zeile erscheinen sollen vorgesammelt werden.
Liebe Grüsse
Michael

Anzeige
AW: dann nutze zusätzlich WENN() ...
28.03.2016 11:18:29
...
Hallo Michael,
... in der 1. Hilfsspalte ergänze die von mir gestern angegebene Formel z.B. Zu:
=WENN((B2="")+(GLÄTTEN(B2)="");GLÄTTEN(D2&" "&E2&" "&F2);B2).
Die anderen Hilfsspaltenformeln für die weiteren Adresszeilen analog konstruieren.
Damit kannst Du einerseits zwischen privat und Firma unterscheiden und andererseits wird die Hilfsspaltenformel entweder ein Text oder ein "" erzeugt, welches dann in Deinem "Brief"-Tabellenblatt dadurch nicht auf 0 geprüft werden muss.
Gruß Werner
.. , - ...

AW: dann nutze zusätzlich WENN() ...
29.03.2016 09:49:20
Michael
Hallo Werner,
merci für deine Antwort. Mit dieser Lösung wird die Komplexität auf sieben möglichen Adresszeilen sehr gross, da die Verschiebung der Bezugszelle je nach Adresse von der letzten (7. Zeile) auf die 3. Zeile sein kann.
Gibt es eine Variante der Formel für Spalten ohne leere Zellen auflisten {=WENN(ZEILE(A1)>ANZAHL2(A:A);"";INDEX(A:A;KKLEINSTE(WENN(A$1:A$1000<>"";ZEILE($1:$1000));ZEILE(A1))))} auch für Zellen, welche nebeneinander liegen? Ich habe ein Beispiel-File hochgeladen, wie ich mir das vorstelle als Resultat. https://www.herber.de/bbs/user/104640.xlsx
Lieber Gruss
Michael

Anzeige
AW: nachgefragt ...
29.03.2016 10:22:31
...
Hallo Michael,
... Du schreibst einerseits von Zeilen andererseits zeigst Du in Deiner neuen Datei eine reine Spalten orientierte Lösung auf, aus der ich interpretiere: Du möchtest die grün markierten Zellen mittels Formel aus den Daten in Spalte B:N ermitteln? Oder?
Grundsätzlich könnte da schon eine INDEX()-KKLEINSTE()- Matrixformel zum Einsatz gelangen, nur eben hier etwas anders konstruiert. Doch bevor ich dies vornehme, beantworte bitte oben stehenden Fragen und gut zu wissen wäre es aber auch, ob Dein Kalkulationsprogramm die Funktionen AGGREGAT() und WENNFEHLER() kennt?
Gruß Werner
.. , - ...

Anzeige
AW: nachgefragt ...
29.03.2016 11:34:57
Michael
Hallo Werner,
genau so ist es. Es sollen alle eingetragenen Werte in die grünen Felder übertragen werden und die leeren Zellen überspringen.
Google Sheets kennt die AGGREGAT-Funktion nicht, jedoch den WENNFEHLER.
Ich freue mich auf deine Lösung.
Beste Grüsse
Michael

AW: dann z.B. mit folgenden Formeln ...
29.03.2016 12:29:21
...
Hallo Michael,
... mit einer zusätzlich Umstellung der Hilfsspalte "Ort gesammelt" in Spalte K folgende 3 Formeln nach unten und Formel Q2 zusätzlich nach rechts kopieren:
 ABCDEFGHIJKLMNOPQRST
1NrFirmaZusatzAnschriftVornameNameName gesammeltStrasse + NummerStrasse 2Strasse 3Ort gesammeltPLZOrtLandAnschrift 1Anschrift 2Anschrift 3Anschrift 4Anschrift 5Anschrift 6
21000RedBullBuchhaltungHerr SchwarzHerr SchwarzHerrenweg 2  DE - 10470 Berlin10470BerlinDERedBullBuchhaltungHerr SchwarzHerrenweg 2DE - 10470 Berlin 
31001RedBull FrauSabineWeissFrau Sabine WeissHerrenweg 23. StockBüro 4DE - 10470 Berlin10470BerlinDERedBullFrau Sabine WeissHerrenweg 23. StockBüro 4DE - 10470 Berlin
41001  FrauAnnaMüllerFrau Anna MüllerWeinstrasse 120  DE - 10840 Berlin10840BerlinDEFrau Anna MüllerWeinstrasse 120DE - 10840 Berlin   

Formeln der Tabelle
ZelleFormel
O2=WENN(B2="";G2;B2)&""
P2=WENN((B2="")*(C2=""); H2;WENN(C2>0;C2;G2))&""
Q2{=WENNFEHLER(INDEX(2:2;KKLEINSTE(WENN($G2:$K2<>"";SPALTE($G2:$K2)); SPALTE(A2)+VERGLEICH(VERGLEICH($P2;2:2;); {3;7;8};)-1))&"";"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: funktioniert! eine kleine änderung ...
30.03.2016 08:27:31
Michael
Hallo Werner,
Formel funktioniert! auch wenn ich sie nicht interpretieren kann :). An dieser Stelle bereits ein grosses Dankeschön. Eine kleine Anpassung braucht es noch, damit die Darstellung korrekt ist. Zwischen Spalte G und H braucht es noch eine weitere Spalte für c/o Adressergänzungen. Ich habe diese Spalte selber in meinem Google Sheet eingefügt, was die Funktion der Formel eliminierte.
Kannst du mir sagen, was an der Formel editiert werden muss, um die weitere Spalte mit ein zu beziehen?
Lieber Gruss
Michael

AW: das ist dann keine kleine Änderung ...
30.03.2016 08:35:28
...
Hallo Michael,
... stelle eine neue Beispieldatei ein, aus der ersichtlich wird, wozu genau die Daten der neuen Spalte benötigt werden bzw. wie entsprechende Eingaben dort sich auf das gewünschte Ergebnis auswirken sollen.
Gruß Werner
.. , - ...

Anzeige
AW: das ist dann keine kleine Änderung ...
30.03.2016 09:51:04
Michael
Hallo Werner,
das mit dem Upload auf dem Forum hat leider nicht geklappt von meinem Rechner. Daher hier den Link: http://we.tl/GKfwuxt4W1
Ich habe von meinem Google Sheet einen Auszug gemacht, daher sind noch Links im File, welche irrelevant sind. Der ganze Bezug der Adresszeilen wiederholt sich bei AM:BH nochmals. Dies aus der Möglichkeit, dass Liefer- und Rechnungsadresse unterschiedlich sind.
Ich freue mich auf deine Lösung.
Beste Grüsse
Michael

AW: zur Dateibereitstellung ...
30.03.2016 12:45:06
...
Hallo Michael,
... am 27.03. und 29.03. hast Du doch auch eine Beispieldatei hochladen können. Ich melde mich ungern woanders an um Zugriff auf Deine neue Datei zu haben. Lösche auch vorher möglichst alle Bezüge. Es wäre mir ausreichend unter Berücksichtigung der evtl. neuen Daten in der neuen Spalte Deine Zielstellung als Ergebniswerte zu sehen.
Gruß Werner
.. , - ...

Anzeige
AW: zur Dateibereitstellung ...
30.03.2016 13:33:05
Michael
Hallo Werner,
Ich habe von zu Hause vom Mac aus gearbeitet, da ging es irgendwie nicht. Anbei den Link: https://www.herber.de/bbs/user/104659.xlsx
Liebe Grüsse
Michael

AW: gewünschte Formelanpassung ...
30.03.2016 14:20:16
...
Hallo Michael,
... Formel P2und Q2 nach unten und R2 zusätzlich nach rechts ziehend kopieren:
 PQR
2Frau Monica Scottc/o Andreas HettmannGörresstrasse 43

Formeln der Tabelle
ZelleFormel
P2=WENN(B2="";G2;B2)&""
Q2=WENN((B2="")*(C2=""); WENN(H2>0;H2;I2); WENN(C2>0;C2;G2))&""
R2{=WENNFEHLER(INDEX(2:2;KKLEINSTE(WENN($G2:$L2<>"";SPALTE($G2:$L2)); SPALTE(A2)+VERGLEICH(VERGLEICH($Q2;2:2;); {3;7;8;9};)-1-($I2=$Q2)))&"";"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: gewünschte Formelanpassung ...
30.03.2016 14:50:50
Michael
Hallo Walter
Scheint einwandfrei zu funktionieren. Danke dir.
Im letzen File gibt es noch die zweite Datenreihe, welche sich über AM:BH zieht und die selbe Funktion aufweisen soll. Der Versuch die Formel auf die gleichen Bezugszellen wie vorne abzuändern hat nicht funktioniert. Wie muss da die [R2] Formel angepasst werden?
Lieber Gruss
Michael

AW: die zweite "Datenreihe" ...
30.03.2016 15:44:41
...
Hallo Michael,
... aber zunächst, wie kommst Du auf einmal darauf, mich als Walter anzuschreiben, bin immer noch der Gleiche.
Um die 2. Datenreihe analog auszuwerten aber auch die beiden neuen Datenkombinationen (siehe AR5 und AR10 gegenüber G5 und G10) richtig auszuwerten, ist es notwendig in AR2 folgende Formel nach unten zu kopieren. =GLÄTTEN(AO2&" "&AP2&" "&AQ2)&WENN(ANZAHL2(AO2:AQ2)=0;"";WENN(ANZAHL2(AO2:AQ2)
 BABBBC
2Frau Monica Scottc/o Andreas HettmannGörresstrasse 43

Formeln der Tabelle
ZelleFormel
BA2=WENN(AM2="";AR2;AM2)&""
BB2=WENN((AM2="")*(AN2=""); WENN(AS2<>"";AS2;AT2); WENN(AN2<>"";AN2;AR2))&""
BC2{=WENNFEHLER(INDEX(2:2;KKLEINSTE(WENN($AR2:$AW2<>"";SPALTE($AR2:$AW2)); SPALTE(A2)+VERGLEICH(VERGLEICH($BB2;$AL2:BB2;); {3;7;8;9};)-1-($AT2=$BB2)))&"";"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Auch ergibt sich eine Änderung in BB2 gegenüber Q2

AW: die zweite "Datenreihe" ...
30.03.2016 16:03:19
Michael
Hallo Werner,
perfekt, das funktioniert wunderbar!
Ich bin dir sehr dankbar, jetzt kann ich das ganze in Google Sheets noch nachtragen.
Beste Grüsse
Michael

AW: na, das freut mich nun auch ...
30.03.2016 16:12:27
...
Hallo Michael,
... vergiss nicht die Anpassungen der Formeln in Spalte G und Q für Deine erste Datenreihe.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige