PDA

Vollständige Version anzeigen : Excel Daten umschichten


bernd_franken
02.07.2014, 11:21
Hallo,

ich habe eine öfters wiederkehrende Arbeit mit Excel.
Es geht immer um ca. 30000 Datenzeilen.
Die Struktur ist:
Stadt...Kd.Nr
Amberg...34343232
Bamberg...34343231
Coburg...34324239
Coburg...6624239
Amberg...1232112
usw.
Nun soll so umgeschichtet werden, daß jede Stadt in einer Extra-Spalte steht und die Kundennummern daneben.
Amberg 34343232.........Coburg 34324239...........Bamberg 34343231
Amberg 1232112.......... Coburg 66324239

Nach Stadt Sortieren und manuell aussschneiden/einfügen ist wegen der Datenfülle und Fehleranfälligkeit nicht gewünscht

Hajo_Zi
02.07.2014, 11:32
<br/><b><em>Tabelle11</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:126px;" /><col style="width:126px;" /><col style="width:126px;" /><col style="width:126px;" /><col style="width:126px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Amberg</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">34343232</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1232112</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Bamberg</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">34343231</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Coburg</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">34324239</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">34324239</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td colspan="4" rowspan="1;border-color:#000000; color:#0563C1; border-color:#000000; background-color:#FFFFFF ; text-decoration: underline ; text-align:left; "><a href="HTTP://WWW.excelformeln.de/formeln.html?welcher=194">HTTP://WWW.excelformeln.de/formeln.html?welcher=194</a></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td colspan="4" rowspan="1;border-color:#000000; color:#0563C1; border-color:#000000; background-color:#FFFFFF ; text-decoration: underline ; text-align:left; "><a href="HTTP://WWW.excelformeln.de/formeln.html?welcher=28">HTTP://WWW.excelformeln.de/formeln.html?welcher=28</a></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width:1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFF66; text-align:center; font-weight:bold; "><td>&nbsp;verbundene Zellen&nbsp;</td></tr><tr><td>A10:&nbsp;D10</td></tr><tr><td>A11:&nbsp;D11</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>A1</td><td>{=INDEX(Tabelle9!A:A;MIN(WENN(Tabelle9!A1:A5&lt;&gt;"";ZEILE(1:5))))}</td><td>$A$1</td><td>&nbsp;</td></tr><tr><td>B1:E3</td><td>{=WENNFEHLER(INDEX(Tabelle9!$B:$B;KKLEINSTE(WENN(Tabelle9!$A$1:$A$5=$A1;ZEILE($1 :$5));SPALTE()-1));"")}</td><td>$B$1</td><td>&nbsp;</td></tr><tr><td>A2:A9</td><td>{=WENNFEHLER(INDEX(Tabelle9!A:A;VERGLEICH(1;(ZÄHLENWENN($A$1:A1;Tabelle9!$A$1:$A $5)=0)*(Tabelle9!$A$1:$A$5&lt;&gt;"");0));"")}</td><td>$A$2</td><td>&nbsp;</td></tr></table><b>{} Matrixformel mit Strg+Umschalt+Enter abschließen<br/>Matrixformeln sind durch geschweifte Klammern {} eingeschlossen<br/>Diese Klammern nicht eingeben!!</b><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.04 einschl. 64 Bit</td></tr></table><br/>

Du hast Extra keinen Tabellenausschnitt dargestellt, da Du die Lösung an Deine Bedingungen selber anpassen möchtest. Ich habe nun schon diese Tabelle erstellt. Ich mache keine Anpassung an Deine Bedingungen.

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

mücke
02.07.2014, 14:07
Moin,
... daß jede Stadt in einer Extra-Spalte steht und die Kundennummern daneben.
Nur mal so aus Interesse, was steckt denn da für eine Logik hinter?
Die Daten können so ja nicht wirklich weiter ausgewertet werden!

Hajo_Zi
02.07.2014, 14:35
oh da hatte ich das wohl falsch interpretiert.

<a href="http://Hajo-Excel.de/index.htm" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>

bernd_franken
02.07.2014, 14:36
Hallo, Hajo_Zi!

VIELEN Dank für Deine Hinweise.

Es hat hervorragend geklappt mit der Formel
=INDEX($B:$B;KKLEINSTE(WENN($C$2:$C$25473=E$1;ZEILE($2:$25473));$A2))

Hat mir einen ganzen Tag Arbeit gespart.

@ mücke
Es steckt viel Logik dahinter. Jede Filiale = Stadt soll bei ihren Kunden bestimmte Sachen nachschauen. Daher sollen jeder Filiale nur "ihre" Kundennummern gemailt werden, nicht fürs ganze Land.

http://abload.de/img/excelvmssx.jpg

Sache erledigt, nochmals DANKE.;)

mücke
02.07.2014, 14:53
Moin,

... die Kd.Nr. steht bei Dir jetzt aber nicht neben der Stadt, sondern darunter :D