Pam a deux colonnes de données. Dans la colonne A, il y a des identifiants simples, tels que A, B, C, etc. Dans la colonne B, il y a une série de valeurs entières.

Elle peut trier les données par l’identifiant et, secondairement, par les valeurs entières. Maintenant, elle veut, dans la colonne C, avoir une formule qui concaténera toutes les valeurs entières pour un identifiant particulier. Ainsi, si A1: A4 contiennent tous l’identifiant A, alors dans la cellule C1, elle aimerait que toutes les valeurs de B1: B4 soient concaténées et divisées par des virgules, telles que « 11, 17, 19, 25 ». Puisque le nombre de lignes pour chaque identifiant peut être différent, Pam ne sait pas comment procéder pour la concaténation.

Le moyen le plus simple d’y parvenir est d’utiliser une macro, qui peut être créée en tant que fonction définie par l’utilisateur. Voici un exemple:

Function CatSame(c As Range) As String     Application.Volatile     sTemp = ""

iCurCol = c.Column     If iCurCol = 3 Then         If c.Row = 1 Then             sLast = ""

Else             sLast = c.Offset(-1, -2)

End If         If c.Offset(0, -2) <> sLast Then             J = 0             Do                 sTemp = sTemp & ", " & c.Offset(J, -1)

J = J + 1             Loop While c.Offset(J, -2) = c.Offset(J - 1, -2)

sTemp = Right(sTemp, Len(sTemp) - 2)

End If     End If     CatSame = sTemp End Function

Cette fonction prend essentiellement une valeur qui lui est transmise (une référence de cellule) et vérifie que la référence de cellule est pour la colonne C. Si c’est le cas, elle commence à concaténer les valeurs de la colonne B en fonction des valeurs de la colonne A. Elle renvoie la chaîne de valeurs concaténées si la valeur de la colonne A est différente de la valeur de la ligne au-dessus.

En supposant que vos identifiants sont dans la colonne A et que vos valeurs à concaténer sont dans la colonne B, vous pouvez placer ce qui suit dans la colonne C:

=CatSame(C1)

Copiez ceci autant que nécessaire dans la colonne C et vous vous retrouverez avec exactement ce que Pam voulait.

Une fonction plus polyvalente serait celle qui fonctionnerait un peu comme RECHERCHEV, mais ramènerait une liste concaténée de valeurs qui correspondent à ce que vous recherchez. Considérons la fonction suivante:

Function VLookupAll(vValue, rngAll As Range, _   iCol As Integer, Optional sSep As String = ", ")

Dim rCell As Range     Dim rng As Range     On Error GoTo ErrHandler

Application.Volatile     Set rng = Intersect(rngAll, rngAll.Columns(1))

For Each rCell In rng         If rCell.Value = vValue Then _           VLookupAll = VLookupAll & sSep & _           rCell.Offset(0, iCol).Value     Next rCell

If VLookupAll = "" Then         VLookupAll = CVErr(xlErrNA)

Else         VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))

End If ErrHandler:

If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)

End Function

Cette fonction prend jusqu’à quatre arguments. Le premier est la valeur que vous souhaitez faire correspondre dans votre recherche. Dans le cas de Pam, ce serait l’identifiant que vous voulez, tel que A, B ou C. Le deuxième argument est la plage de cellules dans laquelle rechercher les correspondances (colonne A dans ce cas).

Le troisième argument est un décalage (à partir de la plage du deuxième argument)

qui représente les valeurs que vous souhaitez concaténer. Vous pouvez utiliser la fonction de cette manière:

=VLookupAll("B",A1:A99,1)

Si vous souhaitez spécifier un séparateur différent entre les valeurs, vous pouvez le faire en utilisant le quatrième argument facultatif. Par exemple, ce qui suit renvoie une chaîne où un tiret sépare chaque valeur:

=VLookupAll("B",A1:A99,1,"-")

Les solutions jusqu’à présent se sont concentrées sur l’utilisation de macros. La raison en est relativement simple: il n’y a pas de solution basée sur une formule qui puisse faire ce dont Pam a besoin. L’utilisation d’instructions IF imbriquées pour évaluer ce qui se trouve dans la colonne A ne fonctionnera pas correctement car vous êtes limité dans la profondeur d’imbrication des instructions IF.

Vous pouvez utiliser une formule et un résultat intermédiaire si cela ne vous dérange pas que les valeurs concaténées soient à la dernière instance d’un identifiant dans la colonne A. Commencez par mettre cette formule dans la cellule C1:

=B1

Cette formule doit entrer dans la cellule C2:

=IF(A2=A1,C1 & ", " & B2, B2)

Copiez cette formule autant de lignes que nécessaire. Vous obtenez une série de plus en plus longue de valeurs concaténées dans la colonne C, la plus longue de chaque exécution étant sur la même ligne que le dernier identifiant séquentiel de la colonne A. Vous pouvez ensuite mettre ce qui suit dans toutes les cellules applicables de la colonne D:

=IF(LEN(C2)>LEN(C1),"",C1)

Cette formule n’affiche que les chaînes les plus longues de la colonne C, ce dont Pam avait besoin pour commencer.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (9197) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Concatenating_Values_from_a_Variable_Number_of_Cells [Concaténation de valeurs à partir d’un nombre variable de cellules].