Macro pour trier et classer des données avant TCD

Version imprimable

Bonjour,
Merci à tous ceux qui auront le courage de traiter ce problème :roll:

Sur une feuille, dans une colonne unique, des "centres d'intérêt" sont écrits avec un séparateur virgule, en face du nom des personnes.
Exemples de quelques lignes :

Nom
Centres d'intérêt

Coco i_couture,i_cuisine

Fred

Baptiste i_balade,i_convivialite,i_cinema

Mick i_theatre,i_livres,i_couture,i_bricolage

Marie i_convivialite

Isabelle i_informatique

Yann

Paul i_sport,i_famille,i_informatique,i_anglais

A partir de ces données d'entrée, je souhaite créer un tableau croisé dynamique, pour lister les personnes par centre d'intérêt (CI). J'ai conscience que la présentation des données dans l'état ne permet pas de créer le TCD.
J'imagine une macro comme suit:

extraire un dictionnaire des centres d'intérêt existants dans la colonne
écrire dans une nouvelle colonne, pour chaque ligne, une chaine avec un séparateur, comprenant les CI, s'ils existe(nt)
copier les données dans autant de colonnes que nécessaires (transposer données...)

Cela donnerait le dictionnaire : i_anglais, i_balade, i_convivialite, i_cuisine, i_couture ...

Nom
Centres d'intérêt
CI ordonnés

Coco i_couture,i_cuisine ,,,,i_cuisine,i_couture,...

Fred ,,,,,,,,,,

Baptiste i_balade,i_convivialite,i_cinema

Mick i_theatre,i_livres,i_couture,i_bricolage

Marie i_convivialite

Isabelle i_informatique

Yann

Paul i_sport,i_famille,i_informatique,i_anglais

Avez-vous des pistes pour résoudre ce problème alambiqué !
Alain

19/02/2024, 17h07
Tête de chat

Bonjour,

je ne comprends pas la finalité d'avoir le résultat sous forme d'une liste séparée par des virgules dans une seule cellule.

À part ça la solution la plus simple passe probablement par Powerquery : faire un split de la colonne centres d'intérêts, puis pivoter sur cette colonne pour avoir un "TCD".
Ensuite si tu tiens absolument aux virgules il faudra réagréger les colonnes.
19/02/2024, 18h03
alainb

Citation:

Envoyé par Tête de chat

Bonjour,

je ne comprends pas la finalité d'avoir le résultat sous forme d'une liste séparée par des virgules dans une seule cellule.

À part ça la solution la plus simple passe probablement par Powerquery : faire un split de la colonne centres d'intérêts, puis pivoter sur cette colonne pour avoir un "TCD".
Ensuite si tu tiens absolument aux virgules il faudra réagréger les colonnes.

Bonjour Tête de chat,
La finalité n'est pas d'avoir le résultat sous forme d'une liste séparée par des virgules dans une seule cellule, mais d'obtenir une liste de Centres d'intérêt par personne et d'avoir une TCD.

Je découvre Powerquery. Après l'import de la feuille concernée, j'ai sélectionné les deux colonnes.
Comment poursuivre ?
19/02/2024, 18h28
deedolith

Salut,

La première question que j'ai envie de te poser est: D'où proviennent tes données ?
Et: Peux-tu les obtenir autrement ?
Sous forme normalisée, ce sera autrement plus facile à gérer.
19/02/2024, 18h37
alainb

Ta question est TRÈS pertinente, c'est la première réflexion que j'ai entrepris avant d'ouvrir la discussion sur le forum.

Les données sont issues d'une base de connaissance, genre Notion. L'export créé une colonne Centre d'intérêt, alimentée par des tags.
Les tags (centres d'intérêt) sont séparés par une virgule et ne sont pas ordonnés dans la cellule. Ils sont susceptibles d'évoluer dans le temps (ajout, suppression, variation orthographe).
D'où le sujet de la discussion :oops:
20/02/2024, 05h52
alainb
Bonjour,
La nuit porte conseil...
Hier, dans l'énoncé du problème, j'avais oublié de tester l'étape finale du TCD.
Par leurs questionnements, deedolith et Tête de chat ont orienté mes réflexions.

Le TCD doit être de cette forme : somme des Centres d'intérêts

Somme de i_couture Somme de i_anglais Somme de i_cuisine

1

2

1

Si je reviens à la macro, l'itération sur la colonne des Centres d'intérêts (CI) :
- créé la colonne avec le nom du CI,
- ajoute dans la cellule la valeur 1 si une personne est concernée.
Une aide serait la bienvenue !
Merci

Cette macro VBA donne le résultat attendu :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
Sub traitement()
'Déclarer les variables
Dim Chemin As String, Référence_Nom As String
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
Dim i As Long, j As Integer, k As Long
Dim DerLig As Long, DerniereColonne As Long
Dim Valeur As String
Dim Tableau As Variant
Dim NbElements As Integer
Dim Element As Variant
Dim ColonneExiste As Boolean
Dim MaFeuille As Worksheet
 
Set MaFeuille = Sheets("interet") 'Le nom de la feuille
 
'Parcourir le contenu de la colonne 2 de la feuille
'Déterminer la dernière ligne de la colonne 2 en partant du bas
DerLig = MaFeuille.Columns(2).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
'Parcourir la colonne 2 de la première à la dernière ligne
For i = 2 To DerLig 'commencer après la ligne de titre
 
'Récupérer la valeur de la cellule i de la colonne 2
Valeur = MaFeuille.Cells(i, 2).Value
    'Vérifier si la cellule est vide
    If MaFeuille.Cells(i, 2).Value = "" Then
        'Passer à la ligne suivante
        GoTo Suivant
    End If
 
    'Vérifier si la valeur contient une virgule
    If InStr(Valeur, ",") > 0 Then
        'Séparer la valeur en un tableau selon la virgule
        Tableau = Split(Valeur, ",")
    Else
        'Créer un tableau avec un seul élément égal à la valeur
        Tableau = Array(Valeur)
    End If
 
 
    'Compter le nombre d'éléments du tableau
    NbElements = UBound(Tableau) - LBound(Tableau) + 1
    'Parcourir chaque élément du tableau
    For j = 0 To NbElements - 1
        'Récupérer l'élément j du tableau
        Element = Tableau(j)
        'Supprimer les espaces éventuels autour de l'élément
        Element = Trim(Element)
        'Initialiser la variable ColonneExiste à Faux
        ColonneExiste = False
        'Déterminer la dernière colonne utilisée en partant de la droite
        DerniereColonne = MaFeuille.Cells(1, Columns.Count).End(xlToLeft).Column
        'Parcourir les colonnes à partir de la colonne 3
            For k = 3 To DerniereColonne
                'Vérifier si le titre de la colonne k est égal à l'élément
                If MaFeuille.Cells(1, k).Value = Element Then
                    'Mettre la variable ColonneExiste à Vrai
                    ColonneExiste = True
                    'Sortir de la boucle des colonnes
                    Exit For
                End If
            Next k
        'Vérifier si la colonne n'existe pas déjà
        If ColonneExiste = False Then
            'Créer une nouvelle colonne à droite de la dernière colonne utilisée
            DerniereColonne = DerniereColonne + 1
            'Mettre l'élément comme titre de la nouvelle colonne
            MaFeuille.Cells(1, DerniereColonne).Value = Element
            'Mettre la valeur 1 dans la cellule de la ligne courante de la nouvelle colonne
            MaFeuille.Cells(i, DerniereColonne).Value = 1
        Else
            'Ajouter 1 dans la cellule de la ligne courante de la colonne existante
            MaFeuille.Cells(i, k).Value = MaFeuille.Cells(i, k).Value + 1
        End If
    Next j
 
Suivant:
Next i
 
End Sub

20/02/2024, 09h34
deedolith

La première chose à faire, est de retravailler tes données afin de les normaliser (un nom / centre d'intéret par ligne):

Nom
Centres d'intérêt

Coco i_couture

Coco i_cuisine

Fred

Baptiste i_balade

Baptiste i_convivialite

Baptiste i_cinema

Ensuite tu pourras faire le TCD qui te plait.
20/02/2024, 09h51
alainb

Citation:

Envoyé par deedolith

La première chose à faire, est de retravailler tes données afin de les normaliser (un nom / centre d’intérêt par ligne):
Ensuite tu pourras faire le TCD qui te plait.

Bonjour deedolith,
Ton propos est effectivement exact.
C'est l'objet du traitement effectué par la macro VBA que j'ai posté ce matin.
Alain
20/02/2024, 09h54
Tête de chat

1 pièce(s) jointe(s)

Bonjour,

solution Powerquery ci-joint

Pièce jointe 651099

*Nom*	*Centres d'intérêt*
Coco	i_couture,i_cuisine
Fred
Baptiste	i_balade,i_convivialite,i_cinema
Mick	i_theatre,i_livres,i_couture,i_bricolage
Marie	i_convivialite
Isabelle	i_informatique
Yann
Paul	i_sport,i_famille,i_informatique,i_anglais

*Nom*	*Centres d'intérêt*	*CI ordonnés*
Coco	i_couture,i_cuisine	,,,,i_cuisine,i_couture,...
Fred		,,,,,,,,,,
Baptiste	i_balade,i_convivialite,i_cinema
Mick	i_theatre,i_livres,i_couture,i_bricolage
Marie	i_convivialite
Isabelle	i_informatique
Yann
Paul	i_sport,i_famille,i_informatique,i_anglais

Somme de i_couture	Somme de i_anglais	Somme de i_cuisine
1	2	1