PDA

Vollständige Version anzeigen : Doppelte Namen mit Bereichskennung zusammenfassen


mücke
05.05.2009, 13:26
Hallo allerseits,

hab da mal ne Frage. :)

Ich habe eine TB mit ca. 1.500 Namen, Vornamen usw. bekommen.
Diese TB wurde aus mehreren TB´s zusammengebaut, d. h. alle Namen wurden einfach untereinander kopiert. So eine schei….! :(
Jetzt habe ich X mal die Namen in der TB stehen. Da ich mit diesen Daten aber weiterarbeiten muss, suche ich nach einer Lösung, die mir nur jeden Namen einmal mit den dazugehörigen Bereichskennungen ausgibt.
Hat hier jemand eine Idee?
IST-Zustand ...
<b>MOF</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:100px;" /><col style="width:67px;" /><col style="width:57px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /></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 rowspan="2" style="background-color:#ffffcc; font-weight:bold; text-align:center; ">Basis Name</td><td rowspan="2" style="background-color:#ffffcc; font-weight:bold; text-align:center; ">Duplikate</td><td rowspan="2" style="background-color:#ffffcc; font-weight:bold; text-align:center; ">Pr&uuml;fung</td><td rowspan="2" style="background-color:#ffffcc; font-weight:bold; text-align:center; ">Bereich 1</td><td rowspan="2" style="background-color:#ffffcc; font-weight:bold; text-align:center; ">Bereich 2</td><td rowspan="2" style="background-color:#ffffcc; font-weight:bold; text-align:center; ">Bereich 3</td><td rowspan="2" style="background-color:#ffffcc; font-weight:bold; text-align:center; ">Bereich 4</td><td rowspan="2" style="background-color:#ffffcc; font-weight:bold; text-align:center; ">Bereich 5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffffcc; ">Meier, A.</td><td style="background-color:#ffffcc; font-family:Verdana; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffffcc; ">Meyer, C.</td><td style="background-color:#ffffcc; font-family:Verdana; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffffcc; ">M&uuml;ller, B.</td><td style="background-color:#ffffcc; font-family:Verdana; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffffcc; ">M&uuml;ller, B.</td><td style="background-color:#ffffcc; font-family:Verdana; ">Duplikat</td><td style="background-color:#ffffcc; color:#008000; ">Pr&uuml;fung</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffffcc; ">M&uuml;ller, F.</td><td style="background-color:#ffffcc; font-family:Verdana; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffffcc; ">Schulze, D.</td><td style="background-color:#ffffcc; font-family:Verdana; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#ffffcc; ">Test, M.</td><td style="background-color:#ffffcc; font-family:Verdana; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffffcc; ">Test, M.</td><td style="background-color:#ffffcc; font-family:Verdana; ">Duplikat</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#ffffcc; ">Voll, I. S.</td><td style="background-color:#ffffcc; font-family:Verdana; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#ffffcc; ">Zecke, K.</td><td style="background-color:#ffffcc; font-family:Verdana; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#993366; ">&nbsp;</td><td style="background-color:#ffffcc; color:#993366; ">&nbsp;</td><td style="background-color:#ffffcc; color:#993366; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#993366; ">&nbsp;</td><td style="background-color:#ffffcc; color:#993366; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#ffffcc; ">Zecke, K.</td><td style="background-color:#ffffcc; font-family:Verdana; ">Duplikat</td><td style="background-color:#ffffcc; color:#008000; ">Pr&uuml;fung</td><td style="background-color:#ffffcc; color:#008000; text-align:center; ">ja</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td><td style="background-color:#ffffcc; color:#008000; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="background-color:#ffffcc; ">Zecke, K.</td><td style="background-color:#ffffcc; font-family:Verdana; ">Duplikat</td><td style="background-color:#ffffcc; color:#008000; ">Pr&uuml;fung</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&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 >A3</td><td >=VERKETTEN(I3;",";" ";K3)</td></tr><tr><td >B3</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A3;A3)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C3</td><td >=WENN(UND<span style=' color:008000; '>(B3="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D3:H3;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A4</td><td >=VERKETTEN(I4;",";" ";K4)</td></tr><tr><td >B4</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A4;A4)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C4</td><td >=WENN(UND<span style=' color:008000; '>(B4="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D4:H4;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A5</td><td >=VERKETTEN(I5;",";" ";K5)</td></tr><tr><td >B5</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A5;A5)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C5</td><td >=WENN(UND<span style=' color:008000; '>(B5="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D5:H5;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A6</td><td >=VERKETTEN(I6;",";" ";K6)</td></tr><tr><td >B6</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A6;A6)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C6</td><td >=WENN(UND<span style=' color:008000; '>(B6="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D6:H6;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A7</td><td >=VERKETTEN(I7;",";" ";K7)</td></tr><tr><td >B7</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A7;A7)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C7</td><td >=WENN(UND<span style=' color:008000; '>(B7="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D7:H7;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A8</td><td >=VERKETTEN(I8;",";" ";K8)</td></tr><tr><td >B8</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A8;A8)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C8</td><td >=WENN(UND<span style=' color:008000; '>(B8="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D8:H8;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A9</td><td >=VERKETTEN(I9;",";" ";K9)</td></tr><tr><td >B9</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A9;A9)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C9</td><td >=WENN(UND<span style=' color:008000; '>(B9="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D9:H9;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A10</td><td >=VERKETTEN(I10;",";" ";K10)</td></tr><tr><td >B10</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A10;A10)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C10</td><td >=WENN(UND<span style=' color:008000; '>(B10="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D10:H10;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A11</td><td >=VERKETTEN(I11;",";" ";K11)</td></tr><tr><td >B11</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A11;A11)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C11</td><td >=WENN(UND<span style=' color:008000; '>(B11="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D11:H11;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A12</td><td >=VERKETTEN(I12;",";" ";K12)</td></tr><tr><td >B12</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A12;A12)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C12</td><td >=WENN(UND<span style=' color:008000; '>(B12="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D12:H12;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A13</td><td >=VERKETTEN(I13;",";" ";K13)</td></tr><tr><td >B13</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A13;A13)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C13</td><td >=WENN(UND<span style=' color:008000; '>(B13="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D13:H13;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</td></tr><tr><td >A14</td><td >=VERKETTEN(I14;",";" ";K14)</td></tr><tr><td >B14</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(A$3:A14;A14)</span>&gt;1;"Duplikat";"")</td></tr><tr><td >C14</td><td >=WENN(UND<span style=' color:008000; '>(B14="Duplikat";Z&Auml;HLENWENN<span style=' color:#0000ff; '>(D14:H14;"ja")</span>&gt;0)</span>;"Pr&uuml;fung";"")</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>

... und so soll es mal werden...
<b>MOF</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:100px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /></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></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="background-color:#ffffcc; ">Name</td><td style="background-color:#ffffcc; text-align:center; ">Bereich 1</td><td style="background-color:#ffffcc; text-align:center; ">Bereich 2</td><td style="background-color:#ffffcc; text-align:center; ">Bereich 3</td><td style="background-color:#ffffcc; text-align:center; ">Bereich 4</td><td style="background-color:#ffffcc; text-align:center; ">Bereich 5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="background-color:#ffffcc; ">Meier, A.</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="background-color:#ffffcc; ">Meyer, C.</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="background-color:#ffffcc; ">M&uuml;ller, B.</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="background-color:#ffffcc; ">M&uuml;ller, F.</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="background-color:#ffffcc; ">Schulze, D.</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="background-color:#ffffcc; ">Test, M.</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="background-color:#ffffcc; ">Voll, I. S.</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="background-color:#ffffcc; ">Zecke, K.</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; text-align:center; ">ja</td><td style="background-color:#ffffcc; ">&nbsp;</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>

Schon mal vielen Dank!

EarlFred
05.05.2009, 16:14
Hallo Dirk,

um an die Daten heranzukommen, ginge eine Zusammenstellung mit Pivot - das Ergebnis müsste entsprechend Deinen Wünschen dann noch ein wenig angepasst werden. Ich bin da nicht so der Experte, also kommen wir zu Variante 2, einem kleinen Makro:

Option Explicit
Sub zusammenfassen()
Dim dicDaten As Object
Dim arrDatenbereich As Variant
Dim arrBereiche As Variant
Dim i As Long, j As Long

'Dictionary mit zusammengefassten Daten
Set dicDaten = CreateObject("Scripting.Dictionary")

'Datenbereich in eine Variable einlesen (Geschwindigkeit)
'BEREICH ANPASSEN!
arrDatenbereich = Range("A3:H14")

'Datenbereich analysieren
For i = 1 To UBound(arrDatenbereich)

'Wenn Name nicht vorhanden, erstelle Dictionary-Eintrag
'mit leerem Datenfeld
If IsEmpty(dicDaten(arrDatenbereich(i, 1))) Then _
dicDaten(arrDatenbereich(i, 1)) = Array("", "", "", "", "")
'Lese vorhandene Angaben (Datenfeld) zum Namen aus
arrBereiche = dicDaten(arrDatenbereich(i, 1))
'Prüfe Datenbereich und ändere Angaben im Datenfeld
For j = 0 To 4
'(j+4 resultiert aus exemplarischen Tabellenaufbau,
'da Angabe zu Bereichen in Spalten D:H (4-8)!)
If arrDatenbereich(i, j + 4) = "ja" Then arrBereiche(j) = "ja"
Next j
'neue Angaben dem Dictionary-Eintrag zuweisen
dicDaten(arrDatenbereich(i, 1)) = arrBereiche
Next i

'BEREICH ANPASSEN!
With ActiveSheet.Range("M1") 'Ausgabebereich
.Resize(1, 6) = Array("Name", "Bereich 1", "Bereich 2", "Bereich 3", "Bereich 4", "Bereich 5")
.Offset(1, 0).Resize(dicDaten.Count, 1) = WorksheetFunction.Transpose(dicDaten.keys)
.Offset(1, 1).Resize(dicDaten.Count, 5) = WorksheetFunction.Transpose(WorksheetFunction.Transpose(dicDaten.items))
End With
End Sub


Die Bereiche noch anpassen (ggf. letzte Zeile bestimmen etc., Du weißt schon...)

Grüße
EarlFred

mücke
05.05.2009, 20:09
Hallo EarlFred,

entschuldige bitte die späte Rückmeldung, musste noch etwas außer Haus erledigen.

Die Pivot-Variante hatte ich zuerst getestet. Leider bekam ich hier nicht das passende Ergebnis :( ...

... aber ...

mit DEINER "kleinen" Makro-Variante (und hier bist du auf jeden Fall ein Experte) hat es wunderbar funktioniert !!! :)

Vielen Dank für die schnelle und perfekte Hilfe!
Wünsche noch einen schönen Abend!

EarlFred
05.05.2009, 20:20
Hallo Dirk,

bei Deinen netten Rückmeldungen (egal ob früh oder spät ;)) macht das Helfen Spaß!

Also: Gern geschehen!

Grüße
EarlFred