Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1476to1480
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

Datensatz bereinigen

Datensatz bereinigen
02.03.2016 10:51:36
Johannes
Guten Morgen,
ich stehe vor einem Haufen Daten (über 15.000) in einer Excel Tabelle, die ich gerne bereinigen möchte.
Die Spalten sind wie folgt aufgebaut.
Firmenname | Name | Vorname | Emailadresse
Viele Daten kommen auch von der gleichen Firma, d.h. sie haben die gleiche Email-Endung.
Das Problem:
Der Firmenname ist nur selten gleich benannt oder gar in der falschen Spalte vermerkt. Bsp.:
1. Zeile: Mustermann Bau GmbH | [LEER] | [LEER] | max@mustermann-bau.de
2. Zeile: Musterman Baugesellschaft | Koch | Peter | peter.koch@mustermann-bau.de
3. Zeile: [LEER] | Mustermann Bau GmbH | [LEER] | günther@mustermann-bau.de
4. Zeile: Mustermann Bau | Brigitte Beispiel | [LEER] | brigitte.beispiel@mustermann-bau.de
Es gibt noch unzählige andere Arten von Fehleinträgen, aber ich glaube es ist das Problem so klar geworden.
Durch eine Hilfsspalte habe ich bereits den kompletten Datensatz nach Email-Endung sortiert.
Ziel ist es nun, im Idealfall in der "Firma"-Spalte einen einheitlichen Namen stehen zu haben und am Besten auch die "Vorname / Nachname" -Spalten korrekt auszufüllen.
Meine Idee war bislang, ein Makro zu schreiben, das alle Zeilen mit der selben Email-Endung erkennt und in einer Messagebox alle bislang eingetragenen Alternativen aufzeigt. Der Benutzer muss dann nur auswählen, welche die korrekte Bezeichnung ist, die dann bei allen anderen ebenfalls eingesetzt wird.
Bei der Umsetzung hapert es leider gewaltig. Ich habe sehr schlechte Kenntnisse von VBA und oft hole ich mir Makro-Schnipsel aus dem Internet und baue sie nach meinem Gusto zusammen. In diesem Fall finde ich allerdings nicht ansatzweise hilfreiche Ansätze im Internet.
Für die Hilfestellung einiger kreativer Köpfe wäre ich sehr dankbar!
Freundliche Grüße
Johannes

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Datensatz bereinigen
02.03.2016 11:02:29
Michael
Hallo,
m.E. ist der Ansatz gut möglich. Im ersten Schritt müsste in einem neuen Blatt eine eindeutige Liste mit 'Firma' und email-Endung aufgebaut werden. Wenn das getan ist, kann mit sverweis oder verweis in der Ursprungstabelle der Firmenname gestzt werden.
Mfg

AW: Datensatz bereinigen
02.03.2016 11:18:07
Johannes
Hallo,
das Problem dabei ist, dass es über 5000 verschiedene Firmen gibt, d.h. für jede Firma müsste ich dann in dem neuen Tabellenblatt einen korrekten Namen eingeben.
Um mir den Aufwand zu ersparen, hatte ich da ja an eine Message-Box gedacht, die mir beim Durchlauf durch die Daten für jede Firma die alternativen Schreibweisen aufzeigt, und ich auswähle, welche die richtige Schreibweise ist.
Das wäre dann eine riesige Zeitersparnis.
Ist sowas überhaupt möglich?
Viele Grüße

Anzeige
AW: Datensatz bereinigen
02.03.2016 11:28:34
Michael
Hallo,
eine Msgbox, aus der von Hand abgeschrieben werden müsste, halte ich für ungeeignet. Aber nach einer geschickten Vorbereitung, vielleich mit einer Pivot-Tabelle, alle Firmennamen in ein Drop-down, evdntuell auch per Datenüberprüfung in die Liste zu legen, und dann 'nur' eine der angebotenen Auswahlmöglichkeiten anzuklicken, könnte praktikabel sein.
Zuerst sollte aber geprüft werden, ob es Kriterien, z.B. Länge des Textes gibt, die eine Vorauswahl automatisieren können.
Mfg

AW: Datensatz bereinigen
02.03.2016 12:57:47
Johannes
Das Dropdown-Menü ist ein guter Hinweis! Hier ist nur wieder das Problem, dass ich das für jede Firma erst erstellen muss. Kann man das vielleicht durch ein Makro automatisieren und wenn ja, wie?
Danke, viele Grüße

Anzeige
AW: Datensatz bereinigen
02.03.2016 15:12:29
Johannes
Ich habe jetzt mal versucht, ein Makro dafür zu schreiben, das wenigstens die Dropdown-Menüs für die jeweiligen Email-Endungen anfertigt.
Es kommt leider immer eine Fehlermeldung, die ich mir nicht erklären kann.
Vielleicht kann jemand mal kurz drüberschauen:
Sub Dropdownmenü()
Dim Zeile As Long
Dim Tester As Long
Dim m As String
Dim n As String
n = 1
m = 1
For Zeile = n To 200
ActiveSheet.Cells(Zeile, 13).Select
'Hier teste ich, ob die Endungen der Emailadressen in den Zeilen gleich ist:
For Tester = m To 30
If ActiveSheet.Cells(Zeile + m, 13).Value = ActiveSheet.Cells(Zeile, 13) Then
m = m + 1
Else: ActiveSheet.Cells(Zeile, 14).Select
With Selection.Validation                'Datenüberprüfung für Drodown-Menü
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Cells(n, 3).Resize(m, 1).Adress
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
n = n + m
Exit For
End If
Next Tester
Next Zeile
End Sub

danke sehr!
viele Grüße,
Johannes

Anzeige
nicht als string
02.03.2016 17:31:56
KlausF
Hallo Johannes,
m und n sollten als Long definiert sein ...
Gruß
Klaus

AW: Datensatz bereinigen
02.03.2016 15:49:11
Michael
Hallo Johannes,
ehrlich gesagt, kch komme mit dem Code nicht zurecht, aber ich möchte einen, vermutlich sehr einfachen Weg vorschlagen.
Zuerst dem ganzen Datensatz in ein leeres Blatt kopieren.
Die emails sollten in der letzten Spalte stehen, dann mit 'Text-in-Spalten', Trennzeichen @ die Domain abspalten und den Daten satz danach sortieren.
Jetzt stehen alle möglichen Schreibweisen einer Firma direkt untereinander.
Das Ziel ist es mit einem Doppelklick auf eine der Schreibweise diese auszuwählen.
Das geht mit dem event 'worksheet_beforeDoubleClick'
Der Code ist
Target.offset(0, hier anpassen, z,b,3) = target.value
Das sollte es gewesen sein. Falls du keine worksheet-event kennst, mit 'alt-F11' und dann das relevante Arbeitsblatt anklicken, im Menu 'worksheet' auf der rechren Seite 'beforedoubleclick' auswahlen.
Mfg

Anzeige
AW: Datensatz bereinigen
02.03.2016 16:20:37
Johannes
Ich weiß nicht, ob es daran liegt, dass ich die Mac-Version 2016 benutze aber bei mir gibt es keine worksheet-events... Und wenn ich den entsprechenden Code für das Double-Click Event in VBA eingebe, funktioniert es irgendwie nicht. Was mache ich falsch?
Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Target.Offset(0, 1) = Target.Value
End Sub

AW: Datensatz bereinigen - manuell
02.03.2016 16:29:10
Daniel
Hi
wenn du Schwierigkeiten mit Makros hast, dann könntest du die manuelle Bereinigung so einfacher machen:
1. wie bereits beschrieben mit TEXT IN SPALTEN den zweiten Teil der E-Mailadresse in eine zweite Spalte abtrennen.
2. die Tabelle nach dieser Spalte mit den E-Mail-Enden sortieren.
3. in der der Firmennamenspalte alle Zellen, die zu einer Firma gehören markieren
4. mit der TAB-Taste zu der Zelle springen, die den gewünschten Namen enthält (oder diese Zelle nochmal mit gedrückter STRG-Taste anklicken)
5. F2 drücken und dann STRG+ENTER. damit wird der Text dieser Zelle in alle markierten übernommen.
Gruß Daniel

Anzeige
AW: Datensatz bereinigen
02.03.2016 17:20:56
Michael
Hallo Johannes,
es ist schwer zu sagen, warum der event nicht auslöst, m.M. sollte es auf dem Mac genauso funktionieren. Wo genau hast du den Code eingegeben? Hast du in mit "Doppelklick" versucht auszulösen?
Mfg

AW: noch Interesse an Lösungsvorschlag?
03.03.2016 20:51:25
Piet
Hallo Johannes
gibt es noch Interesse an weiteren Lösungen?
Ich liebe knifflige Aufgaben. Wenn ja bitte melden .....
mfg Piet

AW: noch Interesse an Lösungsvorschlag?
03.03.2016 21:14:22
Johannes
Hallo Piet,
ja das Interesse besteht immer noch.. :-)
Ich habe mit Daniels Tipp jetzt angefangen, die Firmennamen mehr oder weniger manuell anzupassen. Ist zwar aufwendig, aber mir fällt ansonsten, keine weitere Alternative ein.
Mir ist allerdings noch ein (fast größeres) Problem aufgefallen:
Es sind teilweise Emailadressen von verschiedenen Firmen vertauscht!!!! Es ist zum verrückt werden.
Beispiel:
Zeile 1: "Baugesellschaft bauschön" | Meier | Günther | günther.meier@meier-architekt.de
[...]
Zeile 10: "Architekt Meier" | Max | Mustermann | info@baugesellschaft-bauschön.de
Da ist deine "knifflige Aufgabe" :D
Ich habe schon daran gedacht, per Makro die Email-Domain mit der Spalte "Firmenname" zu vergleichen und nach Ähnlichkeiten zu suchen. Im Internet kursieren ähnliche Lösungen mit "mid()"...
Das übersteigt aber vollkommen meine VBA-Fähigkeiten.
Ich bin immer sehr offen, für sämtliche Anregungen!
Gruß
Johannes

Anzeige
AW: noch Interesse an Lösungsvorschlag?
03.03.2016 23:09:32
Piet
Hallo Johannes
es freut mich sehr das du Interesse hast.
Ich bin nicht der schnellste, brauche meine Zeit, mein Wissen stammt aus Excel 97 Generation,
habe als Frührentner familiaere Aufgaben, aber meine Staerke ist es mich durch Knobbelaufgaben
zu beissen. Bis jetzt hatte ich max 3.500 Datensaetze zu breinigen, die Menge ist unwichtig.
Was mich auszeichnet ist das ich mit dir zusammen eine komplette Test Datei entwickeln möchte,
wo du nicht das Makro selbst entwickeln musst, sondern wir es zusammen machen. Richte dich aber
bitte darauf ein das ich meine Zeit zum Nachdenken brauche. Komplizierte Lösungen schüttelt man
sich nicht locker aus dem Aermel.
Lass mich mal in Ruhe drüber schlafen, ich melde mich wieder mit einer Test Datei.
Wahrscheinlich brauchen wir mehrere Versuche. Aber darin bin ich sehr hartnaeckig!
Lass dich mal überrraschen ....
mfg Piet

Anzeige
AW: Datensatz bereinigen
04.03.2016 03:12:11
Piet
Hallo Johannes
Anbei eine kleine Beispiel Tabelle für die Bereinigung der EMails,
so wie ich es üblicherwiese für mich in der Praxis machen würde.
Lies bitte meine Info durch, ich habe noch mehr Ideen. Jetzt muss
ich aber erst mal schlafen gehen. Morgen geht es weiter ....
ich habe den Thread wieder offen gestellt, er ist in Arbeit.
https://www.herber.de/bbs/user/104083.xls

AW: Datensatz bereinigen
04.03.2016 03:44:11
Piet
Hallo Johannes
noch ein Makro für dich, ex dürfte sehr effektiv sein.

Sub Tabelle2_doppelte_löschen()
'End Adresse in Spalte E
Edr = Range("E30000").End(xlUp).Address
'Schleife für Spalte E-H
For k = 0 To 3
'Spalte E-H selektieren
Range("E3", Edr).Offset(0, k).Select
Adr1 = Selection.Resize(1, 1).Address
GoSub sort
'Schleife zum löschen von doppelten
For Each EM In Selection
If EM.Offset(1, 0) = EM.Value Then EM.Value = Empty
Next EM
GoSub sort
Next k
[A1].Select
Exit Sub
sort: 'Sortier Routine als Unterprogramm
Selection.sort Key1:=Range(Adr1), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Return
End Sub


Anzeige
AW: Datensatz bereinigen
04.03.2016 04:00:10
Piet
Hallo Johannes
Ups, zu früh gedrückt, vor der Erklaerung gesendet.
Kopiere dieses Makro bitte in ein neues Modul Blatt
Bitte kopiere dir deine Daten in unsere Testmappe zum ausprobieren
Kopiere in ein zweites Tabellenblatt genau in die gleichen Spalten E-H
Firma, Name, Vorname, EMail wie in Tabelle1, und lass das Makro laufen.
Dieses Makro löscht in jeder einzelnen Spalte, ganz seperat für sich, alle
doppelten. Jetzt kannst du -optisch prüfen- wo die eindeutigen Fehler sind.
So faellt dir sofort auf ob in der Firmenspalte Vorname, Name, Mail Adresse
stehen, die da nicht hingehören. Ebenso kannst du die anderen Spalten prüfen.
Um eine optische Fehlersuche kommen wir ja nicht herum. Dein Auge sieht 10 mal
schneller und besser wo Fehler sind. Soviel kann ich mir ja niemals ausdenken.
Ich habe ja keine konkreten Daten vor mir, und die paar Beispiele reichen nicht.
Das menschliche Auge und dein Scharfsinn sind hier unerlaesslich.
Ich denke diese Kombination, den Datenmüll so zu verkleinern das du Durchblick
bekommst, dürfte die praxisgerechte Lösung sein. Alles per VBA ist nicht machbar!
In der Zusammenarbeit könnten wir ein gutes Team sein. Würde mich freuen ...
mfg Piet

Sub Tabelle2_doppelte_löschen()
'End Adresse in Spalte E
Edr = Range("E30000").End(xlUp).Address
'Schleife für Spalte E-H
For k = 0 To 3
'Spalte E-H selektieren
Range("E3", Edr).Offset(0, k).Select
Adr1 = Selection.Resize(1, 1).Address
GoSub sort
'Schleife zum löschen von doppelten
For Each EM In Selection
If EM.Offset(1, 0) = EM.Value Then EM.Value = Empty
Next EM
GoSub sort
Next k
[A1].Select
Exit Sub
sort: 'Sortier Routine als Unterprogramm
Selection.sort Key1:=Range(Adr1), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Return
End Sub


Anzeige
AW: Datensatz bereinigen
04.03.2016 21:50:18
Piet
Hallo Johannes
es gibt eine 2. überarbeitet Beispieldatei, bitte laden und ausprobieren.
Ich habe den Thread noch mal offen gestellt damit du ihn leichter findest.
Wir sollten am Ball bleiben ... es ist bestimmt noch viel Arbeit.
mfg Piet
https://www.herber.de/bbs/user/104114.xls

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige