Liste ohne Duplikate aus Matrix,Zuordnung Elemente

Bild

Betrifft: Liste ohne Duplikate aus Matrix,Zuordnung Elemente
von: PeterPan
Geschrieben am: 01.10.2015 09:21:58

Hallo an alle Excel-Experten,
bisher hat mir dieses Forum schon sehr oft weitergeholfen, da ich bisherige Problemstellungen und entsprechende Lösungen hier mindestens in Teilen wiedergefunden habe. Vielen Dank dafür an die vielen Freiwilligen und Experten!
Doch jetzt ist es soweit, ich komme nicht mehr weiter. Ich habe folgende Aufgabenstellung / Herausforderung:
In einer Matrix (Spalte A: Liste aller „Bauteile“ ohne Duplikate; Zeile 1: Liste/Überschriften „Intervalle“ von 1 bis X ohne Duplikate) befinden sich Zellen, die leer sind oder aber mit Text gefüllt („Tätigkeitsbeschreibungen“). Die Zeichenlänge in den Zellen kann größer 256 sein. Der Text kann sich in verschiedenen Zellen der Matrix wiederholen (mit Duplikaten).
a) Ich benötige eine Auflistung aller Tätigkeitsbeschreibungen ohne Duplikate.
=WENN(ZEILE()>SUMME(N(A$1:E$6<>""));"";INDEX(A:E;REST(KKLEINSTE(WENN(A$1:E$6<>""; ZEILE($1:$6)+SPALTE(A:E)*999);ZEILE());999);KÜRZEN(KKLEINSTE(WENN(A$1:E$6<>"";ZEILE($1:$6) +SPALTE(A:E)*999);ZEILE())/999)))
Die dargestellte Formel liefert mir allerdings Duplikate. Außerdem muss die Matrix in A1 beginnen (d.h. Überschrift „Intervalle“ bzw. Spalte „Bauteile“ ist nicht möglich).
b) Jeder Tätigkeitsbeschreibung, die ja mehrfach vorkommen kann, in der erzeugten Liste sollen genau die Bauteile aus Spalte A, die in einer der Spalten die Tätigkeitsbeschreibung beinhaltet, daneben als Aufzählung dargestellt werden.
Zur Veranschaulichung habe ich eine Exceldatei beigefügt.
https://www.herber.de/bbs/user/100518.xlsx
Sollte die Aufgabe deutlich geworden sein, wäre ich für Unterstützung sehr dankbar! Ich bin gespannt.
Grüße
PeterPan

Bild

Betrifft: AW: Liste ohne Duplikate aus Matrix,Zuordnung Elemente
von: ChrisL
Geschrieben am: 01.10.2015 09:52:53
Hi Peter
Eine VBA Lösung:

Sub t()
Dim rng As Range, Zelle As Range
Dim letzteZeile As Long, FindZeile As Long
Set rng = Range("A1:E5")
Columns("G:H").ClearContents
For Each Zelle In rng.Cells
    If Not IsEmpty(Zelle) Then
        letzteZeile = Range("G65536").End(xlUp).Row + 1
        If WorksheetFunction.CountIf(Columns(7), Zelle) = 0 Then
            If IsEmpty(Range("G1")) Then letzteZeile = 1
            Cells(letzteZeile, 7) = Zelle
            Cells(letzteZeile, 8) = Cells(Zelle.Row, 6)
        Else
            FindZeile = Application.Match(Zelle, Columns(7), 0)
            Cells(FindZeile, 8) = Cells(FindZeile, 8) & ", " & Cells(Zelle.Row, 6)
        End If
    End If
Next Zelle
End Sub

cu
Chris

Bild

Betrifft: AW: Liste ohne Duplikate aus Matrix,Zuordnung Elemente
von: PeterPan
Geschrieben am: 01.10.2015 11:46:16
Hallo Chris,
wow, chapeau! Sehr schnell und passt auf die beigefügte Datei. Ich ziehe meinen Hut, tausend Dank! Ich sollte meine VBA-Kenntnisse vertiefen...
Ich versuche dies jetzt auf die "richtige" Datei anzuwenden und melde mich noch einmal.
Gruß
Peter

Bild

Betrifft: AW: Liste ohne Duplikate aus Matrix,Zuordnung Elemente
von: PeterPan
Geschrieben am: 01.10.2015 12:07:42
Hallo Chris,
leider ist mein Versuch gescheitert, da ich den veränderten Matrixbereich im Makro wohl falsch erweitert habe. Ich habe die "richtige" Datei einmal hochgeladen.
https://www.herber.de/bbs/user/100523.xlsm
Was habe ich falsch gemacht?
Danke vorab für die super Unterstützung!
Gruß
PeterPan

Bild

Betrifft: AW: Liste ohne Duplikate aus Matrix,Zuordnung Elemente
von: ChrisL
Geschrieben am: 01.10.2015 12:53:18
Hi Peter
Etwa so...

Sub t()
Dim rng As Range, Zelle As Range
Dim letzteZeile As Long, FindZeile As Long
Set rng = Range("D2:AJ500")
Range("AL2:AM65536").ClearContents
For Each Zelle In rng.Cells
    If Not IsEmpty(Zelle) Then
        letzteZeile = Range("AL65536").End(xlUp).Row + 1
        If WorksheetFunction.CountIf(Columns(38), Zelle) = 0 Then
            Cells(letzteZeile, 38) = Zelle
            Cells(letzteZeile, 39) = Cells(Zelle.Row, 1)
        Else
            FindZeile = Application.Match(Zelle, Columns(38), 0)
            Cells(FindZeile, 39) = Cells(FindZeile, 39) & ", " & Cells(Zelle.Row, 1)
        End If
    End If
Next Zelle
End Sub

cu
Chris

Bild

Betrifft: AW: Liste ohne Duplikate aus Matrix,Zuordnung Elemente
von: PeterPan
Geschrieben am: 01.10.2015 13:18:57
Hallo Chris,
danke, ich habe es soeben ausprobiert.
Kann es sein, dass in Spalte AL doch noch Duplikate auftreten bzw. diese nicht die Bauteile zusammenfassen? Ich habe Dir dies einmal farbig hervorgehoben.
https://www.herber.de/bbs/user/100527.xlsm
Bei genauerer Betrachtung wäre diese Darstellung sogar besser. D.h. nicht alle Bauteile zu einer Tätigkeit in Spalte AM hintereinander auflisten, sondern für jedes Bauteil eine neue Zeile. z.B. so:
Spalte AL Spalte AM
Tätigkeit 1 Bauteil 1
Tätigkeit 1 Bauteil 2
Tätigkeit 1 Bauteil 3
Tätigkeit 2 Bauteil 1
Tätigkeit 2 Bauteil 4
etc.
Gruß
PeterPan

Bild

Betrifft: AW: Liste ohne Duplikate aus Matrix,Zuordnung Elemente
von: Rusi Maintaire
Geschrieben am: 01.10.2015 13:49:33
Hallo,
schreibt in ein neues Blatt.

Sub aaa()
  Dim vArr, arrTmp, i As Long, j As Integer, k As Integer, objDaten As Object, oDaten, arrOut()
  vArr = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 36)
  Set objDaten = CreateObject("Scripting.dictionary")
  Const sDelim As String = "|"
  objDaten("Bauteil" & sDelim & "Tätigkeit") = 0
  For i = 1 To UBound(vArr)
    For j = 4 To UBound(vArr, 2)
      If Len(Trim(vArr(i, j))) Then
        arrTmp = Split(vArr(i, j), vbLf)
        For k = 0 To UBound(arrTmp)
          objDaten(vArr(i, 1) & sDelim & arrTmp(k)) = 0
        Next k
      End If
    Next
  Next
  ReDim arrOut(1 To objDaten.Count, 1 To 2)
  i = 0
  For Each oDaten In objDaten
    arrTmp = Split(oDaten, sDelim)
    If Len(arrTmp(1)) Then
      i = i + 1
      arrOut(i, 1) = arrTmp(0)
      arrOut(i, 2) = arrTmp(1)
    End If
  Next
  With Worksheets.Add
    .Cells(1, 1).Resize(i, 2) = arrOut
    .Columns.AutoFit
  End With
End Sub
Gruß
Rudi

Bild

Betrifft: besser Rusi als Rosi owT
von: Rudi Maintaire
Geschrieben am: 01.10.2015 14:28:23


Bild

Betrifft: AW: Liste ohne Duplikate aus Matrix,Zuordnung Elemente
von: PeterPan
Geschrieben am: 01.10.2015 14:33:30
Hallo Rudi,
perfekt, geht nicht besser! An dieser Stelle Dir und Chris noch einmal ausdrücklich danke!!!
In meinem nächsten Leben versuche ich dann den Code zu verstehen... ;-)
Gruß
Peter Pan

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Liste ohne Duplikate aus Matrix,Zuordnung Elemente"