Vertikaal zoeken met verschillende waarden en uniek maken resultaten

[Peter B]

Hallo,

Ik heb een sheet maar meerde tabbladen, waarvan er 2 relevant zijn voor mijn vraag. Zie ook bijgesloten voorbeeldbestand. Ik gebruik Excel 2010.

Ik heb een lijst bevindingen (tab 2). Bij deze bevindingen is aangegeven op welke interfaces deze bevindingen zitten. Deze bevindingen zijn van toepassing voor verschillende ketens voor 2 verschillende types; KA en KB.

Daarnaast heb ik een lijst interfaces die zijn gekoppeld aan ketens (tab 1). Interfaces kunnen in 1 of meer ketens voorkomen. Interfaces kunnen voorkomen voor beide types, maar ook voor 1 van beide types.

Ik wil achter de lijst bevindingen de (uniek gemaakte) ketens per type opnemen. In het voorbeeldbestand heb ik 5 interfaces met 5 bevindingen. In de praktijk gaat het om beduidend grotere aantallen. Niet echt fijn om met de hand te doen.

Heeft iemand ideeën/ suggesties? Waarbij de opzet bij voorkeur niet op zijn kop wordt gezet. VBA of formules maakt mij niet uit. Hulpkolommen/ tabbladen geen bezwaar ...

Peter B

 

[Leotaxi]

Dat wordt een hele uitdaging maar mag ik weten hoeveel interfaces er in totaal zijn en hoeveel
er maximum in een bevinding kunnen komen ?

mvg
Leo

 

[Peter B]

Ik ga uit van 60-80 interfaces en max. 10 interfaces per bevinding (ik heb ook nog een categorie algemeen ...)

 

[snb]

Ik gebruik deze UDF:

Function F_snb(c00,y)
    sn = Split(c00, ",")
    sp = Sheets("Interfaces").UsedRange
    For j = 0 To UBound(sn)
       c01 = c01 & "," & sp(1 + Val(sn(j)), y)
    Next
    F_snb = Mid(c01, 2)
End Function

 

[Peter B]

Dank snb voor je UDF. Ik ben met je opzet aan het stoeien geweest om te begrijpen wat hier precies gebeurd. En hoe ik dit aan kan passen om de ketens uniek te maken (en bij voorkeur gesorteerd), maar dat krijg ik niet voor elkaar omdat feitelijk de gevonden strings uit "sp" aan elkaar worden geplakt. Volgens mij zouden de gevonden strings met de Split-functie moeten worden opgedeeld in nummers in een derde matrix, en zou vervolgens per interface moeten worden bekeken of het nieuw gevonden nummer al bestaat. Vervolgens kunnen dan de waarden in deze matrix aan elkaar worden geknoopt tot een string die dan als resultaat in de cel wordt geplaatst. En dan dus bij voorkeur gesorteerd ...

Zou je mij hierbij ook kunnen helpen?

 

[snb]

Function F_snb(c00,y)
    sn = Split(c00, ",")
    sp = Sheets("Interfaces").UsedRange

    For j = 0 To UBound(sn)
       c01 = c01 & "," & sp(1 + Val(sn(j)), y)
    Next
    sn=split(mid(c01,2),",")

    With CreateObject("System.Collections.ArrayList")
       For Each it In sn
         If Not .contains(it) Then .Add it
       Next
       
       .Sort
       F_snb = Join(.toarray(),",")
    End With
End Function

 

[Peter B]

Geniaal!

Ik heb (minor detail) de "End Sub" vervangen voor "End Function" en ik heb de eerste "," er af gehaald door toevoeging "F_snb = Mid(F_snb, 2)" voor de "End Function". Ik heb nu nog één laatste wens en dat is een numerieke sortering. Hij sorteert nu bv. als volgt: 1,29,3,6,7,9.

 

[snb]

Daar kun je vast wel zelf iets voor verzinnen....

 

[Peter B]

1,2,3 lukte het niet. Maar dan ga ik er maar een 4,5,6 aan wagen ...

 

[Peter B]

Ik ben er uit. Alleen begreep ik niet waarom ik niet van de eerste komma af kwam. Totdat ik door kreeg dat niets door Val wordt vertaald naar 0. Dus alle ketens begonnen met 0,.

Anyway. Hierbij de finale code. Met heel veel dank aan snb!

Function F_ZoekKetens(c00, y)
    sn = Split(c00, ",")
    sp = Sheets("Interfaces").UsedRange

    For j = 0 To UBound(sn)
       c01 = c01 & "," & sp(1 + Val(sn(j)), y)
    Next
    sn = Split(c01, ",")

    With CreateObject("System.Collections.ArrayList")
       For Each it In sn
'         If Not .contains(it) Then .Add it
         If Not .contains([B]Val[/B](it)) Then .Add [B]Val[/B](it)
       Next

       .Sort
       F_ZoekKetens = Join(.toarray(), ",")
    End With
    Debug.Print F_ZoekKetens
    F_ZoekKetens = Mid(F_ZoekKetens, [B]3[/B])
End Function

 

[snb]

Muy bien !

Ik had hem zo (extra komma bij de bron verwijderd)
:

Function F_snb(c00,y)
    sn = Split(c00, ",")
    sp = Sheets("Interfaces").UsedRange

    For j = 0 To UBound(sn)
       c01 = c01 & "," & sp(1 + Val(sn(j)), y)
    Next
    sn=split(mid(c01,2),",")

    With CreateObject("System.Collections.ArrayList")
       For Each it In sn
         If Not .contains(val(it)) Then .Add val(it)
       Next
       
       .Sort
       F_snb = Join(.toarray(),",")
    End With
End Function