XL 2019 Transposer des données d'une feuille vers une autre sous forme de synthèse

[Marc.A.S.]

Bonjour
Dans une feuille nommée "Base", J'ai des données dans un tableau
Colonne 1 les noms des personnes
Colonne 2 le nom du dossier traité
Colonne 3 le statut du dossier
Colonne 4 le commentaire du dossier

Chaque personne peut avoir traité plusieurs dossiers et toutes n'ont pas le même nombre de dossier (ça peut monter à 100 dossiers pour certains)

Dans une feuille "Synthèse", je voudrais ne retrouver qu'une seule ligne par personne, avec tous les dossiers traités sur la même ligne avec les statuts et les commentaires associés.

J'ai réussi facilement à avoir une liste avec la fonction unique, mais impossible d'arriver à afficher tous les dossiers de chaque personne dans les colonnes suivante.

Voir fichier joint pour comprendre, c'est assez simple dans l'esprit, mais je bute sur la formule pour y parvenir

Merci pour votre aide

 

[sylvanu]

Bonjour Marc,
Avec une centaine de dossiers potentiels par acteurs, c'est peut être plus simple de passer par du VBA, le fichier sera plus simple à gérer, et moins lourd.
De plus les formules seront réévaluées à chaque recalcul, avec du VBA elle ne s'exécute qu'une fois.
Un essai en PJ avec :

Sub Worksheet_Activate()
    Dim DL%, Tablo, Sortie, Ligne%, Colonne%, L%, Nom$, i%
    DL = [A1000].End(xlUp).Row
    [B2:ZZ1000].ClearContents
    Tablo = Sheets("Base").[A1].CurrentRegion
    ReDim Sortie(1 To DL, 1 To 1000)
    Ligne = -1
    For L = 1 To DL
        Ligne = Ligne + 1: Colonne = 1
        Nom = Cells(L, "A")
        For i = 2 To UBound(Tablo)
            If Tablo(i, 1) = Nom Then
                Sortie(Ligne, Colonne) = Tablo(i, 2)
                Sortie(Ligne, Colonne + 1) = Tablo(i, 3)
                Sortie(Ligne, Colonne + 2) = Tablo(i, 4)
                Colonne = Colonne + 3
            End If
        Next i
    Next L
    [B2].Resize(UBound(Sortie, 1), UBound(Sortie, 2)) = Sortie
    Columns.AutoFit
End Sub

La macro est automatique, il suffit de sélectionner la feuille Synthèse pour qu'elle s'exécute.

 

[alexga78]

Bonjour à tous,

une solution Power Query.

let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = {"Nom Dossier", "Statut Dossier", "Commentaire Dossier"},
C = Table.Combine(Table.Group(A, "Noms", {"x", each
 [
 a  = _,
 b = List.TransformMany({1..Table.RowCount(a)}, each B, (x,y)=> y & " " & Text.From(x)),
 c = Table.FromRows({{a[Noms]{0}} & List.Combine(Table.ToRows(Table.RemoveColumns(a,"Noms")))}, {"Nom"} & b)
 ][c]})[x])
in
C

Bonne journée

 

[JHA]

Bonjour à tous,

un début de piste avec Power Query pour dépivoter les colonnes et une formule matricielle.

JHA

 

[Marc.A.S.]

Bonjour Marc,
Avec une centaine de dossiers potentiels par acteurs, c'est peut être plus simple de passer par du VBA, le fichier sera plus simple à gérer, et moins lourd.
De plus les formules seront réévaluées à chaque recalcul, avec du VBA elle ne s'exécute qu'une fois.
Un essai en PJ avec :

Sub Worksheet_Activate()
    Dim DL%, Tablo, Sortie, Ligne%, Colonne%, L%, Nom$, i%
    DL = [A1000].End(xlUp).Row
    [B2:ZZ1000].ClearContents
    Tablo = Sheets("Base").[A1].CurrentRegion
    ReDim Sortie(1 To DL, 1 To 1000)
    Ligne = -1
    For L = 1 To DL
        Ligne = Ligne + 1: Colonne = 1
        Nom = Cells(L, "A")
        For i = 2 To UBound(Tablo)
            If Tablo(i, 1) = Nom Then
                Sortie(Ligne, Colonne) = Tablo(i, 2)
                Sortie(Ligne, Colonne + 1) = Tablo(i, 3)
                Sortie(Ligne, Colonne + 2) = Tablo(i, 4)
                Colonne = Colonne + 3
            End If
        Next i
    Next L
    [B2].Resize(UBound(Sortie, 1), UBound(Sortie, 2)) = Sortie
    Columns.AutoFit
End Sub

La macro est automatique, il suffit de sélectionner la feuille Synthèse pour qu'elle s'exécute.

Merci, je vais tester cette solution,
J'avais imaginé en passer par du code CBA, mais j'avais pensé qu'une formule pourrait peut-être plus simplement résoudre mon problème
Merci beaucoup

 

[Marc.A.S.]

Bonjour à tous,

une solution Power Query.

let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = {"Nom Dossier", "Statut Dossier", "Commentaire Dossier"},
C = Table.Combine(Table.Group(A, "Noms", {"x", each
 [
 a  = _,
 b = List.TransformMany({1..Table.RowCount(a)}, each B, (x,y)=> y & " " & Text.From(x)),
 c = Table.FromRows({{a[Noms]{0}} & List.Combine(Table.ToRows(Table.RemoveColumns(a,"Noms")))}, {"Nom"} & b)
 ][c]})[x])
in
C

Bonne journée

BOnjour
je ne connais pas Power Query, mais je vais creuser cette solution
Merci beaucoup

 

[Marc.A.S.]

Bonjour à tous,

un début de piste avec Power Query pour dépivoter les colonnes et une formule matricielle.

JHA

Bonjour
merci pour cette solution. je ne connais pas Poweer Query, mais je vais creuser cette piste
Merci beaucoup

 

[sylvanu]

Re,

mais j'avais pensé qu'une formule pourrait peut-être plus simplement résoudre mon problème

Si un individu à 100 dossiers, avec 3 datas par dossier, il vous faut prévoir 300 colonnes avec des formules.
Et ce sur l'ensemble des personnes.
Donc pour moi, des formules ne sont pas la bonne solution.

 

[mromain]

Bonjour à tous,

Ci-dessous une autre proposition en PowerQuery.
L'étape Source est à adapter.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshMLSpKVdIBMgyBZFhiTmZKTKmBQaolkBfkGKwUq4OiyAhI+uXnKZShKPRNzCssTVXISVRISSxJRddjTIzBJngNTlUoyS9JzEHXZEqMyWYETM7JTC2F6EkszQHpSMQTEjAl+MMBm6F4QgGmhFAYwA31SkwGChUDJb1wuxVJEXGujQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Noms = _t, Dossiers = _t, Statuts = _t, Commentaires = _t]),
    GroupByName = Table.Group(Source, {"Noms"}, {{"NB", each Table.RowCount(_), Int64.Type}, {"Data", each Table.RemoveColumns(_, {"Noms"}), type table}}),
    TransformData = 
        let
            fnTransformDataPerson = (dataPerson as table) as record =>
                let
                    AddColumnIndex = Table.AddIndexColumn(dataPerson, "Index", 1, 1, Int64.Type),
                    UnpivotColumns = Table.UnpivotOtherColumns(AddColumnIndex, {"Index"}, "Attribut", "Value"),
                    AddColumnName = Table.AddColumn(UnpivotColumns, "Name", each [Attribut] & " " & Text.From([Index])),
                    ToRecord = Record.FromTable(AddColumnName)
                in
                    ToRecord
        in
            Table.TransformColumns(GroupByName, {{"Data", fnTransformDataPerson, type record}}),
    DevelopRecords = 
        let
            listFields = Record.FieldNames(TransformData[Data]{List.PositionOf(TransformData[NB], List.Max(TransformData[NB]))})
        in
            Table.ExpandRecordColumn(TransformData, "Data", listFields, listFields),
    RemoveColumnNB = Table.RemoveColumns(DevelopRecords,{"NB"})
in
    RemoveColumnNB

A+