unterschiedliche Daten in Zellen filtern und in neuer Registerkarte ausgeben

[junjor]

Moin Leute,
 
ich versuche mein Problem mal so kurz und präzise wie möglich darzustellen und füge auch eine komplett anonymisierte (alle Nummern und IDs sind fiktiv) Beispieldatei an:
 
 
Ausgangslage:
Eine veraltete Vereinssoftware muss komplett ausgetauscht werden.
Dazu wurde ein Datenexport der Mitglieder durchgeführt und dieser soll zunächst in Excel von mir wieder aufbereitet werden.
 
Nach dem Import kommen in Excel die drei Spalten „Name“, „Kontaktdaten / Schrank ID“ und „Sparte“ an. So weit so gut!
 
 
Problemstellung:
- die Ausgangsdaten in der Spalte B „Kontaktdaten / Schrank ID“ sind extrem unterschiedlich, durcheinander und auch teilweise im Zeilenumbruch
 
- erschwerend kommt hinzu, dass manche Mitglieder auch Spartenübergreifend gespeichert waren, so dass sie teilweise doppelt oder dreifach auftauchen.
 
Ich habe es auch schon mit aufgenommenen Makros und Excel-Textfiltern in mehreren hintereinander geschalteten Schritten versucht, aber das ist alles derart umständlich und führte letztlich auch nicht zum Erfolg.
 

Ziel:
(siehe Registerkarte „Ziel“ / diese ist manuell als Beispiel erstellt worden)
 
Ich benötige lediglich in einer separaten Registerkarte eine filterbare Liste der Mitglieder (Spalte A), ihren Rufnummern -im internationalen Standard ohne führende Null- (Spalte B) und der dazugehörigen Sparte (Spalte C). Alle anderen Informationen der Ausgangs-Daten (Schrank-ID, Email, etc. sind hierfür irrelevant und zu löschen).
 
- die Mitgliederzahl muss dabei nach unten unbegrenzt bleiben, da es ja idealerweise immer mehr werden, die ich nachträglich einfügen können muss.
 
- es sollen komplette Doppeleinträge gelöscht werden (wenn Name, Nummer und Sparte als Zeile identisch sind). ABER: die Mitglieder können mehrfach auftauchen, wenn sie z.B. mit der gleichen Nummer aber in versch. Sparten geführt werden (z.B. bei dem Mitglied Heinz L. Mann). Oder wenn sie mit verschiedenen Nummern in der gleichen Sparte geführt werden (z.B. bei dem Mitglied Claire Grube).



Frage an die Runde:
Kann mir jemand mit einem VBA-Code aushelfen, der möglichst alle Schritte vereint und den ich so nachvollziehen kann, dass ich daraus noch einen Lerneffekt erziele?  ?
 
Wie gesagt, mit einzelnen VBA-Befehlen (z.B. Split, Suchen, Links, Rechts, Teil, etc…) könnte ich auch umgehen, aber noch nicht in dieser speziellen Komplexität.
 
Vielleicht kommen wir hier ja auch mit mehreren Leuten und vielen helfenden Gedanken irgendwie zusammen zum Ziel…
 
 
Viele Grüße
Micha
 
 
P.S.:

- an den Ausgangs-Daten des alten Programms ist nichts mehr zu ändern! Die muss ich als Basis leider so hinnehmen…
- kein Cross-Post 

[shift-del]

Ausgangslage:
Eine veraltete Vereinssoftware muss komplett ausgetauscht werden.
Dazu wurde ein Datenexport der Mitglieder durchgeführt und dieser soll zunächst in Excel von mir wieder aufbereitet werden.

Ziel:
- die Mitgliederzahl muss dabei nach unten unbegrenzt bleiben, da es ja idealerweise immer mehr werden, die ich nachträglich einfügen können muss.

Klingt für mich widersprüchlich.
Die Ausgangslage ist für mich eine einmalige Sache. Wie kann dann nachträglich noch etwas zum Datenexport hinzukommen.

[junjor]

Hallo shift-del,

danke der Nachfrage, das habe ich dann wohl etwas missverständlich beschrieben 

Also die alte Vereinssoftware ist praktisch unbrauchbar geworden.
Um die vorhandenen Datenbestände aber nicht zu verlieren und zumindest erstmal grob in Excel aufbereiten zu können, ist vorher noch ein Datenexport aus der alten Software erfolgt.
Diese Exportdatei habe ich dann in Excel importiert (siehe Registerkarte: Ausgangs-Daten) und wollte jetzt anfangen, die Daten erstmal in Excel neu zu strukturieren, um sie ggf. in Zukunft einmal in ein neues Programm besser einpflegen zu können.
Und dabei ist es wichtig, dass die Ziel-Tabelle hinsichtlich der Mitgliederzahlen flexibel wird, also z.B. mögliche Such-Formeln nicht nur bis zu einer bestimmten Zeile greifen, sondern nach unten hin unbegrenzt sind, falls neue Mitglieder in dieser Übergangszeit hinzukommen.

Das war meine Intention   


Die Echt-Tabelle ist in der Spalte B "Kontakte/Schrank ID" noch wesentlich umfangreicher und da möchte ich auch zusätzlich zu den Rufnummern, auf die ich mich hier im "einfachen" Beispiel konzentriere, auch noch nach weiteren Parametern suchen und diese in gesonderten Registerkarten ausgeben. Aber das hoffe ich dann anhand der hier aufgezeigten Lösungsmöglichkeiten (das wäre echt super) selbst umsetzen zu können...  

[Fennek]

Hallo,

in einem anderen Projekt habe ich einen Code für die Umwandlung von Telefon-Nummern in ein Standard-Format entwickelt. Etwas angepasst auf deine Datei ist es aber eher nicht so gut:

Code:

'Telefonnummer in Standard-Format bringen: +496912345678

Sub Tel_Nr() '
Dim RegEx As Object, Bsp, RR

Set RegEx = CreateObject("vbscript.regexp")

RegEx.Global = True
RegEx.Pattern = "[0-9\-\s\+\(\)/]{12,27}"

For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
    Bsp = Application.WorksheetFunction.Clean(Cells(i, 2))
   
    If RegEx.Test(Bsp) Then
        Set RR = RegEx.Execute(Bsp)
        Debug.Print RR.Count
        For r = 0 To RR.Count - 1
            'Debug.Print RR(i), fn_tel(RR(i))
            Cells(i, 6) = RR(r)
            Cells(i, 7).NumberFormat = "@"
            Cells(i, 7) = fn_tel(RR(r))
        Next r
    End If
Next i

Set RegEx = Nothing
Set FSO = Nothing
Beep
End Sub

Function fn_tel(ByVal Tel As String) As String
    If IsDate(Tel) Then Exit Function
    If Len(Trim(Tel)) = 11 Then Exit Function
   
    Select Case Left(Trim(Tel), 1)
    Case Is = "+", "0"
        Tel = Replace(Tel, Chr(32), "")
        Tel = Replace(Tel, "/", "")
        Tel = Replace(Tel, "-", "")
        Tel = Replace(Tel, "(0)", "")
        Tel = Replace(Tel, "(", "")
        Tel = Replace(Tel, ")", "")
        If UCase(Tel) <> LCase(Tel) Then Exit Function
        pos = InStr(1, Tel, "-")
        If pos And pos < 6 Then Tel = Replace(Tel, "-", "", , 1)
        If Left(Tel, 1) = 0 And Mid(Tel, 2, 1) <> 0 Then Tel = "+49" & Mid(Tel, 2)
        fn_tel = Tel
    Case Else
        Debug.Print "Tel", Tel
    End Select
End Function

Vielleicht hilft es aus Ausgangspunkt. Es werden nur Nummern zwischen 12 und 27 Zeichen bearbeitet.

mfg

[snb]

Oder:

Code:

Sub M_snb()
   sn = Cells(2, 1).CurrentRegion
  
   For j = 2 To UBound(sn)
      sn(j, 2) = "'+49" & Right(Val(Filter(Split(Filter(Split(Replace(Replace(sn(j, 2), " ", ""), "Phone", "T"), vbLf), "T")(0), ":"), "T", 0)(0)), 10)
   Next
  
   Cells(30, 1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

[junjor]

@Fennek:  ja, deine Variante ist auch ein guter Ansatz - vielen Dank für die Idee und deine Hilfsbereitschaft, mir das Makro zur Verfügung zu stellen!   Allerdings hast du Recht und das Makro passt noch nicht ganz genau auf mein Problem 

@snb:  dein Makro passt perfekt und liefert eigentlich schon genau das, was ich mir vorgestellt habe! Ich hätte nicht gedacht, dass das so schnell geht und bin echt begeistert   , wenngleich ich deinen Code noch nicht vollends durchblickt habe     aber das versuche ich erstmal selbst in den nächsten Tagen nachzuvollziehen und auf meine Echt-Tabelle zu übertragen...   Also schonmal vielen vielen Dank dafür!!!


Ich möchte auch nicht unhöflich sein, aber ich hätte noch 3 kleine Fragen bzw. Anmerkungen, die den Code bzw. die Auswirkungen in der Beispieldatei betreffen:
1. bei Claire Grube wird nach dem Makro nur eine Nummer ausgeworfen, obwohl sie zwei verschiedene hat, die dann beide auftauchen müssten. Woran kann das liegen und kannst du das noch beheben?
2. ist es möglich, dass auch gleich Doppeleinträge, also komplett identische Zeilen (Name, Nummer und Sparte) wie z.B. bei Heinz L. Mann oder auch bei Kyle Riemen im Endergebnis gelöscht werden?
3. Der generierte Datensatz nach dem Makro fügt sich ab Zeile 30 in dasselbe Registerblatt ein. Könntest du das so coden, dass sich die Ergebnisse in einem neuen Registerblatt "Rufnummern" ab A1 einfügen?

Aber wie gesagt, das wäre jetzt nur noch das I-Tüpfelchen, das den Code dann vollends perfekt machen würden. Im Grunde funktioniert es ja so schon super!


@all:   Dank der zwei Antworten bin ich schon sehr viel weitergekommen und kann das Forum hier wirklich nur loben!  

[junjor]

Hallo nochmal,

könnte mir vielleicht schon jemand anderes mit meinen 3 kleinen Fragen weiterhelfen und den tollen Code von snb ggf. dahingehend erweitern?

Das übersteigt leider noch meine Kenntnisse und wäre echt klasse...