Adresslisten zusammenfügen und Listen kenntlich machen

Moderator: ModerationP

Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon magnus.blo » 04. Okt 2017, 10:08

Hallo zusammen,

ich habe hier ein kleines Problem mit einigen Adresslisten.

Ich habe insgesamt 50 Listen, die zusammengefügt werden müssen danach auf doppelte Vorkommen überprüft und die doppelten dann nochmal zu einem Eintrag zusammengefügt werden sollen. Dabei soll allerdings kenntlich gemacht werden, in welchen der 50 Listen die Einträge vorgekommen sind.

Dazu habe ich die Listen schon in einer zusammengefügt, eine Hilfsspalte mit den Listenname ergänzt, alles nach den Emailadressen sortiert, diese sind hier das Kriterium, und alle doppelten rot markiert. Ich könnte diese natürlich jetzt händisch zusammenfassen, aber da es insgesamt 150.000 Einträge sind, ist das sehr zeitaufwendig.

Das soll dann nach Möglichkeit so aussehen:

Spalte A Spalte B Spalte C Spalte D
Max.Mustermann@web.de Liste 1
Max.Mustermann@web.de Liste 2
Max.Mustermann@web.de Liste 3
Erika.Mustermann@gmail.com Liste 2
Erika.Mustermann@gmail.com Liste 4


-->

Spalte A Spalte B Spalte C Spalte D
Max.Mustermann@web.de Liste 1 , Liste 2, Liste 3
Erika.Mustermann@gmail.com Liste 2, Liste 4

Hat jemand eine Idee? Das wäre sehr nett!
magnus.blo
 

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon Fen » 04. Okt 2017, 10:58

Hallo,

ein erstes und vorläufiges Feedback:

- mit VBA "Dictionary", email als key, Liste als item, ist es gut und auch bei dieser Anzahl recht schnell zu lösen.
- es geht auch mit Formeln (nicht mein Gebiet), d.h. erst einmal abwarten, ob sich da ein Spezialist bereit findet.

mfg
Fen
 

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon snb » 04. Okt 2017, 11:14

Excel hat ein 'Remove Duplicates' Methode im Tabblatt Data.
snb
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5272
Registriert: 25. Sep 2014, 16:37

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon Fen » 04. Okt 2017, 13:22

Hallo,

merkwürdig, ich dachte jemand anderes würde ein paar Formeln posten.

Der folgende Code ist recycelt und NICHT getestet:

Code: Alles auswählen
Sub Test2()
'matches values in An with the Range(E2:AL" & lastrow)
Anf = Timer

Ar = cells(1).currentregion


'load data into Dictionary
With CreateObject("scripting.dictionary")
    For i = 1 To UBound(Ar)
                If Not .exists(Ar(i, 1)) Then
                    .Item(Ar(i, 1)) = Ar(i,2)
                Else
                    .Item(Ar(i, 1)) = .Item(Ar(i, 1)) & "|" & Ar(i,2)
                End If
    Next i

'Ausgabe

Range("D1").Resize(.count, 2) = application.transpose(array(.keys, .items))
End With
Debug.Print "total time", Timer - Anf
End Sub


Kleinere Fehler solltest du selbst korigieren.

mfg
Fen
 

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon shift-del » 04. Okt 2017, 20:38

Fen hat geschrieben:merkwürdig, ich dachte jemand anderes würde ein paar Formeln posten.

Bei 150.000 Zeilen macht eine Formellösung nicht so viel Spaß.

Hier ein Ansatz mit Power Query.
Tabellenname: tbl_Adressen
Überschriften: Email, Liste

Code: Alles auswählen
let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Adressen"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Liste", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Email"}, {{"Alle Listen", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Benutzerdefiniert", each Table.Column([Alle Listen],"Liste")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Benutzerdefiniert", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Alle Listen"})
in
    #"Removed Columns"
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 19357
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon Gast » 05. Okt 2017, 09:26

shift-del hat geschrieben:
Fen hat geschrieben:merkwürdig, ich dachte jemand anderes würde ein paar Formeln posten.

Bei 150.000 Zeilen macht eine Formellösung nicht so viel Spaß.

Hier ein Ansatz mit Power Query.
Tabellenname: tbl_Adressen
Überschriften: Email, Liste

Code: Alles auswählen
let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Adressen"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Liste", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Email"}, {{"Alle Listen", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Benutzerdefiniert", each Table.Column([Alle Listen],"Liste")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Benutzerdefiniert", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Alle Listen"})
in
    #"Removed Columns"



Vielen Danke, das klappt schon sehr gut!
Allerdings gehen nun alle anderen Spalten verloren. Gibt es da einen Trick, dass die restlichen Spalten erhalten bleiben und ich quasi nur die Spalte B mit den Listen zusammengeführt wird?

Danke schon im Voraus!
Gast
 

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon shift-del » 05. Okt 2017, 14:51

Als Beispiel mit der Spalte "Name".

tbl_Adressen
Code: Alles auswählen
let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Adressen"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Liste", type text}})
in
    #"Changed Type"


qry_Gruppierung_nach_Email
Code: Alles auswählen
let
    Source = tbl_Adressen,
    #"Grouped Rows" = Table.Group(Source, {"Email"}, {{"Alle Listen", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Benutzerdefiniert", each Table.Column([Alle Listen],"Liste")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Benutzerdefiniert", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Alle Listen"})
in
    #"Removed Columns"


qry_Ergebnis
Code: Alles auswählen
let
    Source = Table.NestedJoin(qry_Gruppierung_nach_Email,{"Email"},tbl_Adressen,{"Email"},"tbl_Adressen",JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "tbl_Adressen", {"Name"}, {"Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded {0}", {"Email"})
in
    #"Removed Duplicates"
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 19357
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon magnus.blo » 11. Okt 2017, 10:09

shift-del hat geschrieben:Als Beispiel mit der Spalte "Name".

tbl_Adressen
Code: Alles auswählen
let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Adressen"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Liste", type text}})
in
    #"Changed Type"


qry_Gruppierung_nach_Email
Code: Alles auswählen
let
    Source = tbl_Adressen,
    #"Grouped Rows" = Table.Group(Source, {"Email"}, {{"Alle Listen", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Benutzerdefiniert", each Table.Column([Alle Listen],"Liste")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Benutzerdefiniert", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Alle Listen"})
in
    #"Removed Columns"


qry_Ergebnis
Code: Alles auswählen
let
    Source = Table.NestedJoin(qry_Gruppierung_nach_Email,{"Email"},tbl_Adressen,{"Email"},"tbl_Adressen",JoinKind.LeftOuter),
    #"Expanded {0}" = Table.ExpandTableColumn(Source, "tbl_Adressen", {"Name"}, {"Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded {0}", {"Email"})
in
    #"Removed Duplicates"


Nochmals Danke!

Ich habe die Codes nun zusammengefügt und Beispielhaft einmal für die Spalte "ContactID" angepasst. Dabei ist der Listenname "Gesamtlisten", die zu überprüfende Spalte "Email Address", die Spalte die zusammengefügt werden soll "Liste". Leider bekomme ich da immer Fehlermeldungen und da ich nicht so sehr im Programmieren und in Excel bewandert bin, hoffe ich, Sie können mir da nochmal helfen und vielleicht einen Codes sagen, den ich für mehrer Spalten nehmen kann. Insgesamt sind es 27 Spalten die erhalten bleiben sollen und die ersten drei sind "ContactID", "First Name" und "Last Name".

Und hier mein offensichtlich falscher Code:

let
Source = Table.NestedJoin(qry_Gruppierung_nach_Email,{"Email Address"},Gesamtlisten,{"Email Address"},"Gesamtlisten",JoinKind.LeftOuter),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Email Address", type text}, {"Liste", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Email Address"}, {{"Alle Listen", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Benutzerdefiniert", each Table.Column([Alle Listen],"Liste")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Benutzerdefiniert", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Expanded {0}" = Table.ExpandTableColumn(Source, "Gesamtlisten", {"ContactID"}, {"ContactID"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded {0}", {"Email Address"})
in
#"Removed Duplicates"


Oder ist es möglich die Befehle doc hinterneineder zu schreiben, so wie hier: (Gibt allerdings ebenfalls eine Fehlermeldung)

let
Source = Excel.CurrentWorkbook(){[Name="Gesamtlisten"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Email Address", type text}, {"Liste", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Email Address"}, {{"Alle Listen", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Benutzerdefiniert", each Table.Column([Alle Listen],"Liste")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Benutzerdefiniert", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Alle Listen"})
in
#"Removed Columns"
;

let
Source = Table.NestedJoin(qry_Gruppierung_nach_Email,{"Email Address"},Gesamtlisten,{"Email Address"},"Gesamtlisten",JoinKind.LeftOuter),
#"Expanded {0}" = Table.ExpandTableColumn(Source, "Gesamtlisten", {"ContactID"}, {"ContactID"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded {0}", {"Email Address"})
in
#"Removed Duplicates"

Ich danke Ihnen schon einmal sehr für Ihre Hilfe!
magnus.blo
 

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon snb » 11. Okt 2017, 11:48

Wie Fennek schon erwähnte:

Code: Alles auswählen
Sub M_snb()
  sn = Cells(1).CurrentRegion
   
  With CreateObject("scripting.dictionary")
    For j = 1 To UBound(sn)
      st = Application.Index(sn, j)
      If .exists(sn(j, 3)) Then
        st = .Item(sn(j, 3))
        st(4) = st(4) & ", " & sn(j, 4)
      End If
      .Item(sn(j, 3)) = st
    Next
     
    Cells(1, 10).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
  End With
End Sub
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
snb
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 5272
Registriert: 25. Sep 2014, 16:37

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon shift-del » 11. Okt 2017, 16:25

magnus.blo hat geschrieben:
shift-del hat geschrieben:Ich habe die Codes nun zusammengefügt und Beispielhaft einmal für die Spalte "ContactID" angepasst. Dabei ist der Listenname "Gesamtlisten", die zu überprüfende Spalte "Email Address", die Spalte die zusammengefügt werden soll "Liste". Leider bekomme ich da immer Fehlermeldungen

Die Codes gehören nicht in einen Query sondern in drei. Die Query-Namen habe ich ja mit angegeben.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 19357
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon Gast » 11. Okt 2017, 17:04

shift-del hat geschrieben:
magnus.blo hat geschrieben:
shift-del hat geschrieben:Ich habe die Codes nun zusammengefügt und Beispielhaft einmal für die Spalte "ContactID" angepasst. Dabei ist der Listenname "Gesamtlisten", die zu überprüfende Spalte "Email Address", die Spalte die zusammengefügt werden soll "Liste". Leider bekomme ich da immer Fehlermeldungen

Die Codes gehören nicht in einen Query sondern in drei. Die Query-Namen habe ich ja mit angegeben.



Also mache ich einfach drei verschiedene Abfragen?
Ich mache es nicht mit Power Query, da ich Office 2016 habe und es dort ja Get & Transform heißt. Aber es sollten ja die gleichen Funktionen vorhanden sein.
Gast
 

Re: Adresslisten zusammenfügen und Listen kenntlich machen

Beitragvon shift-del » 11. Okt 2017, 17:50

Ja.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Benutzeravatar
shift-del
Moderator
 
Beiträge: 19357
Registriert: 25. Jan 2009, 22:54
Wohnort: Ehemaliges Fabrikgelände


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: DerHoepp und 24 Gäste