Re : concatener de cellules avec filtres
Petite modif après m'être creusé la tête....
Sub TableauInverse() ' inspiré d'un code de J. Boisgontier !!!
'
Formation Excel VBA JB
'
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
Set ws2 = Sheets("report ctr")
Set ws1 = Sheets("prestataires")
ws2.Range("E1:k10").ClearContents
nblignes = ws1.[e65000].End(xlUp).Row
Dim a(1 To 5, 1 To 5)
Dim Last As Long
lig = 1: col = 1
Mlig = lig: Mcol = col
Last = ws1.Range("E65000").End(xlUp).Row
For Each c In Sheets("prestataires").Range("e2:e" & Last)
If d1.exists(c.Value) Then lig = d1(c.Value) Else d1(c.Value) = Mlig: lig = Mlig: Mlig = Mlig + 1
tmp = c.Offset(, -1)
If d2.exists(tmp) Then col = d2(tmp) Else d2(tmp) = Mcol: col = Mcol: Mcol = Mcol + 1
a(lig, col) = IIf(IsEmpty(a(lig, col)), c.Offset(, -4) & ";", a(lig, col) & c.Offset(, -4))
Debug.Print a(lig, col), c.Row, c
Next c
' tri
ws2.[f2].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
ws2.[g1].Resize(1, d2.Count) = d2.keys
ws2.[G2].Resize(d1.Count, d2.Count) = a
ws2.Select
End Sub