XL 2013 Concaténation de plusieur cellules sous conditions

[tony1711]

Bonjour a tous,

Je recherche une solution me permettant de concaténer le contenu de plusieurs cellules si un "x" est saisi dans la ligne correspondante.

Dans l'exemple ci joint je voudrais récuperer le contenu " Bitstream Access (ROB), Ether Connect, IP-VPN, Ethernet-LAN, Ethernet-Line, D-Line, OTT (Over The Top) .... En fait les cellules de la colonne B pour lesquels un X est saisi dans la ligne.

Merci d'avance
Cordialement

 

[vgendron]

Hello,
pas sur d'avoir compris le besoin..
mais une piste:
en P3=CONCATENER(P2;" ";SI((NB.SI(C3:N3;"x")<>0);B3;""))
et tu tires vers le bas
le résultat attendu est en bas..

 

[tony1711]

Merci beaucoup vgendron.

Cordialement

 

[job75]

Bonjour tony1711, vgendron,

On peut aussi envisager de faire concaténer une matrice par cette fonction VBA (Alt+F11) :

Function MatConcat$(matrice, separateur$)
Dim e
For Each e In matrice
  If e <> "" Then MatConcat = MatConcat & separateur & e
Next
MatConcat = Mid(MatConcat, Len(separateur) + 1)
End Function

Le code est à placer impérativement dans un module standard.

Dans le fichier joint la fonction est utilisée dans cette formule matricielle en P3 :

=MatConcat(SI(SOUS.TOTAL(3;DECALER(C2:N2;LIGNE(INDIRECT("1:"&EQUIV("zzz";B:B)));));DECALER(B2;1;;EQUIV("zzz";B:B));"");", ")

A valider par Ctrl+Maj+Entrée.

La formule est compliquée c'est vrai... mais elle n'est entrée que dans une seule cellule.

A+

 

[job75]

Bonjour tony1711, vgendron, le forum,

J'ai testé la formule du post #4 sur seulement 4400 lignes en l'entrant via cette macro :

Sub Concatenation()
Dim t
t = Timer
[P3].FormulaArray = "=MatConcat(IF(SUBTOTAL(3,OFFSET(C2:N2,ROW(INDIRECT(""1:""&MATCH(""zzz"",B:B))),)),OFFSET(B2,1,,MATCH(""zzz"",B:B)),""""),"", "")"
[P3] = [P3].Value
MsgBox "Durée " & Format(Timer - t, "0.00 \s"), , "Concaténation"
End Sub

C'est très rapide, le texte concaténé est entièrement visible dans la barre de formule (17398 caractères).

Je rappelle que le nombre maximum de caractères autorisé dans une cellule est 32767.

Fichier (2).

Bonne journée.

 

[job75]

Re,

Quand il y a beaucoup de données à concaténer il est plus raisonnable et vraiment très simple d'utiliser le filtre avançé :

Private Sub CommandButton1_Click()
With CommandButton1
  [O4] = "=COUNTA(C4:N4)"
  If Me.FilterMode Then Me.ShowAllData
  If .Caption Like "Masquer*" Then _
    Range("B3:N" & Rows.Count).AdvancedFilter xlFilterInPlace, [O3:O4]
  .Caption = IIf(.Caption Like "Masquer*", "Afficher tout", "Masquer les lignes non cochées")
End With
End Sub

Pour pouvoir le mettre en place j'ai inséré la ligne 3 (hauteur 0,25) avec des en-têtes.

Fichier joint.

Pour tester la rapidité j'ai recopié 1000 fois le tableau, jusqu'à la ligne 44003.

Chez moi (Win 10 - Excel 2013) le masquage s'exécute en 6,2 secondes, l'affichage en 1,4 seconde.

A+

 

[job75]

Bonjour le forum,

Avec une ListBox dans un UserForm ceci est 4 à 5 fois plus rapide que le filtre avancé :

Private Sub UserForm_Initialize()
Dim P As Range, ncol%, n&
Application.ScreenUpdating = False
Set P = Feuil1.Range("B1:N" & Application.Match("zzz", Feuil1.[B:B])).Offset(2) 'à adapter éventuellement
ncol = P.Columns.Count
With Workbooks.Add.Sheets(1).[B1].Resize(P.Rows.Count, ncol) 'document auxiliaire
  .Value = P.Value
  .Columns(0).FormulaR1C1 = "=If(LEN(RC2)*COUNTA(RC3:RC" & ncol + 1 & "),ROW())"
  .Columns(0) = .Columns(0).Value 'supprime les formules
  With .Columns(0).Resize(, 2)
    .Sort .Columns(1), xlAscending, Header:=xlNo 'tri
    n = Application.Count(.Columns(1))
    If n Then ListBox1.List = .Resize(n).Value '2 colonnes, la 1ère masquée
  End With
  .Parent.Parent.Close False
End With
Label1 = "Nombre de services listés : " & n
Application.ScreenUpdating = True
End Sub

Fichier joint avec 44000 lignes, la macro s'exécute chez moi en 1,3 seconde.

A+

 

[tony1711]

Bonjour Job75,

Merci beaucoup d'avoir pris le temps pour m'aider je suis en train d'étudier ta solution ( elle fonctionne parfaitement mais j'aime bien comprendre comment ça se passe) Je reviendrais vers toi si j'ai des questions encore merci.

Bonne journée
Cordialement
Anthony