Microsoft Excel

Herbers Excel/VBA-Archiv

Suchen und auflisten

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
2Vorlage1

Formeln der Tabelle
ZelleFormel
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

 ABCDEFG
2Muster1Vorlage1Vorlage4Vorlage5Vorlage7  
3Muster2Vorlage1Vorlage2Vorlage3Vorlage4Vorlage5Vorlage6
4Muster3Vorlage1Vorlage2Vorlage3Vorlage4Vorlage5Vorlage6
5Muster4Vorlage1Vorlage2Vorlage4Vorlage6Vorlage8 
6Muster5Vorlage2Vorlage4Vorlage5Vorlage7  
7Muster6Vorlage5Vorlage7Vorlage8   
8Muster7Vorlage1Vorlage2Vorlage5Vorlage8  
9Muster8Vorlage2Vorlage3Vorlage5Vorlage6  
10       

Formeln der Tabelle
ZelleFormel
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

 ABCDEFGHIJK
2           
3Vorlage1 Muster1Muster3Muster4Muster2Muster7Muster3Muster2Muster1 
4Vorlage2 Muster4Muster2Muster3Muster8Muster2Muster5   
5Vorlage2 Muster4Muster3Muster2Muster7Muster5Muster3Muster2Muster4 
6Vorlage3 Muster3Muster8Muster2Muster8Muster3    
7Vorlage4 Muster4Muster2Muster3Muster1Muster2Muster5Muster1Muster3 
8Vorlage5 Muster2Muster8Muster2Muster6Muster1Muster7   
9Vorlage5 Muster2Muster5Muster1Muster2Muster3Muster1Muster2Muster5 
10Vorlage6 Muster8Muster3Muster4Muster2     
11Vorlage7 Muster2Muster3Muster5Muster1Muster2Muster6Muster1Muster6 
12Vorlage8 Muster2Muster4Muster2Muster6Muster3Muster7Muster3Muster7 
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"