PDA

Vollständige Version anzeigen : Spalten vergleichen und nicht doppelte in neu Spalte tragen


Dartgod
10.09.2011, 10:20
Moin Leute,

habe da mal wieder ein kleines Problem wo ich den Wald wieder nicht finde.

Ich habe 2 Spalten.

In beiden stehen Werte. Diese sollen jetzt miteinander verglichen werden nd die nicht doppelten sollen rausgefiltert werden. Ich kriege es nur hin, die so miteinander zuvergleichen, dass er mir anzeigt, welche werte in A auch in B stehen. Ich müsste aber auch umgekehrt vergleichen.

Ich müsste aber die Werte, welche in A und B nicht doppelt sind in spalte C ausgeben. Habt ihr eine Idee für mich??? Vielleicht sogar so, dass es mit einem Makro ist, dami ich keien Formeln habe ... Mit formeln hätte ich aber auch keien Probleme.

Gruß Daniel

josef e
10.09.2011, 11:02
<div style="width:85%; margin-left:5px; margin-right:15px; text-align:justify;">
Hallo Daniel,

<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:130px;" /><col style="width:124px;" /><col style="width:249px;" /><col style="width:140px;" /><col style="width:155px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >&nbsp;</td><td >&nbsp;</td><td style="font-family:Arial; font-size:10pt; ">&nbsp;</td><td style="font-weight:bold; font-family:Arial; font-size:10pt; ">Doppelte</td><td style="font-weight:bold; ">Einzelne</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >2236007BR0000100</td><td >2013000BR0000100</td><td >&nbsp;</td><td >2236007BR0000100</td><td >2236007BR0000500</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >2236007BR0000200</td><td >2013000BR0000300</td><td >&nbsp;</td><td >2236007BR0000200</td><td >2100060BR0000500</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >2236007BR0000300</td><td >2013010BR0000200</td><td >&nbsp;</td><td >2236007BR0000400</td><td >2100060BR0000500</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >2236007BR0000400</td><td >2013010BR0000300</td><td >&nbsp;</td><td >2100060BR0000700</td><td >2100080BR0000200</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >2236007BR0000500</td><td >2013025BR0000100</td><td >&nbsp;</td><td >2100060BR0001000</td><td >2100080BR0000200</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >2100060BR0000500</td><td >2013025BR0000300</td><td >&nbsp;</td><td >2100080BR0000200</td><td >2100032BR0000300</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >2100060BR0000700</td><td >2013025BR0000400</td><td >&nbsp;</td><td >2100032BR0000300</td><td >2960022BR0000500</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >2100060BR0001000</td><td >2013035BR0000100</td><td >&nbsp;</td><td >2960022BR0000500</td><td >2100044BR0000200</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >2100080BR0000100</td><td >2013035BR0000200</td><td >&nbsp;</td><td >2100032BR0000400</td><td >2100044BR0000300</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >2100080BR0000200</td><td >2013035BR0000300</td><td >&nbsp;</td><td >2100044BR0000100</td><td >2100044BR0000400</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >2100080BR0000300</td><td >2013035BR0000400</td><td >&nbsp;</td><td >2100060BR0000400</td><td >2100044BR0000400</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >2100032BR0000200</td><td >2014003BR0000100</td><td >&nbsp;</td><td >2236018BR0000400</td><td >2100044BR0000400</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >2100032BR0000300</td><td >2014003BR0000200</td><td >&nbsp;</td><td >2236010BR0000600</td><td >2100060BR0000100</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >2960022BR0000500</td><td >2014010BR0000300</td><td >&nbsp;</td><td >2960016BR0000100</td><td >2100060BR0000400</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D2</td><td >{=WENN(ANZAHL<span style=' color:008000; '>(VERGLEICH<span style=' color:#0000ff; '>(A2:A316;B2:B184;0)</span>)</span>&lt;ZEILE<span style=' color:008000; '>(A1)</span>;"";INDEX<span style=' color:008000; '>($A$2:$A$316;KKLEINSTE<span style=' color:#0000ff; '>(WENN<span style=' color:#ff0000; '>(ISTZAHL<span style=' color:#804000; '>(VERGLEICH<span style=' color:#ff7837; '>(A2:A316;B2:B184;0)</span>)</span>;ZEILE<span style=' color:#804000; '>($1:$315)</span>)</span>;ZEILE<span style=' color:#ff0000; '>(A1)</span>)</span>)</span>)}</td></tr><tr><td >E2</td><td >{=WENN(<span style=' color:008000; '>(ANZAHL2<span style=' color:#0000ff; '>(A2:A316)</span>-ANZAHL<span style=' color:#0000ff; '>(VERGLEICH<span style=' color:#ff0000; '>(A2:A316;B2:B184;0)</span>)</span>)</span>&lt;ZEILE<span style=' color:008000; '>(A1)</span>;"";INDEX<span style=' color:008000; '>($A$2:$A$316;KKLEINSTE<span style=' color:#0000ff; '>(WENN<span style=' color:#ff0000; '>(NICHT<span style=' color:#804000; '>(ISTZAHL<span style=' color:#ff7837; '>(VERGLEICH<span style=' color:#8000ff; '>(A2:A316;B2:B184;0)</span>)</span>)</span>;ZEILE<span style=' color:#804000; '>($1:$315)</span>)</span>;ZEILE<span style=' color:#ff0000; '>(A1)</span>)</span>)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>




</div>

<div style="width:100px; text-align:center; color:white; font-style:italic; font-size:0.8em; font-family:Tahoma; background-color:royalblue;margin-left:5px; margin-top:15px; padding:4px; border:3px double darkblue;">&laquo; Gru&szlig; Sepp &raquo;</div>

Dartgod
10.09.2011, 20:32
danke funkktioniert!