Herbers Excel/VBA-Archiv

Suchen und auflisten

• Suchen und auflisten von Gregor vom 20.11.2014 12:15:08
• VBA ist da nicht notwendig ... - von neopa C (paneo) am 20.11.2014 12:36:52
  • AW: VBA ist da nicht notwendig ... - von Gregor am 20.11.2014 14:34:33
    • hast Du die Formel auch so eingegeben ... - von neopa C (paneo) am 20.11.2014 14:41:12
      • AW: hast Du die Formel auch so eingegeben ... - von Gregor am 20.11.2014 16:00:25
        • vorher sollte die Fehlermeldung geklärt werden ... - von neopa C (paneo) am 20.11.2014 16:17:16
          • vorher sollte die Fehlermeldung geklärt werden ... - von Gregor am 20.11.2014 16:48:26
            • und genau das macht doch meine Formel ... - von neopa C (paneo) am 20.11.2014 16:54:18
              • AW: und genau das macht doch meine Formel ... - von Gregor am 20.11.2014 17:10:43
                • hier wird eine VBA Lösung gesucht ... - von neopa C (paneo) am 20.11.2014 17:25:01
                • AW:mit VBA - von hary am 21.11.2014 09:10:34
                  • AW: AW:mit VBA - von Gregor am 21.11.2014 10:17:20
                  • AW: AW:mit VBA - von Gregor am 26.11.2014 14:07:59

Betrifft: Suchen und auflisten von: Gregor
Geschrieben am: 20.11.2014 12:15:08

Hallo zusammen

Siehe Musterdatei
https://www.herber.de/bbs/user/93905.xlsx

Ich muss auf einer Tabelle mit vielen Daten folgenden Auszug machen.
Auf Blatt "Muster" den Suchbegriff Spalte 1 übernehmen
Auf Blatt "Vorlage" alle Zellen ab Spalte 3 mit dem Suchbegriff durchsuchen
Auf Blatt "Muster" ab Spalte 2 alle Werte aus Blatt "Vorlage" Spalte1 auflisten, wenn der Suchbegriff in der entsprechenden Zeile vorkommt (Siehe Musterdatei). Wert in Blatt "Muster" nur einmal aufführen.

Etwas kompliziert und herausfordernd, ich weiss. Wie könnte ich das mit vba lösen?

Danke und Gruss
Gregor

Betrifft: VBA ist da nicht notwendig ... von: neopa C (paneo)
Geschrieben am: 20.11.2014 12:36:52

Hallo Gregor,

... dass kannst Du mit einer MATRIXFormel ermitteln.

Die Formel in B2 einfach nach rechts und unten kopieren.

	B
2	Vorlage1

Formeln der Tabelle

Zelle Formel B2 {=INDEX(Vorlage!$A:$A;MIN(WENN((Vorlage!$C$3:$Z$99=$A2)*ISTNV(VERGLEICH(Vorlage!$A$3:$A$99;$A2:A2;)); ZEILE(A$3:A$99))))&""}

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
.. , - ...

Betrifft: AW: VBA ist da nicht notwendig ... von: Gregor
Geschrieben am: 20.11.2014 14:34:33

Hallo Werner

Danke für deine Antwort.
Ich benötige jedoch einen vba-Code, damit ich diesen in ein anderes Makro einbinden kann. Zudem erhalte ich bei Eingabe der Formeel eine Fehlermeldung.

Bin also noch nicht am Ziel.

Danke und Gruss
Gregor

Betrifft: hast Du die Formel auch so eingegeben ... von: neopa C (paneo)
Geschrieben am: 20.11.2014 14:41:12

Hallo Gregor,

... wie angegeben: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen?

Gruß Werner
.. , - ...

Betrifft: AW: hast Du die Formel auch so eingegeben ... von: Gregor
Geschrieben am: 20.11.2014 16:00:25

Hallo Werner

Ja, ich habe diese ohne { } kopiert und dafür mit der Tastenkombination STRG+SHIFT+RETURN abgeschlossen. Aber dann kommt die Fehlermeldung.

.... und eben, eigentlich benötige ich einen vba-Code

Danke und Gruss
Gregor

Betrifft: vorher sollte die Fehlermeldung geklärt werden ... von: neopa C (paneo)
Geschrieben am: 20.11.2014 16:17:16

Hallo Gregor,

... denn bei mir gibt es keine. Hast Du eine engl. sprachige Excelversion? Hast Du es in der hier eingestellten Datei getestet oder wo? Wie sieht denn die Fehlermeldung aus?


Gruß Werner
.. , - ...

Betrifft: vorher sollte die Fehlermeldung geklärt werden ... von: Gregor
Geschrieben am: 20.11.2014 16:48:26

Hallo Werner

Ich habe die Formel nun ohne Copy-Paste eingefügt und nun hat es geklappt. Das Ergebnis entspircht jedoch nicht meiner Vorstellung, es erscheint pro Muster nur ein Text aus dem Blatt "Vorlage". Muster1 zB. kommt bei Vorlage1, 4, 5 und 7 vor. Das Ergebnis ist demnach wie folgt:
Muster1 Vorlage1 Vorlage4 Vorlage5 Vorlage7
Siehe Musterdatei

Danke und Gruss
Gregor

Betrifft: und genau das macht doch meine Formel ... von: neopa C (paneo)
Geschrieben am: 20.11.2014 16:54:18

Hallo Gregor,

... Du musst die Formel natürlich nach rechts und unten kopieren.

Sieh doch nochmal:

Muster

	A	B	C	D	E	F	G
2	Muster1	Vorlage1	Vorlage4	Vorlage5	Vorlage7
3	Muster2	Vorlage1	Vorlage2	Vorlage3	Vorlage4	Vorlage5	Vorlage6
4	Muster3	Vorlage1	Vorlage2	Vorlage3	Vorlage4	Vorlage5	Vorlage6
5	Muster4	Vorlage1	Vorlage2	Vorlage4	Vorlage6	Vorlage8
6	Muster5	Vorlage2	Vorlage4	Vorlage5	Vorlage7
7	Muster6	Vorlage5	Vorlage7	Vorlage8
8	Muster7	Vorlage1	Vorlage2	Vorlage5	Vorlage8
9	Muster8	Vorlage2	Vorlage3	Vorlage5	Vorlage6
10

Formeln der Tabelle

Zelle Formel B2 {=INDEX(Vorlage!$A:$A;MIN(WENN((Vorlage!$C$3:$Z$99=$A2)*ISTNV(VERGLEICH(Vorlage!$A$3:$A$99;$A2:A2;)); ZEILE(A$3:A$99))))&""}

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Vorlage

	A	B	C	D	E	F	G	H	I	J	K
2
3	Vorlage1		Muster1	Muster3	Muster4	Muster2	Muster7	Muster3	Muster2	Muster1
4	Vorlage2		Muster4	Muster2	Muster3	Muster8	Muster2	Muster5
5	Vorlage2		Muster4	Muster3	Muster2	Muster7	Muster5	Muster3	Muster2	Muster4
6	Vorlage3		Muster3	Muster8	Muster2	Muster8	Muster3
7	Vorlage4		Muster4	Muster2	Muster3	Muster1	Muster2	Muster5	Muster1	Muster3
8	Vorlage5		Muster2	Muster8	Muster2	Muster6	Muster1	Muster7
9	Vorlage5		Muster2	Muster5	Muster1	Muster2	Muster3	Muster1	Muster2	Muster5
10	Vorlage6		Muster8	Muster3	Muster4	Muster2
11	Vorlage7		Muster2	Muster3	Muster5	Muster1	Muster2	Muster6	Muster1	Muster6
12	Vorlage8		Muster2	Muster4	Muster2	Muster6	Muster3	Muster7	Muster3	Muster7
13
14

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...

Betrifft: AW: und genau das macht doch meine Formel ... von: Gregor
Geschrieben am: 20.11.2014 17:10:43

Hoi Werner

Klar, wie kann ich mich nur so dumm anstellen, das Formel-Ergebnis stimmt.
Und vba?

Danke und Gruss

Betrifft: hier wird eine VBA Lösung gesucht ... von: neopa C (paneo)
Geschrieben am: 20.11.2014 17:25:01

Hallo Marcus,

... ich halte mich aus VBA-Lösungen heraus.

Gruß Werner
.. , - ...

Betrifft: AW:mit VBA von: hary
Geschrieben am: 21.11.2014 09:10:34

Moin Gregor

Dim wksQ As Worksheet, wksZ As Worksheet
Dim letzte As Long, letzteA As Long, i As Long, ii As Long
Set wksZ = Worksheets("Muster")
Set wksQ = Worksheets("Vorlage")
 letzte = wksZ.Cells(Rows.Count, 1).End(xlUp).Row
 letzteA = wksQ.Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To letzte
   For ii = 3 To letzteA
    If Application.CountIf(wksQ.Rows(ii), wksZ.Cells(i, 1)) And Application.CountIf(wksZ.Rows(i) _
, wksQ.Cells(ii, 1)) = 0 Then
     wksZ.Cells(i, wksZ.Cells(i, Columns.Count).End(xlToLeft).Column + 1) = wksQ.Cells(ii, 1)
    End If
   Next
  Next
Set wksZ = Nothing
Set wksQ = Nothing

gruss hary

Betrifft: AW: AW:mit VBA von: Gregor
Geschrieben am: 21.11.2014 10:17:20

Hallo Hary

Toll, dein Code fufnktioniert perfekt, vielen Dank.

Gruss Gregor

Betrifft: AW: AW:mit VBA von: Gregor
Geschrieben am: 26.11.2014 14:07:59

Hallo Hary

Ja, aber ich musste den Code von Rows() in Range ändern, weil nur ab Spalte 78 bis letzeS im Quellbaltt und ab Spalte 5 bis 30 im Zielblatt verglichen bzw. gezählt werden darf. Folgender Code mit CountIf und Range erzeugt einen Fehler.
If Application.CountIf(wksQ.Range(Cells(ii, 78), Cells(ii, lezteS)), wksZ.Cells(i, 1)) And Application.CountIf(wksZ.Range(Cells(ii, 5), Cells(ii, 30)), wksQ.Cells(ii, 1)) = 0 Then

Bei der ersten Frage habe ich vermutlich nicht darauf hingewiesen, dass erst ab Spalte 78 bzw. 5 geprüft werden darf.

Was ist falsch?

Danke und Gruss
Gregor

Beiträge aus den Excel-Beispielen zum Thema "Suchen und auflisten"