Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

gefundene Werte in Zellen untereinander schreiben

gefundene Werte in Zellen untereinander schreiben
30.06.2013 07:56:15
Metin
Guten morgen an alle,
Dank Google habe ich mein Problem fast gelöst. Leider werden die Werte mit einem , getrennt in eine Zelle geschrieben. Ich möchte allerdings das die Werte untereinander immer in der nächsten Zelle fortgeschrieben werden. In Modul1 steht folgender Code. Kann mir einer das so umschreiben das es wie von mir gewünscht funktioniert. Ich möchte nämlich mit diesen Einzelwerten (Zahl oder Text) weitere Berechnungen in meinem Sheet durchführen. Vielen Dank im Voraus.
Option Explicit
Public Function SVERWEIS2(Kriterium As String, Bereich As Range, SuchSpalte As Integer, _ ErgebnisSpalte As Integer, Optional Unikate As Boolean = True, Optional Trenner As String = ", ") As String 'Autor: Ransi Dim arrTmp Dim L As Long Dim Mydic As Object arrTmp = Bereich Set Mydic = CreateObject("Scripting.Dictionary") If Unikate = True Then For L = 1 To UBound(arrTmp) If arrTmp(L, SuchSpalte) = Kriterium Then Mydic(arrTmp(L, ErgebnisSpalte)) = 0 Next SVERWEIS2 = Join(Mydic.keys, Trenner) Else: For L = 1 To UBound(arrTmp) If arrTmp(L, SuchSpalte) = Kriterium Then Mydic(L) = arrTmp(L, ErgebnisSpalte) Next SVERWEIS2 = Join(Mydic.items, Trenner) End If End Function

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
SVERWEIS bei mehrfach vorkommendem Suchkriterium
30.06.2013 08:58:44
ransi
Hallo Metin
SVERWEIS2 kann das nicht. Der war nur für die Verkettung der gefundenen Werte gedacht.
Du brauchst eigentlich sowas:
http://www.excelformeln.de/formeln.html?welcher=28
ransi

AW: gefundene Werte in Zellen untereinander schreiben
30.06.2013 09:22:24
Metin
Hallo ransi,
habe mal die Datei hochgeladen. In Blatt "1" möchte ich das Werte abhängig vom Suchkriterium "E4" in den Zellen A17:A29 wiedergegeben werden. Die Formel soll in Blatt "Verlegeliste" Spalte K das Suchkriterium finden und die dazugehörigen Werte in Spalte A in Blatt "1" A17:A29 wiedergeben. Mit diesen Werten möchte ich dann weiterarbeiten können.
https://www.herber.de/bbs/user/86137.xls

Anzeige
AW: gefundene Werte in Zellen untereinander schreiben
30.06.2013 10:00:36
ransi
Hallo Metin
Dann ist die Formel auf Excelformel.de genau das Richtige für dich.
Schau mal:
1

 BCDEFGHIJK
1Kabeltrommel - Begleitkarte         
2          
3Bauvorhaben  KabeltrommelnummerAblauf mietfreie ZeitBlatt
4ESTW Müllrose098 00 9081. Mrz. 111
5          
6anfängliche lfd m Ende lfd. mLieferfirma:DrakaLieferschein - Nr.:
701000Lieferdatum:02.09.2010  1234
8          
9LieferlängeProjekt - Nr.Teilprojekt-Nr.frei gemeldet an Firma:DatumName
101000730933  25. Apr. 2013 
11          
12Kabel -Typ. Adern / mm leer zurück an Firma: DatumName
13A-2YOF(L)2YB2Y10x1x1,4    
14          
15verwendet für Kabel TeillängeRestlängeverlegt in kmMuffen inTeillänge ausgezogen Name
16( Nr. ) m mvonbiskmam 
17S2/L2 10970     
18S4/L4 20     
19S5/L1       
20S9/L9       
21        
22        
23        
24        
25        
26        
27        
28        
29        

Formeln der Tabelle
ZelleFormel
B17{=WENN(ZÄHLENWENN(Verlegeliste!K:K;$E$4)>=ZEILE(A1);
INDEX(Verlegeliste!$A$6:$O$100;KKLEINSTE(WENN(Verlegeliste!$K$6:$K$100=$E$4;ZEILE($A$1:$A$95)); ZEILE(A1)); 1);
"")}
B18{=WENN(ZÄHLENWENN(Verlegeliste!K:K;$E$4)>=ZEILE(A2);
INDEX(Verlegeliste!$A$6:$O$100;KKLEINSTE(WENN(Verlegeliste!$K$6:$K$100=$E$4;ZEILE($A$1:$A$95)); ZEILE(A2)); 1);
"")}
B19{=WENN(ZÄHLENWENN(Verlegeliste!K:K;$E$4)>=ZEILE(A3);
INDEX(Verlegeliste!$A$6:$O$100;KKLEINSTE(WENN(Verlegeliste!$K$6:$K$100=$E$4;ZEILE($A$1:$A$95)); ZEILE(A3)); 1);
"")}
B20{=WENN(ZÄHLENWENN(Verlegeliste!K:K;$E$4)>=ZEILE(A4);
INDEX(Verlegeliste!$A$6:$O$100;KKLEINSTE(WENN(Verlegeliste!$K$6:$K$100=$E$4;ZEILE($A$1:$A$95)); ZEILE(A4)); 1);
"")}
B21{=WENN(ZÄHLENWENN(Verlegeliste!K:K;$E$4)>=ZEILE(A5);
INDEX(Verlegeliste!$A$6:$O$100;KKLEINSTE(WENN(Verlegeliste!$K$6:$K$100=$E$4;ZEILE($A$1:$A$95)); ZEILE(A5)); 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
ransi

Anzeige
AW: gefundene Werte in Zellen untereinander schreiben
30.06.2013 10:12:50
Metin
Danke ransi,
ist es eventuell noch möglich Dopplungen ebenfalls zu erfassen. als Beispiel käme S2/L2 zwei oder sogar dreimal vor.

AW: gefundene Werte in Zellen untereinander schreiben
30.06.2013 10:17:24
ransi
Hallo Metin
ist es eventuell noch möglich Dopplungen ebenfalls zu erfassen. als Beispiel käme S2/L2 zwei oder sogar dreimal vor.
Wie meinst du das ?
Die Formel erfasst alle Datensätze die in Spalte K dem Suchkriterium entsprechen.
ransio

@Ransi, funktioniert das mit....
30.06.2013 10:14:47
Matze
Moin Ransi,
....verbundenen Zellen? Man sollte hier und das ist wieder einmal ein schlechtes Beispiel dafür alle verbundenen Zellen auflösen. Formatieren über mehrere Spalten ist hier angebracht.
Matze

Anzeige
ooops
30.06.2013 10:19:21
ransi
HAllo
Danke für den Hinweis.
Die verbundenen Zellen in Blatt1 SpaltenB:C hatte ich natürlich vorher aufgelöst.
ransi

AW: gefundene Werte in Zellen untereinander schreiben
30.06.2013 10:08:38
Matze
Hallo Metin,
in Spalte K der Verlegerliste kommt den Suchkriterium mehrfach vor, daher die von Ransi vorgeschlagene Seite mal anschauen. Des weiteren hast du nicht geschrieben, wenn das "Suchkriterium" gefunden wird, welche Spaltenwert zurückgegeben werden soll. Es wird vermutlich eine Spalte vor der eigentlichen Suchspalte werden, daher gleich der nächste Link:
http://www.excelformeln.de/formeln.html?welcher=24
Matze

Anzeige
AW: gefundene Werte in Zellen untereinander schreiben
30.06.2013 10:16:28
Metin
Hallo Matze,
auch dir Danke. Die Werte die ausgegeben werden sollen stehen dann in Bezug zu Spalte K in Spalte A der "Verlegeliste".

Danke an alle
30.06.2013 10:20:19
Metin
Dank an euch beide,
mit den Dopplungen funktioniert es auch wunderbar. Hatte ein kleinen Fehler in der Verlegeliste. Noch einen schönen Sonntag.

@Metin,...
30.06.2013 11:13:22
Matze
hier noch ein Hinweis im Bezug auf deine Sverweisformeln
du solltest diese mit "absoluten" Bezügen definieren:
=SVERWEIS($D$4;Trommelliste!$A$2:$N$1000;3;FALSCH)
somit sind die dann auch nach unten kopierbar.
habe mir mal erlaubt dein Blatt1 in der Formatierung betreffend der verbundenen Zellen abzuändern.
Ebenso habe ich die Sverweis korrigiert, schau es dir mal in ruhe an.
https://www.herber.de/bbs/user/86139.xls
Matze

Anzeige
AW: @Metin,...
30.06.2013 13:04:40
Metin
Hallo Matze,
nun hab ich doch noch ein Problem. Ich bekomme eure Formel nicht für die weitere Bearbeitung umgewandelt. In Blatt "1" stehen nun die Kabelnummern in den Zellen B17:B29. Hier können auch Dopplungen entstehen. Im folgenden wollte ich die Formel jetzt so nutzen, dass B17 und folgend das Suchkriterium ist und in Blatt "1" C17:C29 die passenden Werte aus "verlegeliste" E:E eingetragen werden. Könntet Ihr mir bitte nochmal helfen danke.

weitere Spalte
01.07.2013 01:01:35
Erich
Hi Metin,
da du mehrere Daten aus einer Zeile der Verlegeliste haben willst, lohnt es sich wohl, die Zeilennnummer
einmal zu berechnen, in die Tabelle (hier Spalte A) zu schreiben und dann mehrfach zu nutzen.
Die Zeilennr. in Spalte A kannst du verstecken, z. B. per Zahlenformat ;;; oder Schriftfarbe weiß.
Meintest du das so?
 ABC
15 verwendet für KabelTeillänge
16 ( Nr. )m
1712S2/L233
1814S4/L4110
1915S5/L1101
2019S9/L944
21   

Formeln der Tabelle
ZelleFormel
A17{=WENN(ZÄHLENWENN(Verlegeliste!K:K;$D$4)<ZEILE(A1); "";KKLEINSTE(WENN(Verlegeliste!$K$6:$K$100=$D$4;ZEILE($A$1:$A$95)); ZEILE(A1)))}
B17=WENN(A17="";"";INDEX(Verlegeliste!$A$6:$A$100;A17))
C17=WENN(A17="";"";INDEX(Verlegeliste!$E$6:$E$100;A17))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
weitere Spalte - Korrektur
01.07.2013 01:18:25
Erich
Hi,
die Verlegeliste beginnt mit Zeile 5, und in der A17-Formel muss keine ganze Spalte K vorkommen:
 ABC
15 verwendet für KabelTeillänge
16 ( Nr. )m
1713S341
1815S4/L4110
1916S5/L1101
2020S9/L944
21   

Formeln der Tabelle
ZelleFormel
A17{=WENN(ZÄHLENWENN(Verlegeliste!K$5:K$100;$D$4)<ZEILE(A1); "";KKLEINSTE(WENN(Verlegeliste!$K$5:$K$100=$D$4;ZEILE($A$1:$A$94)); ZEILE(A1)))}
B17=WENN(A17="";"";INDEX(Verlegeliste!$A$6:$A$100;A17))
C17=WENN(A17="";"";INDEX(Verlegeliste!$E$6:$E$100;A17))

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
Korrektur der Korrektur
01.07.2013 01:21:06
Erich
Hi,
und nun auch in den Spalten B und C ...:
 ABC
15 verwendet für KabelTeillänge
16 ( Nr. )m
1713S2/L233
1815S4/L4110
1916S5/L1101
2020S9/L944
21   

Formeln der Tabelle
ZelleFormel
A17{=WENN(ZÄHLENWENN(Verlegeliste!K$5:K$100;$D$4)<ZEILE(A1); "";KKLEINSTE(WENN(Verlegeliste!$K$5:$K$100=$D$4;ZEILE($A$1:$A$94)); ZEILE(A1)))}
B17=WENN(A17="";"";INDEX(Verlegeliste!$A$5:$A$100;A17))
C17=WENN(A17="";"";INDEX(Verlegeliste!$E$5:$E$100;A17))

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige