PDA

Vollständige Version anzeigen : Doppelte Datensätze filtern


Ghost570
07.10.2010, 09:32
Guten Morgen an alle,

ich habe ein kleines Problem in Excel, und möchte dort doppelte Datensätze löschen.

Dabei stehen in Spalte "A" die Kundennummer, und in Spalte "B" ein Volumen.
Sind in Spalte "A" gleiche Werte, und in Spalte "B" unterschiedliche Volumen sollen diese gefiltert und angezeigt werden. Wie mache ich das über eine Formel.

Vielen Dank.....Ghost570

chris-kaiser
07.10.2010, 09:52
hi

keine Formel
Daten -> Dupplikate entfernen

oder ansonsten siehe
http://www.excelformeln.de

gehe auf die Suche
gebe dort Duplikate ein

das ist sicher etwas mit Formllösung dabei.

Ghost570
07.10.2010, 10:55
Hallo Chris,

über Duplikate entfernen kann ich es nicht machen.

Ich möchte ja Duplikate in Spalte "A" angezeigt bekommen, wenn der Wert in "B" unterschiedlich ist.

Ich werde diese sicherlich erst über den Gesamtbestand in einer Formal in Spalte "C" laufen lassen, und deren Ergebnis (Wahr, Falsch) über die Filterfunktion filtern müssen.

Gruß Ghost570

chris-kaiser
07.10.2010, 11:04
Hi

Ich möchte ja Duplikate in Spalte "A" angezeigt bekommen, wenn der Wert in "B" unterschiedlich ist.


diese bleiben ja erhalten ;)
den wenn B unterschiedlich ist, wäre es ja kein Duplikat.

Wilfried07
07.10.2010, 11:14
Hallo Ghost570!

Meinst du es so?

<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></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><td >F</td><td >G</td><td >H</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Doppelt vorkommende Zeilen-Datens&auml;tze bei beliebig vielen Spalten </td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="font-weight:bold; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">7</td><td style="background-color:#ffff00; text-align:right; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >&nbsp;</td><td >x*</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">1</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >&nbsp;</td><td >&nbsp;</td><td >x*</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="background-color:#ffff00; text-align:right; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >a*</td><td >b*</td><td >c</td><td >d</td><td >e</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >a*</td><td >b*</td><td >c</td><td >d</td><td >e</td><td >f</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">7</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >&nbsp;</td><td >&nbsp;</td><td >x*</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >&nbsp;</td><td >b*</td><td >c</td><td >d</td><td >a</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Wilfried</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="background-color:#ffff00; text-align:right; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >Renate</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="background-color:#ffff00; text-align:right; ">4</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >Renate</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="background-color:#ffff00; text-align:right; ">3</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >Renate</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="background-color:#ffff00; text-align:right; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >Renate</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >Wilfried</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">3</td><td >&nbsp;</td><td style="text-align:right; ">2</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">3</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="background-color:#ffff00; text-align:right; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">3</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >bb</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >h</td><td >&nbsp;</td><td >&nbsp;</td><td style="background-color:#ffff00; text-align:right; ">2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >bb</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >h</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >Wilfried</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >Renate</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="background-color:#ffff00; font-weight:bold; ">Spalte i Der jeweils erste Eintrag erh&auml;lt den Z&auml;hler "1", alle &uuml;brigen (doppelten) eine Zahl gr&ouml;&szlig;er 1.</td><td style="background-color:#ffff00; font-weight:bold; ">&nbsp;</td><td style="background-color:#ffff00; font-weight:bold; ">&nbsp;</td><td style="background-color:#ffff00; font-weight:bold; ">&nbsp;</td><td style="background-color:#ffff00; font-weight:bold; ">&nbsp;</td><td style="background-color:#ffff00; color:#ff0000; ">&nbsp;</td><td style="background-color:#ffff00; ">&nbsp;</td><td style="background-color:#ffff00; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="color:#0000ff; font-weight:bold; ">event. Autofilter</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="color:#0000ff; font-weight:bold; ">gr&ouml;sser als &nbsp;1</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="color:#0000ff; font-weight:bold; ">dann alle l&ouml;schen</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</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 >H3</td><td >=SUMMENPRODUKT(<span style=' color:008000; '>(A3:A$340=A3)</span>*<span style=' color:008000; '>(B3:B$340=B3)</span>*<span style=' color:008000; '>(C3:C$340=C3)</span>*<span style=' color:008000; '>(D3:D$340=D3)</span>*<span style=' color:008000; '>(E3:E$340=E3)</span>*<span style=' color:008000; '>(F3:F$340=F3)</span>*<span style=' color:008000; '>(G3:G$340=G3)</span>)</td></tr></table></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Bedingte Formatierungen der Tabelle</b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Zelle</td><td >Nr.: / Bedingung</td><td >Format</td></tr><tr><td >H3</td><td >1. / Zellwert ist gr&ouml;sser als 1</td><td style="background-color:#ffff00; ">Abc</td></tr></table></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>

Gruß
Wilfried

Ghost570
07.10.2010, 13:02
Hallo Wilfried,

perfekt, so soll das sein.......!

Ich danke Dir!