EXCEL Kundennummern zusammenfügen

Question

Halli Hallo,

mal wieder ich =)

Ich versuche es so genau wie möglich zu erklären, damit es keine missverständnisse geben sollte....

anbei eine 18kb große excel liste von mir hochgeladen: http://uploaded.to/file/4cb5hg

wie ihr sehen könnt habe ich hier in spalte A drei Kategorien (18, 81 und 83) in spalte B, C & D noch mal einzeln aufgeführt

in spalte F ist die kundennummer und in J K & L die jeweilieg adresse...

es gibt kundennummern die nur eine kategorie, aber auch zwei oder drei kategorien besitzen....

wie ihr in zeile 5. 6 & 7 sehen könnt. drei mal die kundennummer und drei mal die dazu jeweilige adresse...

ich würde, bzw muss diese drei zeilen zu einer erstellen....oder falls zwei kategorien angesprochen werden auch zu einer zeile erstellen...

damit verschenke ich viele viele zeilen und es ist leider nicht übersichtlich für die endkontrolle...

ich hoffe ich habe mich dieses mal ein wenig genauer ausgedrückt...

vielen dank im voraus!!!

grüße

mathias

Answer 1

Hi,

@Rainer
So kurz kann man das also halten. ;-)


@Kalle (KJG17)

lt. Spalte A, welche die Original-Kategorie-Daten enthält, [...] Mathias hat versehentlich [...]hat malSchaun in ihrer Lösung...

So hat der ;-) malSchauen, das zumindest angenommen. Ob es sich so verhält, kann nur der Mathias selbst sagen.

Wie dem auch sei. Kopiere ich die Quelle bis ca. Zeile 10000 runter, rechnen beide Makros doch eine ganze Weile rum. Daher hab ich bei mir mittlerweile schon ein Abbruchkriterium eingebaut, welches bewirkt, dass wenn alle 3 Kategorien min. einmal da waren, die naechste Kundennummer durchlaufen wird. Spart zumindest bei dem kopierten Datensatz ne Menge Zeit. (was aber sicher mit echten Daten nicht der Fall sein wird.) Mal schauen, ob mir da noch was einfällt.

Bye
malSchauen

Answer 2

Hi,

Eine kleine Idee hatte ich da noch, um die Laufzeit ein wenig zu reduzieren. Um da dann auch mal etwas Messbares zur Auwertung zu haben, habe ich die Beispieltabelle von Mathias einmal bereinigt (um gleiche Ergebnisse zu erhalten) und bis ca. Zeile 15700 nach unten kopiert. In den letzten Zeilen habe ich dann ein paar Änderungen vorgenommen, damit die Schleifen in meinem Makro beschäftigt werden. Auf diesen Datensatz habe ich dann mal den "Makro-Filter" von Rainer und meinen abgeänderten Entwurf losgelassen.

Aber seht selbst: Filter vs Durchlauf
(Datei als Zip-File mit stattlichen 393KB)

Wenn man die beiden Ergebnisse jeweils nach Name1 sortiert, sollte deutlich werden, dass die Ergebnisse identisch sind. (Nach Kundennummer sortieren ist ungünstig, da durch meine Änderung nun ein Mix aus Zahlenwerten und Zeichenketten bei der FilterAuswertung entsteht.)

Bei einem echten Datensatz wird der Unterschied in der Laufzeit sicher nicht so gravierend ausfallen, wie in diesem Beispiel. Aber wenn diese Auswertung öfter vorgenommen werden muss, läppert sich das ja auch ein klein wenig.

Bye
malSchauen

Answer 3

Hallo malSchaun,

ist schon interessant wie sich die Methode auch auf die Zeit auswirkt. Auf meiner Maschine etwa 1:20 zu 0:07 + 0:05 für das manuelle Kopieren der Kopfzeile und das Löscher zweier überflüssiger Zeilen. Aber das bekommst du bestimmt bei entsprechender Lust auch noch hin. ;0)

Und wie schon gesagt, die rein technische Lösung ist immer nur ein Aspekt, bei solchen Aufgabenstellungen. Selbst wenn das Zeitverhältnis umgekehrt wäre würde ich als Anwender z.B. die Sortieren-Lösung bevorzugen, da mir einfach das notwendige Selbstvertrauen dafür fehlt, bei rd. 10.000 Datensätzen die selbst erstellten Spalten B bis D tatsächlich absolut fehlerfrei ausgefüllt zu haben.

Mal Schaun wie atomictunes die bisher angebotenen Lösungen beurteilt, wenn er nachher wieder seinen Platz bei P&G eingenommen hat. Die könnte natürlich darin bestehen, dass es sich fragt, warum er sich überhaupt erst die Arbeit mit B-D gemacht und nicht schon mal früher hier angefragt hat. ;0)

Gruß
Kalle

Answer 4

@ kalle...

was heißt ich habe mir die arbeit gemacht? ich habe die excel dateien so aufn tisch bekommen =) B-D habe ich so erstellt, damit es leichter wird (dachte ich, wenn nicht könnt ihr/ich auch nur mit spalte A arbeiten)

@ all....

ich bin überwältigt =) in so einer kurzen zeit, so viele antworten....

ich werde mir jetzt erst mal alle excel files runterladen und schauen was ihr schönes gezaubert habt!!!

im voraus schon einmal vielen vielen dank!!!

Answer 5

@ rainer...

am besten gefällt natürlich mir deine lösung, weil sie mir am einfachsten erscheint...

ich habe versucht die spezial-filterung bei meiner original-tabelle anzuwenden, nur leider funktioniert es nicht ganz!!!!

kann es sein, das meine spalten bis CQ gehen?

ich habe auch versucht die spezial-filterung zu erweitern:

Listenbereich: Tabelle1!$A$4:$CQ$10000
Kriterienbereich: Tabelle1!$A$1:$CQ$2

eingabe....und leider ist alles leer =) =) =)

Answer 6

Hallo Mathias,

da der Spezialfilter durch Makro gestartet wird, muss diese
Anpassung im Makro vorgenommen werden.

So sieht der angepasste Code aus

Option Explicit

Private Sub CommandButton1_Click()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)
ws2.Range("A1:CQ" & ws2.Cells(Rows.Count,
6).End(xlUp).Row).ClearComments
ws1.Range("A4:CQ" & ws1.Cells(Rows.Count,
6).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws1.Range("A1:CQ2"),
CopyToRange:=ws2.Range("A1"), Unique:=False
ws2.Select
End Sub

Anbei die erweiterte Datei
ul.to/1f22cm

Gruß
Rainer

Answer 7

hi rainer...muss kurz warten bis ich wieder downloaden kann =)

wie ich sehe empfehlen mir alle das makro einzusetzen....ich versuche mal dein geschriebenes in meine tabelle einzubauen.....

schade da es keine einfache copy-paste funktion gibt =)

Answer 8

@ rainer...

bin jetzt in meiner tabelle

un in visual basic....

habe den quellcode eingegeben und wie erstelle ich den button für die dtabelle?
ich muss leider zugeben, ich habe noch nie mit VB gearbeitet :(

Answer 9

Hallo Mathias,

Menü > Ansicht > Symbolleisten > Steuerelemente Toolbox

hier klickst Du auf das Steuerelement CommandButton und ziehst es in
der Tabelle bei gedrückter linker Maustaste auf die gewünschte Größe.

Rechtsklick auf den Button > Code anzeigen wählen und den Code in
das sich öffnende Codefenster ziehen.

Jetzt musst Du noch den Bearbeitungsmodus deaktivieren (grünes
Dreieck in der VBA-Symbolleiste) und der Button ist scharf.

Den Code den Du bereits vorher in den VBA-Editor hinein kopiert
hattest, löschst Du wieder.

Gruß
Rainer

Answer 10

Hi,

irgendwie hatte ich die Seite2 übersehen und somit nicht gemerkt, daß Ihr hier schon inmitten einer VB-Lösung seit.

Nichtsdestotrotz mein Vorschlag:

Spalte F markieren, Daten - Filter - Spezialfilter - (eventuell auftretende Fehlermeldung von Excel einfach ignorieren).
Im Folgefenster nun haken bei "an selbe Stelle kopieren" und "keine Duplikate".

Nun solltest du Die Kundennummern nur noch einmalig stehen haben.

Diese Kopieren und in Tabellenblatt 2 in Spalte F einfügen.
Tbl-Blatt hat die selbe Struktur wie Blatt 1

Formel in B2 von Tbl.-Blatt 2:
=WENN(SUMMENPRODUKT((Tabelle1!B$2:B$10000=B$1)*(Tabelle1!$F$2:$F$10000=$F2))=0;"";B$1)
Formel soweit benötigt nach unten kopieren und 2 Spalten nach rechts kopieren.

Formel in J2 und nach unten und 2xnach rechts kopiert:
=WENN(INDEX(Tabelle1!J:J;VERGLEICH($F2;Tabelle1!$F:$F;0))=0;"";INDEX(Tabelle1!J:J;VERGLEICH($F2;Tabelle1!$F:$F;0)))

CU Aliba