Salut
prenons l'exemple suivant qui répondra à tes attentes :
la feuille "ListeUtilisateurs"
utilisateur categorie
toto1 A
toto2 B
toto1 B
toto1 C
toto2 B
toto3 A
toto1 A
toto2 F
toto3 C
toto2 E
toto4 E
toto3 A
la feuille "synthese" :
A B C D E F
toto1
toto2
toto3
toto4
toto5
toto6
toto7
toto8
le code suivant fera l'affaire :
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
| Sub tt()
Dim tt As Range
Dim dico
Set dico = CreateObject("Scripting.Dictionary")
rowID = 1
UserName = 1
While (UserName <> "")
UserName = Sheets("ListeUtilisateurs").Cells(rowID, 1)
Category = Sheets("ListeUtilisateurs").Cells(rowID, 2) & ";"
rowID = rowID + 1
If UserName <> "" Then
If Not dico.exists(UserName) Then
dico.Add UserName, Category
Else
dico(UserName) = dico(UserName) & Category
End If
End If
t = 0
Wend
lastRowOfSynthese = Sheets("synthese").Cells(.Rows.Count, "A").End(xlUp).Row
lastColumnOfSynthese = Sheets("synthese").Cells(1, .Columns.Count).End(xlToLeft).Column
For rowID = 2 To lastRowOfSynthese
UserName = Sheets("synthese").Cells(rowID, 1)
For colID = 2 To lastColumnOfSynthese
categoryToFind = Sheets("synthese").Cells(1, colID) & ";"
If dico.exists(UserName) Then
If InStr(dico(UserName), categoryToFind) > 0 Then
Sheets("synthese").Cells(rowID, colID) = "X"
Else
Sheets("synthese").Cells(rowID, colID) = "'-"
End If
Else
Sheets("synthese").Cells(rowID, colID) = "'-"
End If
Next
Next
End Sub |
et donnera :
A B C D E F
toto1 X X X - - -
toto2 - X - - X X
toto3 X - X - - -
toto4 - - - - X -
toto5 - - - - - -
toto6 - - - - - -
toto7 - - - - - -
toto8 - - - - - -
++
Nico
Partager