Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Nach Wersten suchen und ganze Zeile kopieren!

Nach Wersten suchen und ganze Zeile kopieren!
12.09.2014 15:45:48
Andrea
Hallo zusammen,
folgendes Problem:
Ich habe verschiedene Spalten z.B. Obst Farbe Pflückdatum.
In den Zeilen könnte jetzt z.B. Apfel, Birne, Pflaume für Obst, und Grün, Gelb, Rot für Farbe und verschiedene Pflückdaten stehen.
Wenn ich jetzt in einem neuen Tabellenblatt alle Grüne birnen vom 02.10. haben will, wie muss dann die Funktion dazu aussheen? Er soll also das erste Tabellenblatt mit den "Metadaten" durchsuchen und mir alle Grünen Birnen vom 02.10. in jede Zeile reinschreiben. Geht das überhaupt oder ist hier n Makro eindeutig besser?
LG
Andrea

Anzeige

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Nach Wersten suchen und ganze Zeile kopieren!
12.09.2014 16:37:23
Daniel
Hi
mit Formeln: Schwierig
von Hand: mit Autofilter in den Spalten nach den gewünschten Werten Filtern und dann die gefilterte Tabelle in die neue Tabelle kopieren (in gefilterten Tabellen werden nur die sichtbaren Zeilen kopiert)
per Makro: genauso wie von Hand. der Recorder ist dein Freund.
die optimale Excellösung wäre hier der Spezialfilter.
hierzu:
1. in der neuen Tabelle in Zeile 1 die Spaltenüberschriften auflisten, die Filterrelevant sind (Obst|Farbe|Datum)
2. in den Zeilen darunter jetzt die Filterkriterien hinschreiben, jeweils unter die passende Überschrift (Birne|grün|2.10)
3. mit etwas Abstand zu den Filterkriterien in einer Zeile alle Überschriften hinschreiben, die angezeigt werden sollen (Obst|Farbe|Datum)
4. zum Erstellen des Filters dann so vorgehen:
- das Blatt selektiern, auf dem die Ausgabe erfolgen soll
- Menüpunkt: DATEN - SORTIERN UND FILTERN - ERWEITERT klicken
- im Auswahlfenster folgendes einstellen:
- "an andere Stelle kopieren"
- Listenbereich: die Liste mit den Grunddaten (Blatt 1)
- Kritereienbereich: der Zellbereich der unter 1. und 2. erstellt wurde
- Kopieren nach: der zellbereich mit den Überschriften aus 3.
- Keine Duplikate: je nach bedarf
Gruß Daniel

Anzeige
AW: Nach Wersten suchen und ganze Zeile kopieren!
12.09.2014 16:54:23
Andrea
Hi zusammen,
ja das mit dem Spezialfilter habe ich auch schon probiert aber komme nicht zum gewünschten Ergebnis. Ich will ja nicht extra die Filterkriterien nochmals in ein Tabellenblatt schreiben.
Auch sollen alle weiteren Metadaten in der Zeile dann "kopiert" werden.
Sprich wenn ich nur nach "Birne" und "Rot" suche, dann soll auch das Datum, und sämtliche anderen Spalten der Zeile kopiert werden.
Habe mich auch schon an das Thema Matrix gewagt, aber kann hier irgendwie nicht mehr als ein Kriterium filtern.

Anzeige
AW: Nach Wersten suchen und ganze Zeile kopieren!
12.09.2014 16:58:18
Daniel
Hi
genau das funktioniert auch mit dem Spezialfilter, wenn du im Schritt 3 alle Spaltenüberschriften hinschreibst und im Schritt 1 nur diejenigen, nach denen du filtern willst.
und irgenwo musst du die Information, wonach du filterst ja hinterlegen.
ansonsten Autofilter und kopieren.
Formeln hätten den Nachteil, dass die für sowas langsam sind und weil du ja nicht genau weisst, wie viele Zeilen du bekommst, wirst du immer mehr Formeln haben als tatsächlich benötigt.

Anzeige
AW: Nach Wersten suchen und ganze Zeile kopieren!
12.09.2014 19:35:14
Andrea
Cool habe das jetzt recht gut hinbekommen! Da ich das ganze aber jeden Tag machen muss und das über mehrere Tabellenblätter wollte ich das halt mit Formeln machen, damit ich einfach nur die Rohdaten in ein Tabellenblatt kopiere und dann automatisch alle anderen Blätter die gefilterten inhalte habe...oder ist es hier einfach am besten den spezialfilter als makro zu recorden?

Anzeige
AW: Nach Wersten suchen und ganze Zeile kopieren!
12.09.2014 20:10:24
Adis
Hallo
hier eine Makro Lösung die m.E. ohne Spezialfilter auskommt, aber effektiv ist.
Es würde mich freuen wenn sie laeuft. Bitte in ein normales Modulblatt kopieren.
Zuerst in einer Testdatei testen ob alles funktioniert wie gewünscht, damit nicht
versehentlich Daten in der Originaldatei zerstört werden.
Die erste Version sieht vor über drei Zellen Werte einzugeben, die gesucht werden.
Bei mir in der Tabelle1, die Original Tabelle mit allen Obstsorten in die Zellen:
Sorte = Range("G2"), Farbe = Range("H2"), Datum = Range("I2")
Über einen Button sucht er die Werte und kopiert fortlaufend in Tabelle2
Als alter Praktiker, der selbst auf dem Grossmarkt gearbeitet hat, ist mir das
viel zu umstaendlich. Viel schneller ist mein -Automatisch ausfüllen- Programm!
Hier gibt man in eine Hilfsspalte ("E") einen Wert ein, egal was, Buchstabe "a"
und das Programm kopiert automatisch alle markierten Zeilen in Tabelle 2.
Bitt mal ausprobieren ob mir als altem Praktiker eine gute Lösung gelungen ist.
Const Adr1 = "A2" 'Anfangs Adresse (ohne Überschrift) für Obst in Tabelle1
Const KopSpa = 3 'Anzahl zu kopierende Spalten, erweiterbar für Stückzahl, etc.
Const AutoSp = "E" 'Hilfsspalte eingeben (hier D) für Auto Ausfüllen
Dim Tab1 As Object, Tab2 As Object
Dim Sorte, Farbe, Datum 'Variant
Sub Obst_Manuell_Suchen_Kopieren()
Set Tab1 = Sheets("Tabelle1")
Set Tab2 = Sheets("Tabelle2")
Sheets("Tabelle1").Select
Sorte = Range("G2").Value
Farbe = Range("H2").Value
Datum = Range("I2").Value
'Schleife zum durchsuchen von Tabelle1
For Each i In Range(Adr1, Range(Adr1).End(xlDown))
If i.Value = Sorte Then
If i.Cells(1, 2) = Farbe Then
If i.Cells(1, 3) = Datum Then
'** Select nur zum Spalten Test
'i.Resize(1, Spa).Select:  Exit Sub
'zuerst End Zeile in Tabelle 2 finden
If Tab2.Range("A2").Value = Empty Then Edr = "A2" Else _
Edr = Tab2.Range("A1").End(xlDown).Offset(1, 0).Address
'gefundene Zeile  -mit Formate- in Tabelle2 kopieren
i.Resize(1, KopSpa).Copy Tab2.Range(Edr)
End If
End If
End If
Next i
End Sub

Sub ObstListe_automatisch_erstellen()
Set Tab1 = Sheets("Tabelle1")
Set Tab2 = Sheets("Tabelle2")
Sheets("Tabelle1").Select
If Tab2.Range("A2").Value = Empty Then Z = 2 Else _
Z = Tab2.Range("A1").End(xlDown).Row + 1
'Schleife zum durchsuchen von Tabelle1
For Each i In Range(Adr1, Range(Adr1).End(xlDown))
'ausgewertet wird ob Spalte D leer ist oder nicht
If i.Cells(1, AutoSp)  "" Then
'gefundene Zeile  -mit Formate- in Tabelle2 kopieren
i.Resize(1, KopSpa).Copy Tab2.Range("A1").Cells(Z, 1)
Z = Z + 1
End If
Next i
End Sub
Gruss Adis
Anzeige

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Nach Werten suchen und ganze Zeile kopieren in Excel


Schritt-für-Schritt-Anleitung

Um in Excel nach Werten zu suchen und die gesamte Zeile zu kopieren, kannst du folgende Schritte durchführen:

  1. Daten vorbereiten: Stelle sicher, dass deine Daten in einem Tabellenblatt strukturiert sind, z.B. Spalten für Obst, Farbe und Pflückdatum.

  2. Spezialfilter verwenden:

    • Erstelle ein neues Tabellenblatt für die Ausgabe.
    • Listet in Zeile 1 die Spaltenüberschriften auf (z.B. Obst, Farbe, Datum).
    • In den Zeilen darunter schreibst du die Filterkriterien (z.B. Birne, grün, 02.10).
    • Setze die Überschriften für die zu kopierenden Daten einige Zeilen darunter.
  3. Filter einrichten:

    • Wähle das Tabellenblatt mit den Daten aus.
    • Gehe zu Daten > Sortieren und Filtern > Erweitert.
    • Wähle "An andere Stelle kopieren".
    • Gib den Listenbereich (deine Daten) und den Kriterienbereich (deine Filterkriterien) an.
    • Klicke auf "OK", um die gefilterten Daten in das neue Blatt zu kopieren.
  4. Makro aufzeichnen (optional): Wenn du diesen Vorgang regelmäßig durchführst, kann es sinnvoll sein, ein Makro aufzuzeichnen, um die Schritte zu automatisieren.


Häufige Fehler und Lösungen

  • Fehler: Keine Daten werden angezeigt.

    • Lösung: Überprüfe, ob die Filterkriterien korrekt eingegeben wurden und die Daten entsprechen.
  • Fehler: Nur einige Daten werden kopiert.

    • Lösung: Stelle sicher, dass du die richtigen Spaltenüberschriften ausgewählt hast und dass alle relevanten Spalten in den Kriterien enthalten sind.
  • Fehler: Der Spezialfilter funktioniert nicht.

    • Lösung: Überprüfe, ob das Datenformat in den Zellen einheitlich ist (z.B. Datumsformat).

Alternative Methoden

  • Autofilter: Du kannst auch den Autofilter verwenden, um die gewünschten Daten zu filtern und anschließend die sichtbaren Zeilen zu kopieren. Dies ist eine einfache Methode, wenn du nicht mit den Kriterien arbeiten möchtest.

  • VBA-Makros: Wenn du häufig ähnliche Aufgaben durchführen musst, kannst du ein VBA-Makro erstellen, das automatisch nach den gewünschten Werten sucht und die gesamte Zeile kopiert. Ein Beispiel für ein VBA-Skript wird weiter unten bereitgestellt.


Praktische Beispiele

Hier ist ein Beispiel für ein einfaches VBA-Makro, das nach bestimmten Werten sucht und die gesamte Zeile in ein anderes Tabellenblatt kopiert:

Sub SucheUndKopiere()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow As Long, i As Long
    Set ws1 = ThisWorkbook.Sheets("Tabelle1")
    Set ws2 = ThisWorkbook.Sheets("Tabelle2")

    lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow
        If ws1.Cells(i, 1).Value = "Birne" And ws1.Cells(i, 2).Value = "grün" Then
            ws1.Rows(i).Copy ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Offset(1)
        End If
    Next i
End Sub

Tipps für Profis

  • Datenvalidierung: Verwende die Datenvalidierung, um sicherzustellen, dass die eingegebenen Filterkriterien korrekt sind und die Benutzer nur gültige Werte auswählen können.

  • Formeln verwenden: Anstatt nur Filter zu verwenden, kannst du auch Formeln wie FILTER() in Excel 365 nutzen, um dynamische Filter zu erstellen.

  • Regelmäßige Updates: Wenn du regelmäßig mit den gleichen Daten arbeitest, kann es hilfreich sein, eine Vorlage zu erstellen, die bereits alle Filter und Makros enthält.


FAQ: Häufige Fragen

1. Frage: Funktioniert das auch in älteren Excel-Versionen? Ja, die meisten beschriebenen Methoden sind auch in älteren Excel-Versionen verfügbar, jedoch können einige Funktionen, wie FILTER(), nur in Excel 365 verwendet werden.

2. Frage: Wie kann ich die Suche automatisieren? Die Automatisierung erfolgt am besten durch das Erstellen eines VBA-Makros, welches die gewünschten Schritte auf Knopfdruck ausführt.

3. Frage: Warum sollte ich einen Spezialfilter verwenden? Der Spezialfilter ist besonders nützlich, weil er nicht nur die Daten filtert, sondern auch die Möglichkeit bietet, die gefilterten Daten in ein anderes Blatt zu kopieren, ohne die Originaldaten zu verändern.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige