Excel WENN in Tabelle1 in Spalte A irgentwo ein "JA" steht ganze zeile in andere Tabelle kopieren

4 Antworten

Dieses Makro sollte die Anforderungen erfüllen:
Weise das Makro einer Autoform zu, die Du am besten in einem fixierten Titel platzierst, und zwar im auszuwertenden =QuellBlatt (bei Dir Tabelle1), das muss das activesheet sein
Für jede "ja"-Zeile einmal betätigen.
Das Makro findet ja genauso wie Jaguar, Djakarta oder Sonja. Man kann dies änddern, indem man das xlpart zum Argument xlwhole verändert (taucht 2x auf!).
Um Doppelübertragungen zu vermeiden, fügt das Makro im Quellblatt nach dem ersten Buchstaben des Suchpartikels ein Leerzeichen ein, dies würde aber bei Djakarta und Sonja ohne Wirkung bleiben(!).
Die Veränderung könnte einzeln (mindestens zwei Zellen markieren! ) oder gesammelt wieder rückwärts ersetzt werden (J a=>Ja), was aber für die weitere Verwendung des Makros schädlich wäre.
Im Zielblatt (Tabelle2) müssen zumindest in A1 und A2 Einträge stehen (können auch Leerzeichen sein), damit [A1].end(xldown) nicht bis ganz unten durchläuft.

Sub JaZeileKopNachAnderBlatt() '
Dim Partikel: Partikel = "ja"
Columns("A:A").Select
If IsError(Selection.Find(What:=Partikel, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)) Then GoTo Fehlt
On Error GoTo Fehlt
Selection.Find(What:=Partikel, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
ActiveCell.EntireRow.Copy Destination:=Worksheets("Tabelle2").[A1].End(xlDown).Offset(1, 0)
ActiveCell.Value = Mid(ActiveCell.Value, 1, 1) & " " & Mid(ActiveCell.Value, 2, 99999)
MsgBox (Partikel & "-Zeile " & ActiveCell.Address & " wurde übertragen und zu " & ActiveCell.Value & " geändert")
On Error GoTo 0
Exit Sub
Fehlt:
MsgBox ("kein (weiteres) " & Partikel)
End Sub

andere Fehler als "kein (weiteres)..." können hier nicht mehr gemeldet werden!

Wollte man .xls mit .xlsm oder dem 2013-Format interagieren lassen, ergäbe sich ebenfalls ein Fehler wegen der unterschiedlichen Zeilenlänge. Man müsste dann anstatt entirerow einen Bereich formulieren, zB

Range(activecell, activecell.offset(0, 25) für die Spalten A:Z

in der letzten Zeile fehlt noch eine schließende Klammer (konnte ich nicht mehr korrigieren). Die vollständige Zeile heisst:

Range(ActiveCell, ActiveCell.Offset(0, 25)).Copy Destination:=Worksheets("Tabelle3").[A1].End(xlDown).Offset(1, 0) 'vorsichtshalber so wegen .xls <=> .xlsx
Das anstelle der Zeile:

ActiveCell.EntireRow.Copy Destination:=Worksheets("Tabelle2").[A1].End(xlDown).Offset(1, 0)

Eine Aktualisierung wie bei Oubyis Formel findet hier nicht statt, der Stand ist "eingefroren"

0

Für eine EINMALIGE Auswertung ist natürlich der Autofilter sinnvoll, so wie er von Chris vorgeschlagen wurde. Da aber immer wieder ähnliche Anfragen kommen, habe ich mal auf die Schnelle eine Beispieltabelle gebastelt, ohne den Autofilter:

Hier wird in Tabelle2 die Tabelle 1 gefiltert, nach Spalte A und dem Suchwort, das in Tabelle2 in J2 eingegeben wird. Bei Änderungen in Tabelle 1 wird automatisch aktualisiert.
"Knackpunkt" ist die Formel in A2, die beliebig nach rechts und nach unten kopiert werden kann:

{=WENNFEHLER(INDEX(Tabelle1!$A$1:$X$1000;KKLEINSTE(WENN(Tabelle1!$A$1:$A$1000=$J$2;ZEILE(Tabelle1!$A$1:$A$1000);9^9);ZEILE(A1));SPALTE());"")}

Nur die Bereiche müssen ggf. erweitert werden, das heißt !$A$1:$X$1000 und $A$1:$A$1000 müssen erweitert werden, und natürlich muss die Formel in Tabelle2 weiter nach rechts und/ oder unten kopiert werden.

[ACHTUNG!
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die { } und macht die Formel zu einer Matrixformel.]

Hier der Link zu meinem Beispiel:

http://www.file-upload.net/download-7654812/GF20210701_BeispielFilterOhneAuto.xlsx.html

Hilft Dir das weiter?

Vielen Dank für die super Erklärung ;D Hatt alles perfekt geklappt ;D

nur eine Frage noch

{=WENNFEHLER(INDEX(Tabelle1!$A$1:$X$1000;KKLEINSTE(WENN(Tabelle1!$A$1:$A$1000=$J$2;ZEILE(Tabelle1!$A$1:$A$1000);9^9);ZEILE(A1));SPALTE());"")}

Wenn das der Code ist um das Filterfeld in J2 zu haben...

ist doch das der Code damit das Feld in K2 ist ?:

{=WENNFEHLER(INDEX(Tabelle1!$A$1:$X$1000;KKLEINSTE(WENN(Tabelle1!$A$1:$A$1000=$k$2;ZEILE(Tabelle1!$A$1:$A$1000);9^9);ZEILE(A1));SPALTE());"")} ???

Mfg

Tom ;D

0
@TomKell

Ja, richtig,
Hättest Du aber auch einfach kurz ausprobieren können.

P.S.:
Mir fällt übrigens gerade auf, dass die von mir oben aufgeschriebene Formel nicht ganz der in der Beispielmappe entspricht. Diese hier steht oben:

{=WENNFEHLER(INDEX(Tabelle1!$A$1:$X$1000;KKLEINSTE(WENN(Tabelle1!$A$1:$A$1000=$J$2;ZEILE(Tabelle1!$A$1:$A$1000);9^9);ZEILE(A1));SPALTE());"")}

Dabei ist das ;9^9 überflüssig, stört allerdings auch nicht (höchstens minimal bei der Performance).
So (wie in der Beispielmappe) reicht es, geändert auf K2:

{=WENNFEHLER(INDEX(Tabelle1!$A$1:$X$1000;KKLEINSTE(WENN(Tabelle1!$A$1:$A$1000=$K$2;ZEILE(Tabelle1!$A$1:$A$1000);9^9);ZEILE(B1));SPALTE());"")}

0

Warum filterst du nicht einfach? und kopierst dann?

WENN-DANN-SONST leere Zeile entfernen?

Wenn der Wert in Tabelle 1 der Spalte D ja ist, dann soll er den Wert von Tabelle 1 Spalte B übertagen sonst soll es leer bleiben.

=WENN(Tabelle 1!D:D="ja";Tabelle1!B:B;"")

Jetzt habe ich dass Problem, dass ich leere Zeilen in meiner Tabelle habe, wie kann ich diese entfernen bzw. was muss ich bei der Formel unter SONST eintragen?

...zur Frage

Excel Zeilen innerhalb einer Tabelle automatisch verschieben?

Hallo

Ich überarbeite zur Zeit mein Haushaltsbuch und habe jetzt folgendes Problem, wofür ich eine Lösung bräuchte.

Ich möchte (siehe Bild - Feste Ausgaben), wenn eine Zeile frei ist, der gesamte untere Bereich automatisch um eine Zeile nach oben verschoben wird.

Beispiel Bild in E7-H7 soll dann E8-H7, E8-H8 soll dann E9-H9 stehen usw.

bzw. wenn zwischen den Zeilen eine leere Zeile frei ist, soll der Bereich der unter der leere Zeile stehen z.B. auch um 1 Zeile automatisch nach oben verschoben werden.

Beispiel Bild: Der gesamte Bereich von E12-H12 - E16-H16 soll um 1 Zeile nach oben verschoben werden Bezieht sich aber auch darauf, wenn ggf. mehrere Zeilen untereinander frei sind

Bezieht sich aber nur auf die Tabelle "Feste Ausgaben". Die Reihenfolge soll nicht verändert werden (z.B. keine Sortierung von A-Z)

Falls relevant Desweiteren stehen in allen Zellen (außer Spalte Datum) Formeln, da die Daten die in der Tabelle stehen aus einer anderen Tabelle übernommen werden. Diese müssen beim verschieben ja eigentlich nur gelöscht bzw. überschrieben werden.

Danke für die Hilfe im vorraus

...zur Frage

von einer tabelle in die zweite kopieren und immer eine zeile bei neuem tag runter?

ich möchte das von einer tabelle (tabelle1)in die andere (tabelle2) kopiert wird und bei neuen tag sollen die eintragungen in tabelle 1 gelöscht werden und in tabelle2 die nächste zeilen genutzt werden das nichts überschrieben wird

...zur Frage

Wert aus Tabelle 1 mehreren Zeilen in Tabelle 2 zuordnen

Ich habe 2 Tabellen. In der ersten Tabelle ist pro Zeile ein Hauptartikel mit einer eindeutigen Artikelnummer aufgeführt. In einer weiteren Spalte der jeweiligen Zeile steht die Katalogseite, in der der Hauptartikel abgebildet ist. Die zweite Tabelle hat alle Varianten eines Hauptartikels. Das sind bis zu 50 Zeilen pro Hauptartikel. Jede Variante hat auch in einer Spalte die Hauptartikelnummer, somit besteht ein eindeutiger Idex zwischen den beiden Tabellen. Jedoch fehlt in der Variantentabelle die Spalte Katalogseite. Wie kann ich die Variantentabelle mit der Katalogseiten des jeweiligen Hauptartikels füllen? Nur zur Verdeutlichung: Tabelle 1, mit einem Hauptartikel pro Zeile hat 650 Zeilen. Tabelle 2 Variantenartikeln, hat 22.000 Zeilen aber auch nur 650 Hauptartikel!

...zur Frage

Excel Tabelle Differenz bilden?

Hallo Leute!

wie kann ich in Excel eine Tabelle mit 3 Spalten ABC erstellen von denen immer die Zeilen z.B. B1-A1=C1 ausgebgeben werden, aber dass ich diese Formel nicht überall hinschreiben muss. Gibt das da einen Weg? Sowas wie jede Zeile der Spalte B - jede Zeile der Spalte A = dazugehörige Zeile in Spalte C.

Soll für eine Art Fahrtenbuch dienen und ständig erweitert werden können ohne immer die Differenzformel anzugeben.

Mfg

Lazarys

...zur Frage

Was möchtest Du wissen?