Excel 2003 Wiedergabe einer Zeile, wenn der erste Wert entsprechend ist - ohne Makro

Hi zusammnen,

kurz und knapp - ist es in Excel möglich, eine komplette Zeile wiederzugeben, wenn der erste Wert in der (Vektor-)Zeile mit einem Zielwert (Artikelnummer) übereinstimmt. Geht sowas ohne Makro?

Als Hintergrund: Die Zellen bleiben nicht gleich, also muss das ganze so gestaltet werden, dass wirklich nach den Werten gegangen wird.

Besten Dank schon mal an die Excel Experten

 

Ohne genaue Inputs kann man nur sagen WENN, ZÄHLENWENN etc.

 

Hi, was bräuchtest du denn noch? Mit "=WENN" funktioniert es zwar, aber dann muss man das über die komplette Mappe ziehen. Das wollte ich eigentlich vermeiden - weil viele Daten. Ich versuchs also mal anders darzustellen:

1. Blatt (Es sind allerdings 1000 Zeilen und 12 Spalten)

A | B | C | D | E
1 1001 | XYZ | 345 | abc | 123
2 1002 | ABC| 987 | xyz | 123
3 1002 | DDD| ooo | vvv | 222
4 1003 | AEF | 268 | 028 | 888

Auf einem separaten Blatt, will ich dann zum Beispiel, dass alle Zeilen zu dem Wert 1002 (in Spalte A) wiedergegeben werden, d.h.:

A | B | C | D | E
1 1002 | ABC| 987 | xyz | 123
2 1002 | DDD| ooo | vvv | 222

Ich hoffe so wird das verständlicher. Bis denne

 

Nicht wirklich, aber es könnte hier SVERWEIS richtig sein. Deine Angaben sind nicht unbedingt hilfreich.

 

Hallo Cmc

Hast Du schon mit Filtern experimentiert? Vielleicht reicht das ja schon für das was Du willst.

- Gehe im Blatt mit der Original-Tabelle in eine Zelle der Zeile mit den Spalten-Ueberschriften
- dann Menü Daten >>> Filter >>> Autofilter

>>> im DropDown in Spalte A wähle zB 1002, dann siehst Du nur noch diese Daten

- Wenn das den Zweck erfüllt, ist schon fertig.

- Wenn die gefilterten Daten in einem anderen Blatt benötigt werden >>> CopyPaste in das andere Blatt

- Wenn das automatisch geschehen soll, weiss ich nicht, wie man das ohne Makro erreichen könnte.
Sind Makros bei Euch deaktiviert/verboten ?

Eine einzige Makrozeile würde die gefilterte Tabelle in ein anderes Blatt kopieren >>>
Sheets("OriginalBlatt").AutoFilter.Range.Copy Sheets("BlattFürKopie").Range("A1")

...damit es praxistauglich wird, kämen dann wohl noch ein paar Zeilen dazu...

Gruss Urs

 

Hi Urs,

du liegst schon sehr richtig damit, wie du es verstanden hast. Vielen Dank. Ich habe jetzt noch etwas gespielt – also mit Filter ist das Ergebnis erstmal zu erreichen.

Makros sind erlaubt, allerdings bin ich darin Anfänger – wohl nicht mehr lange

So wie du es beschrieben hast, sollen die Daten gefiltert werden und separat ausgewiesen werden. Da dies über 100 mal geschehen soll, wäre eine Automatisierung via Makro nicht schlecht. Ich habe mal versucht das ganze aufzuzeichnen – das Ergebnis ist entsprechend deiner Zeile.

Die Frage betrifft also nun die Automation: Kann ich Excel anweisen, per Wert ein neues Datenblatt anzulegen und die gefilterten Ergebnisse dort einzufügen? Wenn ja, wie würde so ein Makro aussehen? Sehr aufwendig?

Danke schon mal für deine Hilfe

 

Hallo Cmc

Das Erklären würde dauern... ich habe mal getestet und das Resultat hier angehängt.
Es ist eine normale XLS, Du musst nur die txt-Endung entfernen.

Starte das Ding mit dem Button, dann siehst Du ob es tut was Du willst.
Anpassen an Deine Datei dürfte kein Problem sein, es sind nur wenige Werte im Code zu verändern.
Alle Erklärungen sind in der Datei oder im Code.

Mit meinen 15 Konten (wohl Kostenstellen, Artikelnummern oder so etwas?) läuft der Code sehr schnell, bei Deiner grossen Tabelle musst Du ihm vielleicht etwas Zeit gönnen, er hat sehr viel zu tun.
Bei solchen Sachen ist nicht das Berechnen das Problem, sondern das Hin- und Her-Springen zwischen den Arbeitsblättern.

Gruss Urs

 

Hi Urs,

also jetzt bin ich echt sprachlos. VIELEN DANK für deine Mühe, es macht genau das was ich will und mit deiner Hilfe bekomme ich dass sogar noch 1A umgesetzt. Die Erläuterungen sind top. Echt vielen vielen Dank. Da macht Excel doch gleich noch mal doppelt so viel Spaß

Viele Grüße und Dank

cmc

 

Hi, also nachdem das so gut funktioniert, habe ich mich daran gemacht noch ein bisschen rumzuspielen Unter anderem habe ich versucht, das Makro so zu ändern, dass die Files nicht in Sheets gespeichert werden, sondern in neue Dateien. Leider hat sich der Erfolg nicht so ganz eingestellt, zwar wird das erste Workbook noch geöffnet, aber dann kommt der Fehler. Die Frage daher, mit welchem Code mach ich das am Besten? Außerdem scheint er die Anweisung "Filename :=" nicht zu kennen, wie muss man die definieren?

Und zu guter Letzt - ich will euch ja nicht ständig auf die Nerven gehen. Kann man wahlweise in Darmstadt oder Köln einen VBA Kurs machen, am Besten abends? Weil an der Uni hab ich bis jetzt nur die allg. Excel Kurse gesehen.

 

Hallo CmC

Ich habe das alte Makro an "Dateien-Erstellen" angepasst.
Kopiere es so wie es hier ist in meine Datei, kommentiere das alte Makro aus und... starten.

Code:

Option Explicit

Private Sub CommandButton1_Click()
'>>>neu
Dim rngOrig As Range, rngOrigData As Range, ws As Worksheet, wbk As Workbook
Dim rowTitOrig, colTitOrig, rowTitDetail, colTitDetail, colValue, colValueFilter, _
    shOrig, xCell, dblNummer, strPath, strPrefix, strRightWbk
        
        'diese 7 Werte musst Du dann an Deine Datei anpassen
'>>>alt
[COLOR="Red"]rowTitOrig = 16                 '= Nummer der Titelzeile im OriginalBlatt
colTitOrig = 1                  '= id. Spalte links aussen der Titelzeile... A=1, B=2, C=3...

rowTitDetail = 1                '= Nummer der Titelzeile im DetailBlatt
colTitDetail = 1                '= id. Spalte links aussen der Titelzeile

colValue = 1                    '= Spalte mit den zu bearbeitenden Werten in OrigTabelle
colValueFilter = 1              '= idem aber Spalte von links gezählt im Autofilter
                                '> hier beginnt der Autofilter in Spalte A > Wert also wie oben
shOrig = "OriginalTabelle"      '= Name des OriginalBlattes
'>>>neu
strPath = "C:\Temp\Detail\"     'Pfad zu Detaildateien, mit Backslash am Ende
strPrefix = "Detail-"           'Dateiname-Präfix vor der Nummer
                                'Name der Original-Datei mit dem Makro darf nicht so beginnenn[/COLOR]                                
'>>>alt
        'verhindert Bildschirmflackern während Ausführung
Application.ScreenUpdating = False
        'vermisst Originaltabelle mit Titel, die 1 ist Spalte A
Set rngOrig = Cells(rowTitOrig, colTitOrig).CurrentRegion
        'idem ohne Titelzeile
Set rngOrigData = rngOrig.Offset(1, 0).Resize(rngOrig.Rows.Count - 1, rngOrig.Columns.Count)
        'sortiert OrigigTabelle nach Spalte A
rngOrigData.Sort key1:=Columns(colValue)
'>>>neu
        'schliesst allfällig geöffnete Detail-Dateien, ohne Speichern
For Each wbk In Workbooks
    If Left(wbk.Name, Len(strPrefix)) = strPrefix Then wbk.Close False
Next
        'prüft ob Detail-Ordner vorhanden und erstellt ihn ggf.
        'kann nur den letzten Ordner erstellen, hier "Detail" - "c:\temp" muss existieren
If Len(Dir(strPath, vbDirectory)) = 0 Then
    MkDir strPath
End If
        'löscht alle Detail-Dateien im Detail-Ordner
On Error Resume Next
Kill strPath & strPrefix & "*"
On Error GoTo 0
        'erstellt alle Dateien neu entsprechend den vorhandenen Nummern
For Each xCell In rngOrigData.Columns(colTitOrig).Cells
    If xCell.Value <> dblNummer Then
        Set wbk = Workbooks.Add
        'Application.DisplayAlerts = False
        wbk.SaveAs Filename:=strPath & strPrefix & xCell.Value
        dblNummer = xCell.Value
    End If
Next
Set wbk = Nothing
        'setzt für jedes Detailblatt den entsprechenden Filter im OrigBlatt
        'und kopiert das Filtrat in die entsprechende Datei
For Each wbk In Workbooks
    If Left(wbk.Name, Len(strPrefix)) = strPrefix Then
        ThisWorkbook.Activate
        Sheets(shOrig).Select
        With Sheets(shOrig)
                'entfernt allf. gesetzten AutoFilter
            If .AutoFilterMode Then .Cells(rowTitOrig, colTitOrig).AutoFilter
                'filtert nach Spalte A > ist die 1. Spalte des Autofilters
                'Filterkriterium ist die Nummer im Dateinamen
            strRightWbk = Left(wbk.Name, InStr(1, wbk.Name, ".") - 1)
            strRightWbk = Right(strRightWbk, Len(strRightWbk) - Len(strPrefix))
            .Cells(rowTitOrig, colTitOrig).AutoFilter field:=colValueFilter, Criteria1:=strRightWbk
                'kopiert Filtrat in neue Datei
            .AutoFilter.Range.Copy _
                Workbooks(wbk.Name).Sheets("Tabelle1").Cells(rowTitDetail, colTitDetail)
                'entfernt Autofilter
            .Cells(rowTitOrig, colTitOrig).AutoFilter
        End With
    End If
Next

'>>>alt
        'wenn nötig wird OrigTabelle wieder zurücksortiert, hier zB nach Spalte D
rngOrigData.Sort key1:=Columns("D")
        'geht zurück zu OrigTabelle
Sheets("OriginalTabelle").Select
Application.ScreenUpdating = True

End Sub

- passe dann die roten Variablen Deinen Bedürfnissen an, der Rest läuft automatisch


Funktion >
- wenn nötig wird ein Detail-Ordner erstellt
- alte Detail-Dateien werden geschlossen und gelöscht
- dann wie vorher, aber jetzt in Dateien, nicht in Blätter gespeichert

Mit meinen nur 14 Konten braucht es wenige Sekunden, bei dir dann...?
> lass ihm etwas mehr Zeit, Dateien manipulieren dauert etwas länger als Arbeitsblätter...
> wenn das dann zu viele geöffnete Dateien ergibt, könnte man sie auch fortlaufend schliessen... aber erst willst Du doch sehen...
> wenn die alten Detail-Dateien erhalten bleiben sollen, kann man den Detail-Ordner umbenennen, zB indem man vor dem alten Ordnernamen das aktuelle Datum in den Namen setzt.


VBA-Kurse in Köln... ich wohne in der Schweiz...
Wobei... der beste Lehrmeister dürfte die Praxis sein. Persönlich hätte ich in einem Kurs wohl wenig gelernt.
Ueben, üben, üben... nur selbst gemachte Fehler prägen sich wirklich ein
Zudem hat man heute ja einen gewaltigen Vorteil > Google!
Wenn man dann wirklich auf dem Schlauch steht (oft nur das berühmte Brett), gibt es ja auch noch das Forum.

Gruss Urs

 

Hi Urs,

danke dir. Hat bei mir super geklappt. Auch von der Dauer her geht es echt schnell - also auch nur wenige Sekunden. Hätte ich nicht gedacht.

Ansonsten haste wohl recht, üben hilft Hatte ich bisher sonst auch immer so gemacht, aber ich dachte mit Hilfe könnts schneller gehen und ist evntl eleganter

In diesem Sinne, bis bald vielleicht Viele Grüße in die Schweiz

cmc