Excel Tabelle mit Adressdaten vervollständigen?
Hallo, ich muß eine Excel Tabelle mit Daten aus dem Internet vervollständigen und suche die schnellste Methode. In der Excel Tabelle sind mehrere Spalten von rechts nach links mit: Bezeichnung, Name, Vorname, Doktortitel, Frau bzw. Herrn, Straße, PLZ, Ort, E-Mail Adresse und Telefonnummer. Die Tabelle ist schon vorausgefüllt mit Ärzten und sortiert nach Postleitzahlen. Nun habe ich eine Internetseite mit Adressen aufgelistet, die ich mit der Tabelle abgleichen muss, sprich ich suche als erstes nach der PLZ und schaue ob der Name des Arztes in der Excel Tabelle bereits aufgeführt ist. Wenn ja ist alles okay, wenn nicht füge ich eine Zeile ein (sortiert nach PLZ) und kopiere die einzelnen Daten aus dem Internet in die Tabelle. Das ist sehr aufwendig und zeitraubend, weil ich alles einzeln kopieren muss um die Spalten zu befüllen. Hat jemeand eine Idee wie man das beschleunigen kann? Bin über jeden Versuch dankbar...Ach ja, die Tabelle ist sehr lang, sortiert von PLZ 0 bis 9.
4 Antworten
Neue Antwort:
ist zwar keine vollständige Automatisierung, aber hoffentlich doch eine Erleichterung bei 1500 Einträgen!
- Gehe in Deine vorhandene Liste (bzw erst mal eine Kopie davon)
- OBEN 27 NEUE ZEILEN EINFÜGEN
- Zeile 28 am Zeilenkopf markieren (ganze Zeile auswählen), Menü Ansicht-Fenster einfrieren-Fenster einfrieren (=>Linie oberhalb)
- Kopiere aus dem Inet den Datenblock eines Namens.
- Füge ihn in A1 ein: Es sollte jetzt A1:A26 ausgefüllt sein, aber Deine Liste fängt in Z. 28 an (ob mit oder ohne Überschriften, ist egal, ich nehme aber an, mit)
- Füge ihn in A1 ein: Es sollte jetzt A1:A26 ausgefüllt sein, aber Deine Liste fängt in Z. 28 an (ob mit oder ohne Überschriften, ist egal, ich nehme aber an, mit.
- Stehen in den Einträgen A1:A26 immer Bezeichnungen davor? Wie z.B.E-Mail:(Leerz.) oder Name:(Leerz.)? (siehe Kommentar später im Makro)
- offne/wechsle nun mit Alt,F11 den VBA-Editor
- Dort Menü: Einfügen, Modul. Kontrolliere, ob in der obersten Zeile Option Explicit steht, wenn nicht, schreibst Du das rein (aus Prinzip, auch wenn nicht nötig!). .
- eine oder mehrere Zeile(n) unterhalb (Du kommst mir Enter dahin) fügst du den folgenden Code ein (hier kopieren)
´Sub DatenBlock_zu_Liste()
ActiveSheet.Range("A9999").End(xlUp).Offset(1, 0) = ActiveSheet.Range("A1").Value 'anstatt A1 die Zelladresse, wo Region steht
'wenn da Region: davorsteht, leicht verändert ("Region: " hat incl. Leerzeichen 8 Zeichen, also Text ab 9 Zeichen nehmen:
''ActiveSheet.Range("A9999").End(xlUp).Offset(1, 0) = Mid(ActiveSheet.Range("A1").Value, 9, 99) 'vor die nicht benötigte Zeile win Hochkomma setztn (nennt sich auskommentieren)
ActiveSheet.Range("A9999").End(xlUp).Offset(0, 1) = Mid(ActiveSheet.Range("A2").Value, 11, 99) '11 sei der Beginn des tatsächlichen Eintrags
'Beachte: erste Setzung nach Offset(1, 0), da in der Zeile noch nichts steht. Ab dann .offset(0, Spalten)
ActiveSheet.Range("A9999").End(xlUp).Offset(0, 2) = Mid(ActiveSheet.Range("A3").Value, 11, 99) 'immer individuellen Beginn einsetzen!
ActiveSheet.Range("A9999").End(xlUp).Offset(0, 3) = Mid(ActiveSheet.Range("A4").Value, 11, 99)
'....musst Du selbst ausfüllen
ActiveSheet.Range("A9999").End(xlUp).Offset(0, 9) = Mid(ActiveSheet.Range("A10").Value, 11, 99) 'dann kommt die Lücke, also next:
ActiveSheet.Range("A9999").End(xlUp).Offset(0, 14) = Mid(ActiveSheet.Range("A11").Value, 11, 99)
ActiveSheet.Range("A9999").End(xlUp).Offset(0, 15) = Mid(ActiveSheet.Range("A12").Value, 11, 99)
'.... musst Du selbst ausfüllen bis
ActiveSheet.Range("A9999").End(xlUp).Offset(0, 25) = Mid(ActiveSheet.Range("A26").Value, 11, 99)
End Sub
- Der Code wird im VBA-Editor besser lesbar (Umbrüche erscheinen an richtiger Stelle)
- erstelle ein Textfeld (Menü einfügen, Textfeld, dieses aufziehen sogroß wie nötig) und schreibe da rein:- Makro Datenblock_zu_Liste starten -formatiere es nach Gutdünken
- In dessen Kontext-Menü wählst Du Makro zuweisen im Dialogfeld wählst du das wahrscheinlich einzige Makro aus , dann ok.
- Stelle das untere Fenster so ein, dass Du den nach A1:A26 kopierten Eintrag zum Überprüfen im Blick hast. (je nach Sortierung sollte das ohne größere Umstände möglich sein.)
- ist der Eintrag nicht vorhanden, starte das Makro durch Druck auf den Textfeld-Knopf. Du brauchst nach Kontrolle der ersten 2-3 Einträge nicht mehr kontrollieren sondern kannst im Bild so wie eingestellt bleiben
- Du kannst dich drauf verlassen, dass das reproduzierbar geht.
Bei Rückfragen melde dich nochmal!
Hallo, also Anschrift 1 ist tatsächlich das Institut oder Praxis. Anrede 1 ist Herrn bzw Frau, die andere Anrede ist leer. PLZ Wien brauche ich nicht, aber mit PLZ 1 bin ich heute fertig geworden. Ich brauche nur Deutschland.... habil ohne Punkt ist kein Fehler, Wegen der Namen....dann trenne ich es manuell, ich muss sowieso den Nachnahmen in Kleinbuchstaben schreiben. GFFC sind die vorausgefüllten Daten in der Liste, da steht ein Ja, sobald ich aus der Internetseite einen Eintrag ergänzen muss steht dort ein Nein und bei DAF ein Ja, sind ein Eintrag vorhanden in der GFFC Liste und in der DAF Liste steht auf beiden Seiten ein Ja. DAF Liste ist die Internetseite und GFFC ist die vorhandene Liste in der Excel Tabelle. Wenn du mir eine Mail schickst, dann schick ich dir mal die Liste. Hast du noch Fragen?
- erst mal: danke für den Stern, hat doch geklappt!
- Um persönliche Daten zu übermitteln, kann man hier über einen Freundschaftsantrag und dann eine PN8persönliche Nachrichten) machen, die kann dann ausser uns nur der Administrator lesen.
- Auf die Schnelle: hab mal die Einträge für PLZ 1..., 2..., 3... kopiert nach xl und stelle fest, dass die Blöcke keine gleiche Struktur aufweisen. Muss mal sehen, ob ich da einen Ankerpunkt pro Block finde, einfach aufgeben is nich! (noch bin ich optimistisch für ein zumindest etwas arbeitserleichterndes Verfahren!)
Es ist mir gelungen, alle Einträge eines Blocks trotz unterschiedlicher Fehlstellen eindeutig zuzuordnen (institut und Straße/Nr über die relative Position zu der IMMER VORHANDENEN PLZ/Ort.
Die PLZ geht relativ einfach vom Ort abzutrennen, die vierstellige PLZ zB Wiens enthält dann eben ein Leerzeichen anstatt der 5.Ziffer.(Sollte das stören, ließe sich das auch noch mit nur einer kleinen Komplikation entfernen)
Schwierig dürfte die Trennung von Titeln ud Name werden, man könnte nach dem letzten Punkt suchen, sofern das PD Dr. med. habil Mathias WITT ohne Punkt nach habil ein Schreibfehler wäre.
Die Trennung von Vorname(n) und Name ist mir nicht möglich: Im Block der PLZ 1 kommt zwar kein doppelter Vorname ohne Bindestrich vor, das ist aber gar nicht so selten: den Typ Peter Paul Rubens (2 getrennte Vornamen) kann ich nicht unterscheiden von dem realen Beispiel Mazen AL JABRIE oder dem bei uns durchaus geläufigen Alexander GRAF LAMBSDORF oder gar FREIHERR VON UND ZU GUTTENBERG -welchen Vornamen hatte der gleich wieder? (k.A., ob bei Euch sowas vorkommt?)
Bei allen Fortschritten, es gibt noch viel zu tun, aber ich sehe Land!
Noch was: die vielen anderen Spalten, wo kommen denn die her? zB Anrede.
Dinge wie GFFC , DAF etc verstehe ich nicht, sowas finde ich aber auch nicht in der Liste. Anschrift 1: ist das das ggf vorhandene Institut?
Und dsann kommt mehrfach Praxis: vor, ohne was dahinter. Ich kann die Bruchstücke natürlich nur dahin schichen, wo ich weiß dass sie hingehören.
Umgekehrt hast du wohl mit einigen Ausdrücken Schwierigkeiten: im Code? .offset heißt Versatz(Zeilen, Spalten): ich wähle im Makro eine Zelle weit unten aus, gehe mit End(xlup) nach oben als wenn ich mit der Tastatur Ende Pfeilnachoben drücke, lande alo auf der letzten gefüllten Zelle. Von diesem Punkt aus dirigiere ich den Eintrag mit offset um eine gewisse Spaltenzahl nach rechts.
So, es ist wieder halb drei geworden, jetzt bevorzuge ich die Horizontale!
Hallo lamiam, ja vielen vielen dank. Übrigens die Internetseite öffnet sich, aber du musst auf Mitgliedersuche gehen und bei PLZ eine 1 eingeben, dann spuckt er dir alle PLZ im 1er Bereich aus und so weiter mit 2.....bis 9. Ich habe mir die Seite auch nach Hause geschickt und da habe ich keinen gesonderten Zugang. Für mich sind nur manche Ausdrücke von deiner Erklärung völlig unbekannt und somit schwer verständlich. Ich gehe immer zwei Tage die Woche arbeiten, Dienstag und Donnerstag und da arbeite ich dann daran. PLZ 0 und 1 habe ich fast fertig. Am Donnerstag mache ich PLZ 1 fertig. Also wenn du nochmal magst, dann hilf mir nochmal mit dem Makro und dem Adressblock bis nächste Woche Montag. Musst du aber nicht, du hast dich wirklich reingekniet und ich habe schon ein schlechtes Gewissen, du wirst auf jeden Fall mit der hilfreichsten Antwort ausgezeichnet. Vielen Dank dafür. Liebe Grüße
in der hilfe werden alles schritte angezeigt, es wäre zu lang hier alles zu kopieren. ist super beschrieben
- zunächst: Ich kann dir nur helfen, wenn die inet-Liste nach xl überführbar ist. (zB über copy-paste?)
- Deine Suche nach dem schon vorhandenen Namen birgt Risiken: Dr. Meier könnte auch bei gleicher PLZ doppelt vorkommen. Du musst eine Kombination vergleichen (Hilfsspalte oder Matrixformel)
- Weiteres auf Nachfrage (hier in dieser Frage) und nur bei Angabe der Spaltenbuchstaben für Name, Vorname und Postleitzahl
Hallo, danke dir für deine Hilfe. Ja die Internet Liste ist kopierbar in die Excel Tabelle. Die Daten stehen dann untereinander. Nur leider ist die vorausgefüllte Excel Tabelle sehr lang und Platz für neue Daten erst sehr weit unten.....Ein doppelter Name kommt nicht vor, das ist sicher.
Anschrift 1 = Spalte E, Funktion=Spalte M, Vorname= Spalte N, Name= O, Akademischer Titel=Spalte P, Anrede 1=Spalte Q, Straße= Spalte S, PLZ= Spalte T, Ort=Spalte U, E-Mail= Spalte W, Telefonnummer=Spalte X
Freue mich über jeden Vorschlag....
ich denke, ich hab das Konzept, scheint gar nicht schwierig zu sein, aber ich werd erst mal drüber schlafen. bis morgen! (bzw. heute)
Bist Du sicher, dass kein Name doppelt vorkommt, oder kommt er niur mit derselben PLZ nicht doppelt vor? (Hätte Auswirkungen auf die Formel!)
ich gehe mal davon aus, dass Du Deine alte Tabelle in Tabelle1 hast, die Inet-Kopie in Tabelle 2 steht.
Ausserdem gehe ich davon aus, dass beide Tabellen die gleiche Spaltenzuordnung haben.
Wenn nicht, verschiebst i die Spalten der Inet-Tabelle an die entsprechende Position. (geht mit etwas Fingerspitzengefühl ganz einfach!). Ich setze die Formeln in die Spalten AB und AA, also hoffentlich ausserhalb Deines genutzten Bereichs
Formel in Tabelle2!AB3:
=Wennfehler(Vergleich(N3;Tabelle1!N:N;0);"nicht vorhanden!")
einfach runterziehen über alle Zeilen in Tabelle2. Du erhältst als Resultat die Zeile des gefundenen Namens (also alles i.O., wie Du sagst) oder den Eintrag "nicht vorhanden".
Nun verbindest Du den gesamten Bereich (indem Du zB in die freien Spalten in der Titelzeile Leerzeichen oder Punkte setzt, legst ein Filter drüber und filterst nach "nicht vorhanden" (alles auswählen abklicken und nicht vorhanden anklicken).
Alle nicht vorhandenen sind jetzt in einem Block vereinigt. Diesen Block kopierst du und hängst ihn an Deine Tabelle1 an. Danach sortierst du wieder nach 1.PLZ und 2. Name, also T und N)
Für die Zweite Variante, dass der gleiche Name nur nicht mit derselben Postleitzahl vorkommt, gilt diese Formel in Tabelle2!AA3:
=Wennfehler(Index(Tabelle1!T:T;Vergleich(N3;Tabelle1!N:N;0))=T3;"nicht vorhanden!")
Das prüft, ob die PLZ übereinstimmt, aber nur für das oberste Auftreten des Namens. Solltest Du diese brauchen, dann frag nochmal zurück, denn dann müsste man
- entweder in beiden Tabellen in einer Hilfsspalte PLZ und Name mit & aneinanderhängen und die Abfrage darauf beziehen (einfachste Lösung)
- oder mit einer Matrixformel arbeiten (kennst Du die schon?).
viel Erfolg!
hallo, erstmal vielen vielen Dank für deine Bemühungen....nun ist es leider so, dass die Adressen die ich einfügen muss im Internet untereinander aufgelistet stehen: in etwa so:
Dr. med. Max Mustermann
Musterstr. 54
12345 Musterstadt
Telefon: 0123-456789
E-Mail: abcdegfh@web.de
Steht nicht in einer anderen Excel Tabelle.....
Doppelter Name kann unter anderer PLZ vorkommen.
Ich denke ich habe keine andere Wahl als die einzelnen Zeilen zu kopieren und einzufügen oder?
Steht nicht in einer anderen Excel Tabelle.
.wie soll ich das verstehen? Kannst Du das nicht in eine andere xl-Tabelle überführen oder kannst du das nicht aus dem Inet nach xl kopieren?
Die Konvertierung der Position in Einzelzeilen pro Name ist möglich per Formel (aber für mich nur innerhalb xl), sofern jede Gruppe gleichviele Zeilen hat oder (ziemlich kompliziert) ein(mehrere) spezielles Zeichen die Einträge trennt (ich hab auch schon mal die 5-stellige PLZ als Ankerpunkt verwendet, mit entsprechendem Aufwand geht viel.
Manchmal ist da auch ein Makro eine deutliche Vereinfachung (gibt nur immer blöde Warnmeldungen beim Öffnen von .xlsm)
Die Spalten kannst Du dann so verschieben, dass sie Deiner schon existierenden Liste entsprechen.
Stell doch mal in einer eigenen AW hier einen Screenshot von einem Ausschnitt ein (Zoom mindestens 150%, Icon-Leiste dafür vorübergehend ausblenden, aber Zeilen- und Spaltenköpfe belassen)
Auch wenn nichts möglich wäre, bitte Rückmeldung, damit ich das abschließen oder bearbeiten kann!
Hallo, also wenn ich es in eine Excel Tabelle aus dem Internet überführe, denke ich kann ich es auch gleich in die vorausgefüllte Tabelle einpflegen. Dadurch dass die Daten im Internet alle untereinander stehen, kann ich sie nur einzeln kopieren und in die richtigen Spalten einpflegen. Oder hast du einen Trick wie man das schneller machen kann? Einen Screenshot bekomme ich mit der Breite der Tabelle nicht hin, und ich wüßte auch nicht wie ich den hier reinkriege. Die Internetseite mit den Adressen lautet: www.daf-online.de/mitgliedschaft/mitgliedersuche
Faxnummer und Internetseite werden nicht benötigt.
Dann muss man die PLZ 1 eingeben und alle Ärzte die er ausspuckt müssen eingepflegt werden. Das setzt sich fort bis PLZ 9.
Sorry Änderung im Aufbau, wobei die zuerst geschriebenen ohne Untereintrag sind:
Region=Spalte A, Hinweis=Spalte B, Bereich=Spalte C, ID Pro Alpha=Spalte D;
mit Untereintrag manchmal auch nicht:
GFFC=Spalte E, DAF=Spalte F, diese beiden sind je nach vorhandensein mit ja oder nein unterlegt,
Anschrift 1 = Spalte G, Anschrift 2=Spalte H, Abteilung=Spalte I, Zusatz Abteilung=Spalte J,Spalten K,L,M,N sind leer aber für spätere Zwecke noch nützlich, Funktion=Spalte O, Vorname= Spalte P, Name= Q,
Akademischer Titel=Spalte R, Anrede 1=Spalte S, Anrede 2=Spalte T, Straße= Spalte U, PLZ=
Spalte V, Ort=Spalte W, Postfach=Spalte X,E-Mail= Spalte Y, Telefonnummer=Spalte Z
Jetzt ist es ganz genau und die anderen folgenden Spalten sind alle frei.
Liebe Grüße
Hallo Funnymaja, das liest sich nun freilich total anders als alles Vorhergehende. Aber ich weiß, dass Ungeübte große Schwierigkeiten sowohl mit der richtigen Ausdrucksweise als auch mit der Einschätzung, was wichtige Infos sind, haben
aus Deinem Link kriege ich leider keinen Adressenblock raus (vermutlich hast Du einen lizensierten Zugang), so dass ich Dir nur anhand des Adressenblocks (für evtl. spätere Fragen: merke Dir den Ausdruck Datenblock im Ggs zu Liste!) von Dr Gabel einen Prototyp für ein Makro schreiben kann, das hoffentlich soweit selbsterklärend wird, dass Du es selbst anpassen/erweitern kannst.
Das Makro kann Dir die Schreibarbeit abnehmen, aber Du musst schon jeden Adressenblock als ganzes markieren und in Dein Blatt einkopieren (Beschreibung dann später). Ich versuch das so bequem wie möglich zu gestalten- bei 1500 zu kontrollierenden Adressen sollte sich das lohnen!
Da die AW nun schon sehr lang ist, fange ich eine neue an, dauert aber etwas, bis ich das Makro geschrieben und die Vorbereitung erklärt habe.
Hoffe, ich werden heute noch fertig, ansonsten würde es nämlich Mittwoch späte Nacht (hab morgen etliche Termine, bekanntlich geht einem als Rentner ja der Urlaub ab...)
Also am einfachsten wäre es, ein kleines vba macro zu schreiben, dazu muss man aber entsprechende Kenntnisse haben.
Hallo lamiam, sorry bin aus Versehen auf den Button keine hilfreiche Antwort gekommen und nun kann ich das nicht mehr rückgängig machen..... aber ich gebe dir mal meine Email Adresse, dann kann ich dir auch einen Screenshot schicken oder die Tabelle usw.....