Namen leerlingen achter genoemde planeten

[mulderwme]

Hallo allemaal,

Ik ben aan't stoeien met dynamische bereiken en de nieuwe functies die Excel daarvoor heeft gelanceerd.
In het voorbeeld vind je een lijstje van 3 fictieve leerlingen die een aantal planeten hebben genoemd.
Voor het uitsplitsen van de planeten heb ik een nieuwe functie gebruikt.
Nu wil ik nog de namen van de leerlingen achter een planeet hebben.

Wie kan mij daarmee helpen?

Alvast bedankt.
Willem

 

[peter59]

Ik heb het niet opgelost met de "nieuwe" functies maar met power query.

 

[mulderwme]

Mooie oplossing, Peter, maar in dit verband niet te gebruiken.

Nog meer ideeën? Misschien wel iets met index en vergelijken, maar daar kwam ik ook niet echt ui.

 

[peter59]

Uhm? In welk verband niet?

 

[AlexCEL]

Ik ben maar een beetje aan het hobbyen gegaan. Zoiets wellicht?

=LET(x;TRANSPONEREN(TEKST.SPLITSEN(TEKST.COMBINEREN(", ";1;SUBSTITUEREN(B2:B4;", ";" "&A2:A4&", ")&" "&A2:A4);", "));HOR.STAPELEN(TEKST.VOOR(x;" ");TEKST.NA(x;" ")))

Of met de benoemde bereiken:

=LET(x;TRANSPONEREN(TEKST.SPLITSEN(TEKST.COMBINEREN(", ";1;SUBSTITUEREN(tblPlaneten[Genoemde planeten];", ";" "&tblPlaneten[Leerling]&", ")&" "&tblPlaneten[Leerling]);", "));HOR.STAPELEN(TEKST.VOOR(x;" ");TEKST.NA(x;" ")))

 

[emields]

als het ook met een macro mad?

 

[mulderwme]

Ja, dit is wel dé oplossing, AlexCEL! Hier was ik naar op zoek. Dat heb je snel voor elkaar gekregen.

Ik zie dat de variant NAAR.KOLOM() ook werkt. Het is wel mooi allemaal, maar het wordt er niet eenvoudiger op.

Heel erg bedankt!

groet,
Willem

 

[snb]

Je probeert met een formule een onjuiste struktuur van gegevens 'op te lossen'.

 

[mulderwme]

Dat klopt wel, snb. Maar op dit moment rollen de gegevens er nog op deze manier uit. Het is foutgevoelig, merk ik nu ook als er extra spaties in velden staan. Bijv. als er Grote Beer staat. Dan is de planeet "Grote" en de leerling is ineens "Beer Frank".

 

[peter59]

Bijv. als er Grote Beer staat. Dan is de planeet "Grote" en de leerling is ineens "Beer Frank".

Met power query geen probleem.
Probeer maar eens met de bijlage van # 2.
Het geheel is ook dynamisch. M.a.w., als je alle bekende planeten doet invoeren dan krijg je ze ook allemaal te zien.
Een nadeeltje, je dient na het invoeren van nieuwe gegevens altijd in de tabel welke uit power query rolt op verversen te klikken.

 

[mulderwme]

als het ook met een macro mad?

Met een macro werkt het ook. Mooie oplossing. Nu is ook het probleem van Grote Beer opgelost.

De Maan is officieel geen planeet. Maar het zijn nog maar leerlingen, hè. Ze noemen ook Grote Beer.

Wel zou ik het interessant vinden om te zien of ook het probleem met Grote Beer met een formule is op te lossen. Gewoon als uitdaging.

Groet,
WIllem

 

[peter59]

De leerlingen in groep 6 al beduvelen?

 

[mulderwme]

De leerlingen in groep 6 al beduvelen?

Nee nee, zij moeten de planeten opnoemen die ze kennen. Ik zou niet durven om ze te beduvelen.

 

[AlexCEL]

Aangepaste formule, houdt rekening met spaties:

=LET(x;TRANSPONEREN(TEKST.SPLITSEN(TEKST.COMBINEREN(", ";1;SUBSTITUEREN(SUBSTITUEREN(tblPlaneten[Genoemde planeten];" ";"@");",";" "&tblPlaneten[Leerling]&", ")&" "&tblPlaneten[Leerling]);", "));HOR.STAPELEN(SPATIES.WISSEN(SUBSTITUEREN(TEKST.VOOR(x;" ");"@";" "));SPATIES.WISSEN(SUBSTITUEREN(TEKST.NA(x;" ");"@";" "))))

 

[mulderwme]

De uitwerking van AlexCEL lost het vraagstuk op. De macro was ook een mooie oplossing, emields.

Ziet iemand misschien nog kans om "Punten" ook in de formule mee te nemen?
In de bijlage zit het bestand Groep 6 - Planeten (AC) V02.xlsx.

In G2 heb ik de formule X.Zoeken() gebruikt. Is het mogelijk om die in E2 in te bouwen? Er zitten zoveel Substitueren en SPATIES.WISSEN in, dat geen idee heb waar ik de x.zoeken zou moeten neerzetten.

Groet,
Willem

 

[snb]

Waarom verplaats je kolom C niet naar kolom M; en vervolgens splits kolom B in afzonderlijke kolommen (Texttocolumns)

 

[peter59]

Ach ja.
Hoe eenvoudig kan het (vaak) zijn met power query.

 

[mulderwme]

Tja, wie weet, peter59, misschien komt dat er nog een keer van. We laten de moed niet zakken.

 

[Warme bakkertje]

Ik weet dat je een formuleoplossing prefereert maar toch maar even meegeven.

Sub tst()
    Dim planets() As Variant, _
        AvPlanets As Long, _
        x As Long, i As Long, j As Long
        
    AvPlanets = 5: x = 1
    With Sheet1
        sn = .ListObjects("tblPlaneten").DataBodyRange.Value
        ReDim planets(1 To UBound(sn) * AvPlanets, 1 To 3)
        For i = 1 To UBound(sn)
            For j = 0 To UBound(Split(sn(i, 2), ","))
                planets(x, 1) = Trim(Split(sn(i, 2), ",")(j))
                planets(x, 2) = sn(i, 1)
                planets(x, 3) = sn(i, 3)
                x = x + 1
            Next
        Next
        .Range("E2").Resize(UBound(planets), 3) = planets
    End With
End Sub

 

[snb]

of gewoon:

Sub M_snb()
  sn = Sheet1.ListObjects(1).Range
  
  With CreateObject("scripting.dictionary")
    .Item(.Count) = Array(sn(1, 1), sn(1, 2), sn(1, 3))
    
    For j = 2 To UBound(sn)
      For jj = 0 To UBound(Split(sn(j, 2), ","))
         .Item(.Count) = Array(sn(j, 1), Split(sn(j, 2), ", ")(jj), sn(j, 3))
      Next
    Next
    
    Sheet1.Cells(1, 5).Resize(.Count, 3) = Application.Index(.items, 0, 0)
  End With
End Sub