Excel-Makro zum Sammeln von Daten

Question

Ich verwende MS Excel 2016 unter Windows 7 SP1.

Ich bekomme in der Regel Dateien Arbeitsmappen jede hat 1 oder mehrere Blätter

Ich möchte Daten aus diesen Dateien / Blättern in einem Hauptarbeitsblatt sammeln

kopiert größtenteils alle Daten, in einigen Spalten jedoch in anderer Reihenfolge.

Tabellen enthalten Daten wie diese:

id, name, address, telephone,branch,area, ...

Das Masterblatt enthält:

id, name,adress,telephone-1, telephone-2,area,branch

Wenn der Kunde über ein Dateneingabesystem mit zwei Telefonen verfügt, geben Sie es in den meisten Fällen in zwei Zeilen ein, und zwar wie folgt:

id, name, address, telephone,branch,area
g13, jake, 7 st., 23456343, ,
k12,johne,ca st., 142635, ,
k12,johne,ca st.,150979, ,

im Masterblatt sieht es folgendermaßen aus:

id, name, address, telephone-1,telephone-2,area,branch,verified_id
g13, jake, 7 st.,456343, , ,
k12,johne,ca st.,142635,150979, , ,k12

Fügt einfach die ID am Ende hinzu, um zu überprüfen, ob diese Daten von derselben Kunden-ID kopiert wurden.

Ich hoffe, ich habe es gut erklärt, da ich als Sehbehinderter keinen Screenshot zeigen kann.

Answer 1

Hier ist ein Beispiel mit Power Query:

Dies setzt voraus, dass in den duplizierten Spalten, wie Sie in Ihrem Beispiel zeigen, alle identisch sind, mit Ausnahme von „Telefon“. Wenn dies nicht der Fall ist, müssen Sie dieses Verfahren möglicherweise für mehrere Spalten durchführen.

Kommentiert M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"name", type text}, {"address", type text}, {"telephone", Int64.Type}, {"branch", type any}, {"area", type any}}),
    
    //Group by all the rows EXCEPT "telephone"
    //Do not aggregate (select all rows)
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "name", "address", "branch", "area"}, {{"Grouped", each _, type table [id=nullable text, name=nullable text, address=nullable text, telephone=nullable number, branch=text, area=text]}}),
   
   //extract the phone numbers as a "List"
   #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Telephone", each Table.Column([Grouped],"telephone")),

   //remove unwanted column and put the phone number column where it belongs
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"id", "name", "address", "Telephone", "branch", "area"}),

    //add the verified_id column
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "verified_id", each if List.Count([Telephone]) = 1 then null else [id]),

    //extract the individual phone numbers from the list, and split the columnt
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Telephone", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Telephone", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Telephone.1", "Telephone.2"}),
    
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Telephone.1", Int64.Type}, {"Telephone.2", Int64.Type}})
in
    #"Changed Type1"

Answer 2

Ein Makro (zumindest für weniger fortgeschrittene Benutzer/Programmierer) hilft Ihnen nur, wenn Sie genau dieselbe Aktion an den Daten ausführen UND die Daten genau dasselbe Layout haben.

Ich nehme an, dass es möglich ist, hierfür ein Makro zu erstellen, aber das ist nicht notwendig.

Ich würde einfach zur letzten Zelle in der ersten Zeile mit Daten gehen (sagen wir, es ist B8).

Geben Sie „B8=B1“ ein und es wird die ID von B1 angezeigt.

Kopieren Sie dann die Zelle B8 -> wählen Sie alle verbleibenden Zellen in der Spalte B mit Daten aus und fügen Sie die Formel ein.

Sie haben jetzt an beiden Enden Ihre passende ID.

Danke,

Mike

Answer 3

Eine halbmanuelle Lösung:

1. Alles manuell in Masterblätter zusammenführen.
2. Nach ID sortieren
3. Setzen Sie in Zeile E2 ein: =WENN(A2=A3, D2, "") *
4. Nun kopieren und als Werte Spalte E (Telefon2) einfügen
5. Klicken Sie auf der Registerkarte „Daten“ auf „Duplikate entfernen“, wählen Sie nur die Spalte „ID“ aus und fertig

• Leider gibt es bei Schritt 3 ein kleines Problem – wenn Sie bereits Daten aus einer vorherigen Zusammenführung in Spalte E / Telefon 2 haben. Um das Problem zu beheben, setzen Sie einen Filter auf die Kopfzeile und filtern Sie nur nach Leerzeichen. Kopieren Sie dann die Formel und fügen Sie sie nur in die leeren Zellen ein. Alternativ können Sie eine neue Spalte Telefon 2 in F erstellen und in F2 =WENN(A2=A3, D3, E2) eingeben.