Microsoft Excel

Herbers Excel/VBA-Archiv

Zellen in Spalten zuordnen

Betrifft: Zellen in Spalten zuordnen von: Riedinger
Geschrieben am: 05.02.2014 14:42:14

Hallo Excel-Profis,

eine kurze Frage. Ich habe eine Tabelle, die in etwa so aufgebaut ist:

Materialnummer_____Herstellertext
1__________________in rot
___________________in blau
___________________in gelb
2__________________auch grün
3__________________blau
___________________rot
___________________braun
___________________usw.

Jetzt muss ich in einer anderen Tabelle, anhand der Materialnummer, die ganzen Herstellertexte in Spalten hinter die Materialnummer schreiben.
Gibt es hier eine Möglichkeit, da ich ja zum Beispiel in der Zeile "in blau" keine Zuordnung zu der Materialnummer hab?

Herzlichen Dank vorweg und viele Grüße.

  

Betrifft: AW: Zellen in Spalten zuordnen von: Christian
Geschrieben am: 05.02.2014 16:14:14

Hallo,

ein Ansatz mit Hilfsspalte (geht bestimmt auch noch kürzer):

 ABCDEFGHIJK
1MaterialnummerHerstellertext   MaterialnummerHerstellertextHerstellertextHerstellertextHerstellertextHerstellertext
21in rot1  1in rotin blauin gelb  
3 in blau1  2auch grün    
4 in gelb1  3blau rot braun usw.  
52auch grün2        
63blau 3        
7 rot 3        
8 braun 3        
9 usw. 3        
10           
11           
12           
13           

Formeln der Tabelle
ZelleFormel
C2=WENN(B2="";"";A2)
F2{=WENN(ZEILE(A1)>ANZAHL($A$2:$A$200); "";INDEX($A$2:$A$200;KKLEINSTE(WENN($A$2:$A$200<>"";ZEILE($1:$199)); ZEILE(A1))))}
G2{=WENN($F2="";"";WENN(SPALTE(A$1)>ZÄHLENWENN($C$2:$C$200;$F2); "";INDEX($B$2:$B$200;KKLEINSTE(WENN($C$2:$C$200=$F2;ZEILE($1:$199)); SPALTE(A$1)))))}
C3{=WENN(B3="";"";WENN(A3="";INDEX($A$2:A3;MAX(WENN($A$2:A3<>"";ZEILE($1:2)))); A3))}
F3{=WENN(ZEILE(A2)>ANZAHL($A$2:$A$200); "";INDEX($A$2:$A$200;KKLEINSTE(WENN($A$2:$A$200<>"";ZEILE($1:$199)); ZEILE(A2))))}
G3{=WENN($F3="";"";WENN(SPALTE(A$1)>ZÄHLENWENN($C$2:$C$200;$F3); "";INDEX($B$2:$B$200;KKLEINSTE(WENN($C$2:$C$200=$F3;ZEILE($1:$199)); SPALTE(A$1)))))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

MfG Christian


  

Betrifft: AW: Zellen in Spalten zuordnen von: Riedinger
Geschrieben am: 05.02.2014 16:52:57

Hallo Christian,

vielen Dank für deinen Ansatz.
Das Problem ist, die Tabelle hat ca. 72.000 Zeilen, eine manuelle Hilfsspalte (oder die Materialnummer ständig nach unten kopieren) wird somit auch hier etwas langwierig ;-). Aber der Ansatz mit dem Zählenwenn finde ich super, wäre ich gar nicht drauf gekommen.

Eventuell fällt mir ja noch eine gute Idee ein (oder hat jemand eine), mit der ich das ganze automatisieren kann.

Melanie


  

Betrifft: AW: Zellen in Spalten zuordnen von: Christian
Geschrieben am: 05.02.2014 17:05:27

Hallo Melanie,

hier nochmal ein Ansatz ohne Hilfsspalte:

Die Formeln in F2 und G2 nach unten kopieren, die Formel in H2 nach rechts und unten.
Achte bei der Namensdefinition darauf, dass die Zelle H2 angewählt ist!

 ABCDEFGHIJK
1MaterialnummerHerstellertext   MaterialnummerHerstellertextHerstellertextHerstellertextHerstellertextHerstellertext
21in rot   1in rotin blauin gelb  
3 in blau   2auch grün    
4 in gelb   3blau rot braun usw.  
52auch grün   4blau grünrot braun lila
63blau    5braun     
7 rot    6braun lilahellgrau  
8 braun          
9 usw.          
104blau          
11 grün         
12 rot          
13 braun          
14 lila         
155braun          
166braun          
17 lila         
18 hellgrau         

Formeln der Tabelle
ZelleFormel
F2{=WENN(ZEILE(A1)>ANZAHL($A$2:$A$200); "";INDEX($A$2:$A$200;KKLEINSTE(WENN($A$2:$A$200<>"";ZEILE($1:$199)); ZEILE(A1))))}
G2=WENN(F2="";"";INDEX(B:B;VERGLEICH(F2;A:A;0)))
H2=WENN($F2="";"";WENN(SPALTE(A$1)>WENN(ISTNV(letzte_1); letzte;letzte_1)-erste;"";INDEX(INDEX($B:$B;erste):INDEX($B:$B;WENN(ISTNV(letzte_1); letzte;letzte_1)); SPALTE(B$1))))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
H2erste=VERGLEICH(Tabelle1!$F2;Tabelle1!$A:$A;0)
H2letzte=VERWEIS(2;1/(Tabelle1!$B$1:$B$65535<>"");ZEILE(Tabelle1!$B:$B))
H2letzte_1=VERGLEICH(Tabelle1!$F3;Tabelle1!$A:$A;0)-1
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4



MfG Christian


  

Betrifft: AW: Zellen in Spalten zuordnen von: Riedinger
Geschrieben am: 06.02.2014 14:30:15

Wow Christian Super, vielen, lieben Dank es funktioniert. Du bist super.
Und sorry für die späte Rückmeldung mein PC benötigt für solche Dateien immer etwas Berechnungszeit.

Vielen, vielen Dank!


 

Beiträge aus den Excel-Beispielen zum Thema "Zellen in Spalten zuordnen"