PDA

Vollständige Version anzeigen : Daten zueinander ordnen lassen


Slimboyfat
11.07.2014, 11:18
Hallo liebes MSO-Forum,
da das hier mein erster Beitrag ist, wollte ich mich kurz Vorstellen.
Ich bin 28, arbeite in einem kleinen Handelsunternehmen und bin kaufm. Mädchen für alles.

Folgendes Problem:
Die Vergabe der Kd.-Nr. erfolgt derzeit fortlaufend, also letzte KD-Nr.+1.
Das war nicht immer so, da es früher bestimmte Nummernkreise für bestimmte Kunden gab. Jetzt haben wir riesige, leerstehende Nummernblöcke, die wir gerne auffüllen wollen.

Hat jemand eine Idee?

Ein Lösungsansatz von mir war, eine Spalte mit fortlaufenden Zahlen zu erstellen (erste Kd.Nr. bis letzte Kd.Nr.) und Excel dann die Werte zu einander ordnen zu lassen. Leider habe ich keine Ahnung wie das erfolgen soll.

Über Lösungsvorschläge wäre ich sehr dankbar.

Beverly
11.07.2014, 11:39
Hi,

ich nehme an, du meinst das so:

<br/><b><em>Tabelle4</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:106px;" /><col style="width:106px;" /><col style="width:106px;" /><col style="width:106px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>M</td><td>N</td><td>O</td><td>P</td><td>Q</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">19</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Ist</b></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:left; "><b>Soll</b></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; ">20</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><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; ">21</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Kd.Nr</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Name</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; "><b>&nbsp;</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Kd.Nr</b></td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Name</b></td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">22</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">100</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Hans</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; ">100</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Hans</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">23</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">101</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Dieter</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; ">101</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Dieter</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">24</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">102</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Volker</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; ">102</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Volker</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">25</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">207</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Gerd</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; ">103</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; ">26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">208</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Benjamin</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; ">104</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; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">209</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Manuel</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; ">105</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; ">28</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1050</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Achim</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; ">106</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; ">29</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">1051</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Peter</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; ">107</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&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; 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>Q22:Q29</td><td>=WENNFEHLER(SVERWEIS(P22;M$22:N$29;2;0);"")</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><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.08 einschl. 64 Bit</td></tr></table><br/>

<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

Superingo2
11.07.2014, 11:45
Hallo Grußformellose,

das läßt sich ganz leicht mit SVERWEIS lösen.

Formelbeispiel für Q20 (ich sehe ja leider nur ein Foto, daher weiß ich nicht wo die Daten tatsächlich stecken):
=SVERWEIS(P20;$M$20:$N$999;2;0)

Und wenn bei keinem Treffer nicht "#nv" kommen soll, dann nimm:
=WENNFEHLER(SVERWEIS(P10;$M$10:$N$999;2;0);"freie Kd.-Nr.")

LG Ingo

Mc Santa
11.07.2014, 12:00
Hallo,

ich habe die Aufgabe erst ganz anders verstanden, vermutlich suchst du also nicht genau das, aber vielleicht hilft es dir trotzdem :)

<br/><b><em>Tabelle1</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:97px;" /><col style="width:97px;" /><col style="width:137px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Minimum</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">10</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">vergebene Nummern</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Kleinste noch nicht vergebene Nummern</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">44</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">10</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">22</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">15</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">16</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">17</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">18</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">14</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">21</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">19</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">23</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">24</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>C4:C11</td><td>{=KKLEINSTE(WENN(ISTFEHLER(VERGLEICH($C$2+ZEILE(A:A)-1;$A$4:$A$11;0));ZEILE(A:A)+$C$2-1);ZEILE(A1))}</td><td>$C$4</td><td>&nbsp;</td></tr></table><b>Hier wird eine Matrixformel benutzt.<br> Die geschweiften Klammen {} werden nicht mit eingegeben, sondern vor dem Verlassen der Zelle mit STRG + SHIFT + ENTER erzeugt.</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.01 einschl. 64 Bit</td></tr></table><br/>

Falls du Fragen zur Formel hast, oder mit der Anwendung nicht klar kommst, dann frag gerne nach :)
VG

Slimboyfat
11.07.2014, 12:56
Das Problem konnte dank der SVerweis-Funkiton 1A gelöst werden.
Vielen vielen Dank für den schnellen und entscheidenden Hinweiß.
:hands:

Klasse Funktion, die werd' ich mir merken.