Formel für Auflistung

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
Bild

Betrifft: Formel für Auflistung
von: Rainer
Geschrieben am: 06.09.2015 12:29:44

Hallo,
habe über eine Matrix zur Produktprüfung per VBA ein automatisches Ausblenden der Spalte, wenn ein bestimmtes Kriterium nicht zutrifft.
Nun würde ich gerne auf einem anderen Arbeitsblatt die Produkte, welche nun noch den Kriterien entsprechen auflisten.
Als Formel hatte ich "TEILERGEBNIS" im Sinn, was aber wohl nur für eine Auswertung, nicht aber für die Inhalte selbst funktioniert.
Gibt es eine Formel zur Auflistung oder muss ich in Tabelle1, in welcher die Produkte enthält ein Zusatzfeld einrichten?
Vielen Dank für Eure Mithilfe.
Gruß Rainer
Beispiel: https://www.herber.de/bbs/user/100041.xlsx

Bild

Betrifft: AW: Formel für Auflistung
von: Daniel
Geschrieben am: 06.09.2015 12:52:34
HI
Teilergebnis wäre schon der richtige Ansatz, eine zusätzliche Zeile würde das Formelwerk auch tatsächlich vereinfachen, aber das Problem ist, dass Teilergebnis nur das Ausblenden von Zeilen erfasst, aber nicht das Ausblenden von Spalten.
wenn du mit Formeln weiter arbeiten willst, müsstest du also in der ersten Tabelle die Anordnung ändern, so dass die Werte untereinander stehen und nicht nebeneinander.
gruss Daniel

Bild

Betrifft: AW: Formel für Auflistung
von: Rainer
Geschrieben am: 06.09.2015 13:14:27
Hallo Daniel,
vielen Dank für die schnelle Antwort.
Gerade die unterschiedliche Anordnung ist hier das Ziel.
In Tabelle1 sind alle Informationen zu einem Produkt. In einer weiteren Tabelle möchte ich dann der Darstellung wegen nur die Produkte auflisten, welche die Kriterien erfüllt haben.
Mit einer Zusatzzeile komme ich dann vermutlich selbst zurecht.
Habe mir in den vergangenen Zeiten etliche Formeln offline abgespeichert.
Wenn es alternativ eine VBA-Lösung gibt, wäre ich hierfür natürlich auch offen.
Gruß Rainer

Bild

Betrifft: AW: Formel für Auflistung
von: Daniel
Geschrieben am: 06.09.2015 13:23:53
HI
mit VBA kannst du einfach kopieren und transponiert einfügen:

Sheets("Tabelle1").Range("F6:K6").SpecialCells(xlCellTypeVisible).Copy
Sheets("Tabelle2").Range("f8").PasteSpecial Paste:=xlValues, Transpose:=True
die ausgeblendeten Spalten werden durch die Angabe in den .SpecialCells übersprungen.
ist halt nicht dynamisch wie Formeln, dh das Makro muss erneut ausgeführt werden, wenn du was in der Tabelle1 änderst.
Gruss Daniel

Bild

Betrifft: AW: Formel für Auflistung
von: Rainer
Geschrieben am: 06.09.2015 15:18:09
Hallo Daniel,
danke, das passt; da ich auch in Tabelle1 via VBA die Auswahl treffe, hänge ich die Formel für Tabelle2 einfach hinten an.
Gruß Rainer

Bild

Betrifft: AW: Formel für Auflistung
von: Rainer
Geschrieben am: 06.09.2015 15:41:53
Hallo,
muss nochmals auf den Code zurückkommen.
Habe vergessen, dass ich in meiner Original-Tabelle je Produkt eine Hilfsspalte habe, so dass ich nur jede zweite Spalte auslesen möchte. Geht das auch?
Beim Ausblenden habe ich in der Schleife iCol + 1.
Zur Orientierung den ganzen Code:

  If Me.CheckBox1.Value = True Then
    Application.ScreenUpdating = False
  
      Columns("I:EZ").Hidden = False
      
      For lngZeile = 1 To Range("G" & Rows.Count).End(xlUp).Row
        If Range("G" & lngZeile) = True Then
          For iCol = 9 To 172
            If Cells(lngZeile, iCol).Value = "L" Then
              Columns(iCol).Hidden = True
              Columns(iCol + 1).Hidden = True
            End If
            iCol = iCol + 1 'nur jede zweite Spalte prüfen
          Next iCol
        End If
      Next
      
'https://www.herber.de/forum/archiv/1444to1448/t1446241.htm
      Tabelle2.Range("F7:G122").Clear
      'Firma
      Tabelle1.Range("J6:EZ6").SpecialCells(xlCellTypeVisible).Copy
      Tabelle2.Range("F7").PasteSpecial Paste:=xlValues, Transpose:=True
      'Produkt
      Tabelle1.Range("J7:EZ7").SpecialCells(xlCellTypeVisible).Copy
      Tabelle2.Range("H7").PasteSpecial Paste:=xlValues, Transpose:=True
      
    Application.ScreenUpdating = True
    
  End If
Gruß Rainer

Bild

Betrifft: AW: Formel für Auflistung
von: Daniel
Geschrieben am: 06.09.2015 15:45:50
hi
geht sicherlich
aber dann isses vielleicht einfacher, auch die Daten gleich per Schleife zu übertragen.
oder du blendest vor dem kopieren die Hilfsspalten auch gleich mit aus.
Gruss Daniel

Bild

Betrifft: AW: Formel für Auflistung
von: Rainer
Geschrieben am: 06.09.2015 16:09:54
Hallo Daniel,
"aber dann isses vielleicht einfacher, auch die Daten gleich per Schleife zu übertragen."

habe ich paralell gerade versucht, bekomme zwar die Einträge, habe aber auch nur jede zweite Zeile einen Eintrag.
Gruß Rainer

Bild

Betrifft: AW: Formel für Auflistung
von: Daniel
Geschrieben am: 06.09.2015 16:12:39
HI
du musst halt den Spaltenzähler um 2 erhöhen, den Zeilenzähler aber um 1 (und das auch nur dann, wenn die Spalte sichbar ist)
Gruss Daniel

Bild

Betrifft: AW: Formel für Auflistung
von: Rainer
Geschrieben am: 06.09.2015 16:55:57
Hallo,
(und das auch nur dann, wenn die Spalte sichbar ist)
Habe nun zwar jede zweite Zeile, aber eben nur (wie von Dir schon vorhergesehen) alle Inhalte.

iCol2 = 9
              'Beschriftung Tabelle2
              Tabelle2.Cells(iCol2, 6).Value = Tabelle1.Cells(6, iCol + 1)
              iCol2 = iCol2 + 1
Können es aber auch bei Deiner ersten Variante von Dir belassen.
Kann ja einfach das Arbeitsblatt vorher formatieren und jede zweite (leere) Zeile ausblenden.
Gruß Rainer

Bild

Betrifft: AW: Formel für Auflistung
von: Daniel
Geschrieben am: 06.09.2015 17:10:04
Hi
du musst dann auch noch prüfen, ob die Spalte ausgeblendet ist oder nicht:

IF Tabelle1.Columns.(iCol + 1).hidden = False Then

Gruss Daniel

Bild

Betrifft: AW: Formel für Auflistung
von: Rainer
Geschrieben am: 06.09.2015 17:52:21
Hallo Daniel,
abschließend vielen Dank für Deine Mühe.
Habe es nun in einer eigenen Schleife wie folgt gelöst:

Dim iCol2 As Integer
iCol2 = 8
Tabelle2.Range("F8:H156").Clear
      
      For iCol = 9 To 156
        If Columns(iCol).Hidden = False Then
          Tabelle2.Cells(iCol2, 6).Value = Tabelle1.Cells(6, iCol + 1)
          Tabelle2.Cells(iCol2, 8).Value = Tabelle1.Cells(7, iCol + 1)
          iCol = iCol + 1
          iCol2 = iCol2 + 1
        End If
      Next iCol

Gruß Rainer

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formel für Auflistung"