Lister des mots dans 2 colonnes sur excel

**malakou** · 21/04/2017, 11h35

Bonjour à tous,

Je galère depuis quelque jours avec cette idée sur Excel. Je voudrais lister une colonne contenant des mots et des cases vides, sans prendre en compte les cases vides. J'ai trouvé la macro suivante. Elle fait très bien le boulot.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub LISTER()
derligne = Range("A70").End(xlUp).Row
[D4] = ""
For i = 1 To derligne
If Cells(i, 1).Value <> "" Then
[D4] = [D4] & Cells(i, 1).Value & ", "
End If
Next i
' ci-dessous pour ôter la dernière virgule
[D4] = Left([D4], Len([D4]) - 2)
End Sub

J'ai deux colonnes A et B : la macro liste la colonne A. La colonne B contient des sous parties de la colonne A. J'aurai voulu que lorsqu'elle rencontre un certain mot, elle commence à lister entre parenthèses les mots de la colonne B en face de la cellule contenant ce mot (col A) et ce jusqu'à la rencontre d'une cellule vide.

Ma macro va lister comme ça minimes, adulte, cadets, seniors mais j'aurai voulu qu'elle fasse : minimes (Alexis, Pierre, Jean), adultes, cadets (Louis, Adrien). J'espère que je suis compréhensible

minime	Alexis
adultes	Pierre
	Jean

cadets	Louis
	Adrien


seniors

Merci d'avance pour votre aide

malakou

**Philippe Tulliez** · 21/04/2017, 11h51

Bonjour,

J'espère que je suis compréhensible

Absolument pas, parce-qu'au vu de ton image pour moi Pierre est Adulte et toi tu écris Minime

Alors pourquoi des cellules vides ?
Personnellement je remplirais les cellules et j'utiliserais la mise en forme conditionnelle pour masquer les cellules dont la valeur serait identique à celle du dessus.

**malakou** · 21/04/2017, 12h39

Bonjour,

Merci pour ta réponse et désolé si je ne suis pas clair.
Alors le format du tableau n'est pas modifiable même si sa logique est discutable il faut le voir comme ça :

Nom : minime.PNG
Affichages : 300
Taille : 5,1 Ko

Nom : minime.PNG
Affichages : 300
Taille : 5,1 Ko

Ce tableau est créé à la base pour avoir plus de visibilité mais pas pour être utilisé comme tu l'entend.

En espérant être plus clair

**Marc-L** · 21/04/2017, 12h55

Bonjour,

c'est juste une question de simple logique et accessible à un débutant !

Parmi les cellules de la plage utilisée (cf aide VBA interne de UsedRange)
tester les cellules de la première colonne et aussi celles de la seconde.

Coucher déjà cela sur papier et une fois la logique aboutie, le code en découlera facilement …

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Stockholm, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

**malakou** · 21/04/2017, 15h25

Bonjour et merci pour ta réponse.

En essayant de tatonner un peu j'ai réalisé ça. Je dois avoir écrit des incohérences mais j'ai du mal à me rendre compte.

J'arrive à obtenir ça :
()Alexis, Pierre, Jean, ))Louis, Adrien, ))))))))))minimes, adultes, (Jean, ))Louis, Adrien, ))))))))))())Louis, Adrien, ))))))))))()Louis, Adrien, ))))))))))cadets, (Adrien, ))))))))))())))))))))seniors, ())))))))()))))))())))))()))))())))()))())

Je ne sais pas trop comment stopper la boucle quand il arrive sur une cellule vide.
Merci d'avance

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub LISTER()
derligne = Range("A70").End(xlUp).Row
 
    Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
    Dim currentRowValue As String
    sourceCol = 2
    rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row
 
[D4] = ""
For i = 1 To derligne
        If Cells(i, 1).Value <> "" Then
            If Cells(i, 1).Value <> "minimes" Or Cells(i, 1).Value <> "cadets" Then [D4] = [D4] & Cells(i, 1).Value & ", "
            Else
                [D4] = [D4] & Cells(i, 1) & "("
            For j = i To rowCount
                If Cells(j, 2).Value <> "" Then
                [D4] = [D4] & Cells(j, 2) & ", "
                Else
                [D4] = [D4] & "), "
                [D4] = Left([D4], Len([D4]) - 2)
                End If
            Next j
            End If
Next i
[D4] = Left([D4], Len([D4]) - 2)
End Sub

**Marc-L** · 21/04/2017, 16h10

Comme c'est une boucle bornée elle finit de facto toute seule !

Ligne de code n°2 non fiable : imagine si tu retires seniors de ton exemple !

Si le résultat attendu est bien minime (Alexis), adultes (Pierre, Jean), cadets (Louis, Adrien), seniors alors
il suffit déjà de commencer la logique sur la colonne B …

Réalisable en une quinzaine de lignes de code pour peu qu'avant de commencer à l'écrire
une réflexion soit déjà menée comme il se doit, le papier cela aide ! …

**malakou** · 21/04/2017, 16h27

Comme dit plus haut, le résultat attendu est
minimes (Alexis, Pierre, Jean), adultes, cadets (Louis, Adrien) et non pas minime (Alexis), adultes (Pierre, Jean), cadets (Louis, Adrien), seniors.

Les catégories minimes adultes cadets et seniors seront toujours dans la liste.

Je ne comprend pas pourquoi la ligne 2 n'est pas fiable sachant que je ne veux pas prendre en compte les cases vides.

**Marc-L** · 21/04/2017, 16h59

Avec un peu de jugeote, si plusieurs membres séniors alors cette ligne de code n°2 n'est vraiment pas fiable ‼

Sinon cela demande une p'tite vingtaine de lignes de code toujours en s'articulant sur la colonne B …

La chaine de caractères doit commencer par le contenu de la première ligne
puis commencer une boucle de la deuxième ligne jusqu'à la dernière ligne de la plage utilisée (cf post #4 !).

Le premier niveau de tests étant sur la cellule de colonne B remplie ou pas
afin déjà de commencer à mener une réflexion quant à cette simple problématique …

**malakou** · 21/04/2017, 17h57

Petites précisions sur le tableau
- Seul les catégories minimes et cadet peuvent contenir des noms et chaque catégorie est indiqué systématiquemet une seule fois dans la colonne A.
- Je pensais du coup qu'il fallait lister à partir de la colonne A, dès qu'on rencontre le mot minime ou cadet alors on liste la colonne B. Sachant que dans la colonne B, la liste se termine toujours par une case vide.

Je vais continuer de tâtonner.

**Philippe Tulliez** · 21/04/2017, 18h03

Bonjour,
Voici un petit exemple dont tu peux t'inspirer.
Le tableau commence en cellule A1 de la feuille nommée Feuil1 et la première ligne contient les titres
Pour l'exemple le texte est afficher par la fonction MsgBox, il suffit d'écrire dans une cellule plutôt que l'afficher

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub t()
 Dim rng As Range, Row As Long, txt As String
 Set rng = ThisWorkbook.Worksheets("Feuil1").Range("A1").CurrentRegion
 With rng
  For Row = 2 To .Rows.Count
   If Len(.Cells(Row, 1)) Then
     If Len(txt) Then txt = txt & ")": MsgBox txt
     txt = .Cells(Row, 1) & "(" & .Cells(Row, 2)
    Else
     txt = txt & ", " & .Cells(Row, 2)
   End If
  Next
  txt = txt & ")": MsgBox txt
 End With
End Sub

[EDIT]
Dans mon exemple, il n'y a pas de ligne vide entre les catégories et on suppose que les noms sont déjà regroupés par catégorie

**Marc-L** · 21/04/2017, 18h17

Envoyé par malakou

Seules les catégories minimes et cadet peuvent contenir des noms

Information importante devant être indiquée dès la présentation initiale et alors évidemment
simplifiant énormément la problématique car plus besoin de boucler sur toutes les cellules !

Rechercher dans la colonne A une catégorie précise via la méthode Range.Find
puis depuis la colonne B de la catégorie trouvée jusqu'à la dernière via la propriété End(xlDown) …

**malakou** · 21/04/2017, 18h25

Merci beaucoup pour ta réponse.

J'ai appliqué ta macro à mon fichier. Mais le problème c'est que ça ne prend pas en compte les cases vides dans la colonne B.
J'ai supprimé la partie message box puisque je voudrai ça dans une cellule.

Le problème de la macro est qu'elle me liste Pierre dans adulte alors qu'il devrait être dans minimes.

Je t'envoie mon fichier qui sera peut être plus clair que moi.

**malakou** · 21/04/2017, 18h41

Envoyé par Marc-L

Information importante devant être indiquée dès la présentation initiale et alors évidemment
simplifiant énormément la problématique car plus besoin de boucler sur toutes les cellules !

Rechercher dans la colonne A une catégorie précise via la méthode Range.Find
puis depuis la colonne B de la catégorie trouvée jusqu'à la dernière via la propriété End(xlDown) …

Mais en recherchant une catégorie précise, je n'aurai pas la liste identique à celle que je veux si ? Il faut qu'elle soit dans le même ordre que celle du tableau. Est ce que c'est possible comme ça ?

**Marc-L** · 21/04/2017, 18h45

Aucun souci avec la méthode Find car c'est juste une question de simple logique !

Sur le même principe que la méthode Find mais à partir du B-A-BA d'Excel,
une simple formule de calculs ne nécessitant qu'une dizaine de lignes de code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub Demo1()
  Const D = ", ", F = "TRANSPOSE(IF(#>"""",ADDRESS(ROW(#),1,4)))"
    Dim V, C&, S$
        V = Filter(Evaluate(Replace(F, "#", ActiveSheet.UsedRange.Columns(1).Address)), False, False)
    For C = 0 To UBound(V) Step 2
        With Range(V(C))
            S = IIf(S > "", S & D, "") & .Value & " (" & Join(Application.Transpose( _
                Range(.Offset(, 1), .Offset(, 1).End(xlDown)).Value), D) & ")" & D & Range(V(C + 1)).Value
        End With
    Next
        [D4].Value = S
End Sub

La première catégorie commençant en A1 dans cet exemple ou avec la cellule A1 vide …

___________________________________________________________________________________________________________

Merci de cliquer sur

en bas à droite de chaque message ayant aidé puis sur

pour clore cette discussion …

**malakou** · 22/04/2017, 08h39

Merci pour cette formule qui fonctionne très bien avec mon exemple !

En revanche, elle ne va plus si j'ajoute une catégorie poussin. Comment j'intègre un nombre illimité de valeur possible dans la colonne a ?
Et si la cellule est vide visuellement mais qu'elle contient une formule alors elle est listée comme une colonne pleine.

EDIT : J'ai mis un autre exemple de tableau en exemple.

Merci d'avance!

**Marc-L** · 22/04/2017, 11h37

Déjà vu le manque de neurones certain de la part du concepteur de la feuille de calculs enfreignant
les règles de base quant à la conception de données dans un tableur, il faudrait déjà savoir le résultat attendu !

Qui plus est vu la présentation des données fluctuantes, on en revient

• soit à une conception digne de ce nom avec un tant soit peu de jugeote
c'est à dire avec au moins une ligne entière vide séparant chaque catégorie & ses membres
- là aussi ce n'est pas conseillé les lignes vides pour certaines fonctionnalités d'Excel
mais par code cela ne cause pas de souci pour le besoin désiré -
et là quelle que soit la configuration un code de niveau débutant (en moins de quinze lignes)
combinant les propriété End & CurrentRegion fonctionnera toujours jusqu'à la fin des temps;

• soit en restant en l'état alors relire les posts #4 & 8 car ce n'est pas en soit tant une difficulté de codage
(en fait là aussi du niveau débutant et réalisable en environ vingt lignes)
mais juste une question de simple logique déjà à modéliser puis à mettre en œuvre …
Et dans ce cas on attend toujours de connaître ta réflexion sur le sujet ! (cf règles du forum)

Soit encore en simplifiant le besoin - et donc la logique nécessaire - en ne tenant compte que des catégories avec des membres …

**Marc-L** · 22/04/2017, 13h11

Le code du post #14 effectivement fonctionne uniquement pour le cas présenté;
c'est le souci avec une présentation initiale incomplète et pas assez claire, on se retrouve limité par un contexte fermé !

Ceci est ma dernière intervention, si le besoin n'est toujours pas atteint ou en cas d'évolution,
tout a déjà été écrit, tu sais donc ce qu'il te restera à accomplir …

Toujours via le B-A-BA d'Excel, à savoir une encore plus simple formule de calculs
et la séquence de touches CTRL + Flèche vers le bas, la propriété End(xlDown) en VBA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Demo3()
       Const D = ", ", F = "TRANSPOSE(IF(#>"""",#))"
         Dim Rg As Range, Rf As Range, V, W
         V = Filter(Evaluate(Replace(F, "#", ActiveSheet.UsedRange.Columns(1).Address)), False, False)
         If UBound(V) < 0 Then [D4].ClearContents: Exit Sub
         Set Rg = IIf([B1].Value > "", [B1], [B1].End(xlDown))
    Do Until Rg.Value = ""
         Set Rf = IIf(Rg(2).Value > "", Rg.End(xlDown), Rg)
              W = Application.Match(Rg(1, 0).Value, V, 0)
        If IsNumeric(W) Then
          If Rf Is Rg Then V(W - 1) = V(W - 1) & " (" & Rg.Value & ")" _
                      Else V(W - 1) = V(W - 1) & " (" & Join(Application.Transpose(Range(Rg, Rf).Value), D) & ")"
        End If
         Set Rg = Rf.End(xlDown)
    Loop
         Set Rg = Nothing:  Set Rf = Nothing
     [D4].Value = Join(V, D)
End Sub

Évidemment ceci a des chances de ne pas rentrer dans le cadre des cours d'un étudiant car
souvent c'est de l'algorithmie classique qui est attendue (et non pas du spécifique Excel) et,
dans un tel cas, mon code pourtant fonctionnel rapportera un joli zéro (déjà vu !) surtout
lorsque l'étudiant n'aura même pas pris la peine de comprendre et donc de pouvoir justifier ce code …

___________________________________________________________________________________________________________

Merci de cliquer sur

en bas à droite de chaque message ayant aidé puis sur

pour clore cette discussion …

___________________________________________________________________________________________________________

Heureux l'étudiant qui, comme la rivière, suit son cours sans quitter son lit …

**Marc-L** · 22/04/2017, 16h06

Pour ceux intéressés par l'apport de formules de calculs Excel résolvant une problématique VBA
voici une variante du précédent code avec la colonne B traitée par une formule (donc sans la propriété End)
simplifiant l'extraction des plages utilisées de cette colonne par le biais d'une p'tite astuce :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub Demo3v()
       Const D = ", ", A = "TRANSPOSE(IF(#>"""",#))", B = "TRANSPOSE(IF(#>"""",ADDRESS(ROW(#),2,4)))"
         Dim Rg As Range, V, U$, W
    With ActiveSheet.UsedRange.Columns
        V = Filter(Evaluate(Replace(A, "#", .Item(1).Address)), False, False)
        If UBound(V) < 0 Then [D4].ClearContents: Exit Sub
        U = Join(Filter(Evaluate(Replace(B, "#", .Item(2).Address)), False, False), ",")
    End With
    If U > "" Then
        For Each Rg In Union(Range(U), Range(U)).Areas
                W = Application.Match(Rg(1, 0).Value, V, 0)
            If IsNumeric(W) Then
              If Rg.Count > 1 Then V(W - 1) = V(W - 1) & " (" & Join(Application.Transpose(Rg.Value), D) & ")" _
                              Else V(W - 1) = V(W - 1) & " (" & Rg.Value & ")"
            End If
        Next
    End If
        [D4].Value = Join(V, D)
End Sub

___________________________________________________________________________________________________________

Merci de cliquer sur

en bas à droite de chaque message ayant aidé puis sur

pour clore cette discussion …

**RyuAutodidacte** · 22/04/2017, 16h26

Coucou Marc,

pour les 2 intervention précédentes

C'est bien souvent le problème :
- soit les présentations en dehors du contexte (les demandeurs pensant bien faire en simplifiant la problématique)
- soit le manque de clarté et de précisions qui font omettre certains paramètres à la problématique (qui font : " ben ça ne marche pas sur mon fichier !!!")

et quand j'ai vu la structure des fichiers c'est à se demandé si celle-ci à bien été réfléchie …

car avant tout (mr les étudiants) il est impératif avant de faire ses formules, ou du vba, de définir la structure la plus logique/pragmatique selon les données
afin de pourvoir traiter celles-ci par la suite de manière efficace (d'ailleurs y a t-il une raison à ce que la structure des fichiers soit comme cela … ?)

Pour ma part j'en resterai là, puisque le demandeur à tout ce qu'il faut pour continuer

Edit :

pour la 3è arrivée entre temps (c'est presque Noël avant l'heure pour les étudiants

)

**Marc-L** · 22/04/2017, 16h47

Merci Ryu !

Si c'est dans le cadre d'un exercice estudiantin, la "structure" des données est imposée et donc non modifiable
car évidemment à des fins algorithmiques; la raison pour laquelle j'ai présenté des solutions utilisant
plutôt les fonctionnalités d'Excel et non pas un simple algorithme tel qu'il pourrait être attendu et là,
ce n'est certainement pas Noël pour eux même s'ils peuvent toujours continuer de croire au Père Noël,
dussé-je rappeler les règles de ce forum …

___________________________________________________________________________________________________________

Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)