Gegevens uit meerdere kolommen halen en combineren in 1 cel

[stefanvan]

Ik ben een werkgroepenindeling aan het maken, hierbij heb ik als input een tabel met verticaal alle werkgroepen en horizontaal alle leden die ingedeeld worden (tabel links).
Uiteindelijk wil ik per persoon kunnen zien in welke werkgroepen ze zitten, door een tweede tabel te genereren waarbij de leden verticaal komen te staan, en in de tweede kolom een opsomming van de werkgroepen waar ze in zitten (voorbeeld tabel rechts).

Ik heb geprobeerd dit te verwezenlijken door de FILTER functie te gebruiken, uiteindelijk lukt dit, maar dit werkt niet icm een tabel door het overlopen van de FILTER functie, en het wordt een zeer foutgevoelige en omslachtige methode.

Heeft een van jullie misschien een betere methode?

[Tk55]

Probeer een pivot table (draaitabel)!

[g0tanks]

Tk55 schreef op donderdag 25 juli 2024 @ 22:32:
Probeer een pivot table (draaitabel)!

Dat gaat in dit geval niet werken gezien de structuur van de tabel.

@stefanvan heeft een werkgroep altijd maximaal 4 leden zoals in jouw voorbeeld?

[stefanvan]

@g0tanks Deze tabellen zijn een voorbeeld, in mijn eigenlijke tabellen gaat het tot maximaal 10 leden, meer zal het praktisch gezien ook niet worden.

[ Voor 3% gewijzigd door stefanvan op 25-07-2024 22:48 ]

[Patrick_6369]

De makkelijkste oplossing is je brondata anders structuren. Dan ben je echt zo klaar:

[dixet]

Je zou dit ook op kunnen lossen via Data -> From Table/Range. Daarmee krijg je de tabel in PowerQuery en kan je alle daar beschikbare transformaties toepassen.

Met deze powerquery

code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Werkgroep", type text}, {"Lid 1", type text}, {"Lid 2", type text}, {"Lid 3", type text}, {"Lid 4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Werkgroep"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Naam"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Naam", "Werkgroep", "Attribute"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Naam"}, {{"Group1", each _, type table [Naam=text, Werkgroep=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Group1],"Werkgroep")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Values",{{"Custom", "Werkgroepen"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Group1"})
in
    #"Removed Columns1"

Krijg ik dit resultaat:



Wat ik hier doe is

1. Unpivot je tabel
2. Overbodige kolommen weggooien
3. Groeperen op naam. Hierdoor ontstaat 1 kolom met een tabel als inhoud. In die tabel zitten alle werkgroepen bij die naam
4. Custom column maken op basis van de kolom
5. Deze kolom uitpakken met een komma als delimiter
6. Kolom waarin de complete tabel staat weggooien

Deze methode lijkt complex maar blijft werken, ook als je werkgroepen meer da 4 leden kunnen krijgen of er meer namen bij komen

[stefanvan]

@dixet het was even puzzelen, maar inderdaad precies hoe ik het wil hebben, deze ga ik toepassen op mijn werkgroepenindeling. Super bedankt voor het meedenken allemaal!

[dix-neuf]

Stefan,
Als in je huidige weergave in rij 1 in plaats van Lid 1, Lid 2, enz. de werkelijke namen van de leden staan (dus: Nel, Jan, Piet, Klaas, enz.), dan is ook een oplossing zoals in de afbeelding hieronder mogelijk.
Is dat een optie?

[stefanvan]

@dix-neuf klopt inderdaad, zo zou het ook opgelost kunnen worden. Alleen wordt de invoer van de indeling van de werkgroepen minder inzichtelijk, maar met de eerder genoemde oplossing ben ik ook geholpen. Bedankt in ieder geval om ook mee te denken!

[dix-neuf]

Die indeling leek mij er nou juist overzichtelijker door te worden, maar dat zie ik blijkbaar verkeerd.
Voor het geval je het nog zou willen vergelijken/gebruiken:
Uitgaande van de tabel in de bovenste afbeelding op Blad1, verkrijg je door onderstaande macro uit te voeren, de tabel in de onderste afbeelding op Blad2.

'

code:

Sub macro1()
Dim lk As Integer, lr As Integer, m As Integer, nstr As String, x As Integer, y As Integer
Sheets("Blad2").Cells.ClearContents
Application.ScreenUpdating = False
With Sheets("Blad1")
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range(.Cells(2, 1), .Cells(lr, 1)).Copy
Sheets(2).Cells(1, 2).PasteSpecial Paste:=xlValues, Transpose:=True
lk = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(1, 2), .Cells(1, lk)).Copy
End With
With Sheets("Blad2")
.Cells(1, 1).Value = "Naam"
.Cells(2, 1).PasteSpecial Paste:=xlValues, Transpose:=True
Application.Goto Sheets("Blad2").Cells(1, 1)
End With
With Sheets("Blad1")
For x = 2 To lk
For y = 2 To lr
If IsEmpty(.Cells(x, y)) = False Then
nstr = .Cells(x, y).Value
m = WorksheetFunction.Match(nstr, Sheets("Blad2").Range(Cells(1, 1), Cells(lk, 1)), 0)
Sheets("Blad2").Cells(m, x).Value = "x"
End If
Next y, x
End With
Sheets("Blad2").Range(Cells(2, 1), Cells(lk, lr)).Sort key1:=Sheets("Blad2").Cells(2, 1)
Application.ScreenUpdating = True
End Sub

[stefanvan]

Mij is het overzichtelijker op de huidige manier, zo kan ik ook eenvoudig zien of er nog plekken gevuld moeten worden in een bepaalde werkgroep dmv voorwaardelijke opmaak op basis van het minimaal aantal leden (wit/grijs). Verder is Lid 1 in de lijst ook automatisch de kartrekker van de werkgroep, wat minder handig aan te geven is op basis van kruisjes. Maar natuurlijk is voor iedere toepassing weer een andere manier handig.
En het is een lijst van 50 personen wat het misschien onoverzichtelijk zou kunnen maken.

[ Voor 5% gewijzigd door stefanvan op 29-07-2024 19:34 ]

[dix-neuf]

Dank voor je reactie.
Je moet vanzelfsprekend díe oplossing kiezen die je zelf het beste vindt; jíj moet er immers mee werken.
Succes ermee!