1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| Function Composition(Index As Long) As String
Dim CountOf As Long
Dim Formula As String
Dim Headers
Dim i As Long
Dim Temp As String
Dim Value As String
Application.Volatile
Formula = "=TEXTJOIN("";"",TRUE,IF(A{ligne}:E{ligne}<>"""",LEFT($A$1:$E$1,LEN($A$1:$E$1)-2),""""))"
Temp = Evaluate(Replace(Formula, "{ligne}", Index))
Headers = Evaluate("=UNIQUE(TRANSPOSE((LEFT(A1:E1,LEN(A1:E1)-2))))")
For i = 1 To UBound(Headers)
CountOf = (Len(Temp) - Len(Replace(Temp, Headers(i, 1), ""))) / Len(Headers(i, 1))
If CountOf > 0 Then Value = Value & CountOf & " " & Headers(i, 1) & IIf(CountOf > 1, "s", "") & ", "
Next i
If Value <> "" Then Value = Left(Value, Len(Value) - 2)
Composition = Value
Erase Headers
End Function |
Partager