XL 2016 Comptabilisation de prénoms rangés en groupes dans des cellules, avec retours à la ligne

[Webperegrino]

Bonjour Le Forum,

Dans un extrait de fichier, ci-joint, je m’arrache les neurones depuis plusieurs jours.

Je voudrais, par VBA et dans la codification en place, pouvoir comptabiliser le nombre de fois que chaque personne est sollicitée en fonction de ce qui est entré dans les cellules de la feuille ORIGINE, et l’indiquer dans le tableau de DESTINATION.

Pour trouver une parade, j’ai bloqué la longueur du premier prénom à 8 caractères, avant un retour à la ligne dans la cellule... car je ne sais pas séparer les groupes de prénoms par VBA.

Auparavant, ma macro fonctionnait seulement pour le cas où il n’y avait que zéro, un ou deux prénoms dans chaque cellule pour effectuer le comptage dans DESTINATION.

Maintenant je suis contrait de devoir reformuler tout cela pour arriver à remplir les cellules de DESTINATION en tenant compte des zéro, un ou plusieurs prénoms de ORIGINE (non limités en nombre !) séparés par un retour à la ligne.

Comment formuler le milieu de ma macro pour comptabiliser cela dans DESTINATION ? C'est l'objet de ma venue ici.
Pouvez-vous y jeter un coup d’œil et m’aider ,

Cordialement,
Webperegrino

 

[Gégé-45550]

Maintenant je suis contrait de devoir reformuler tout cela pour arriver à remplir les cellules de DESTINATION en tenant compte des zéro, un ou plusieurs prénoms de ORIGINE (non limités en nombre !) séparés par un retour à la ligne.

Bonjour,
Voici un bout de code pour retrouver dans la variable tableau "Noms" les prénoms des personnes (exemple pour la cellule AA28 de l'onglet "ORIGINE").

Sub Test()
Dim Noms, Contenu$, i&
    Contenu = ThisWorkbook.Worksheets("ORIGINE").Range("AA28")
'    Debug.Print "* " & Contenu & " *" & vbLf
    Noms = Split(Contenu, vbLf)
    For i = 0 To UBound(Noms)
    Debug.Print Noms(i) '& vbLf & "----------------"
    Next
End Sub

Cordialement,

 

[job75]

Bonjour Webperegrino, Gégé-45550,

Il me semble que cette formule en C1 de la feuille DESTINATION suffit :

=NB.SI(ORIGINE!$U28:$AV37;"*"&C2&"*")

à tirer vers la droite.

Et tâchez d'éviter les références circulaires en AU2 et AV2...

A+

 

[Webperegrino]

Bonjour Le Forum,
Bonjour Gégé-45550,
Bonjour Job75,
Mon objectif est de réussir le bon remplissage des zones [C3:C28] de la feuille DESTINATION par des lignes VBA.
Ainsi je trouvais le contenu de C1 à DC2 plus simple... (=SOMME(C$3:C$52))... tiré sur la droite.
Quant à la proposition de Gégé-45550 je n'arrive pas à l'appliquer ; à priori, pour vérification, Noms = Split(Contenu, vbLf) en AA38 ne donne que Anne comme résultat.
Je continue mes recherches de lignes de programmation de mon côté.
En tout cas je vous remercie tous les deux d'avoir tenté une solution, et je garde espoir de recevoir une plus favorable.
Cordialement,
Webperegrino

 

[Gégé-45550]

Quant à la proposition de Gégé-45550 je n'arrive pas à l'appliquer ; à priori, pour vérification, Noms = Split(Contenu, vbLf) en AA38 ne donne que Anne comme résultat.

re
N'avez-vous pas remarqué que Noms est un tableau de valeurs ?
Copier la Sub Test et faites F5, vous verrez s'afficher les différentes valeurs contenues dans Noms() dans la fenêtre de débogage.
Cdlt,

 

[Webperegrino]

Désolé Gégé-45550,
Je ne suis pas du tout familiarisé à l'usage des lignes du #2 et ai du mal à en comprendre leur signification.
J'ai copié Test() dans un module ; Développeur + Macro + Test + Exécuter ne me donne rien.
Dans Feuil ORIGINE faire touche F5, choisir V28 m'indique bien le détail de la cellule avec prénoms et leurs retour à la ligne, le tableau Destination ne se complète toujours pas en tenant compte de la répartition de tous les prénoms des cases ORIGINE pour savoir combien de fois ils sont avec un ou des autres.
Meric
Webperegrino

 

[Webperegrino]

Le Forum,
Désolé Hasco, je l'avais modifié et mal replacé ensuite (en #1).
Webperégrino.

 

[job75]

Je ne comprends pas Webperegrino que vous vous polarisiez sur le VBA.

S'il faut encadrer les prénoms cherchés par des renvois à la ligne mettez en DESTINATION!C1 :

=SOMMEPROD(--ESTNUM(CHERCHE(CAR(10)&C2&CAR(10);CAR(10)&ORIGINE!$U28:$AV37&CAR(10))))

 

[Webperegrino]

Le Forum,
J'ai "bidouillé par formule en colonnes AX à AZ pour procéder à l'extraction et au comptage, mais ça coince en 4 ème ligne ajoutée dans la macro".
Ci-joint mon essai rectifié.
Webperegrino

 

[Webperegrino]

Le Forum,
Glups, Job 75.
Merci
Je vais appliquer votre dernière formule et étudier le résultat.
Mais actuellement je dois m'absenter pour un spectacle.
Webperegrino

 

[Gégé-45550]

Désolé Gégé-45550,
Je ne suis pas du tout familiarisé à l'usage des lignes du #2 et ai du mal à en comprendre leur signification.

re

Sub Test()
Dim Noms, Contenu$, i&
    Contenu = ThisWorkbook.Worksheets("ORIGINE").Range("AA28")
    Noms = Split(Contenu, vbLf)
    For i = 0 To UBound(Noms)
        Debug.Print Noms(i) '& vbLf & "----------------"
    Next
End Sub

ce bout de code vous permet d'obtenir dans une variable tableau nommée Noms() le liste des prénoms inscrits dans une cellule donnée, ici, pour exemple, dans la cellule AA28 dont le contenu (5 prénoms séparés par un retour à la ligne) est copié par commodité dans la variable "Contenu".
la fonction "Split", à laquelle on fournit le séparateur "Retour à la ligne", dont la constante VBA est "vbLf", sépare la chaine "Contenu" avant et après chaque séparateur (vbLf) et mémorise autant de bouts de chaîne qu'elle trouve dans le tableau Noms.
Ici, elle trouve 5 prénoms et les mémorise consécutivement dans Noms(0), Noms(1), Noms(2), Noms(3) et Noms(4) puis les imprime dans la fenêtre de débogage.
Si cette démarche vous convient, il vous appartient maintenant de créer le code VBA pour balayer toutes les cellules de type "AA28" et d'exploiter les résultats des tableaux "Noms()" dans votre feuille "DESTINATION".
Je vous recommande toutefois de regarder avant si la solution proposée par @job75 vous convient pour répondre à votre problématique.
Cordialement,

 

[Webperegrino]

Le Forum,
Ci-joint le fichier rectifié avec la technique de Job75.

1 - Merci Gégé-45550 !
Je commence à comprendre cette programmation, toute nouvelle pour moi, et vais étudier la façon de compléter le fichier dans sa partie DESTINATION.[C3C30].

2 - Job75 :
Vous avez eu raison d'insister pour que j'étudie la formule que vous placiez en C1.
Après réflexion, je l'ai adaptée et améliorée en DESTINATION.[C1]

Ensuite, je l'ai repensée entre Destination.[C3] et DESTINATION.[C30].
J'ai fait glisser ce pavé de formules vers la droite, jusqu'en zone [DC3C30] et, oh miracle ! Votre formulation donne parfaitement satisfaction !
Tout s'affiche parfaitement. Merci beaucoup.
Je peux détecter où les personnes ont trop de prestations par rapport aux autres.
Le tableau s'ajuste automatiquement dès qu'une valeur est modifiée dans ORIGINE.
C'est parfait pour moi.
Quant à savoir si du VBA sera moins mangeur d'octets, avec la proposition de Gégé-45550... ça reste à voir.
En tout cas me voilà avec une solution toute prête pour mon vrai fichier de travail (de bénévole) dans l'association.

3 - Merci à Hasco également d'avoir tenté de jeter un coup d'œil au fichier temporairement disparu, le temps d'une rectification. J'ai mis le bouton de commande VBA en repos pour l'instant, avec un message d'ouverture lorsqu'on l'active.

Bien cordialement à vous trois.
Webperegrino

 

[Webperegrino]

Bonjour Le Forum,

Je me permets de vous soumettre un autre problème que je rencontre dans la feuille TABLEAU CARRÉ du fichier ci-joint.

Je prends ici l'exemple pour Anne dans les deux feuilles ORIGINE et TABLEAU CARRÉ.
Combien de fois apparaît-elle ? 3 fois : c'est donné en G2 de la Feuille « TABLEAU CARRÉ » avec la formule magique de JOB75 :
=SI(G4="";"";SOMMEPROD(--STNUM(CHERCHE(CAR(10)&G4&CAR(10);CAR(10)&ORIGINE!$U$28:$AV$37&CAR(10)))))

Dans la Feuille ORIGINE on lit dans les cellules :
Cellule ORIGINE![AA38] : ANNE * COLETTE * GUYLÈNE * MARTINE C * MICHELLE
Cellule ORIGINE![AA29] : ANNE * BRIGITTE A * DIDIER * GUYLÈNE
Cellule ORIGINE![AP30] : ANNE * GILBERTE * KAËLIG * MARIE * MARTINE C * OTHILIE * SEBASTIAN * FRANCOIS G *FRANÇOIS LR

Compte tenu que Anne apparaît dans les trois cellules précédentes, quelles formules dois-je placer dans le pavé [C5 :C109] de la Feuille « TABLEAU CARRÉ » afin de trouver les bons résultats aux croisements des personnes concernées ?

Sauf en cellule K9 (où j’ai implanté une formule qui fonctionne mal) j’ai mis une simulation sans formule avec les croisements chiffrés jaunes et bleus dans ce pavé…
Aux croisement Anne avec … les formules devraient indiquer pour…
Colette : 1
Guylène : 2
Martine C : 2
Michelle : 1
Brigitte A : 1
Didier : 1
Gilberte : 1
Kaëlig : 1
Marie : 1
Othilie : 1
Sebastian : 1
François G : 1
François LR : 1

Pouvez-vous m’orienter pour trouver cette formulation de comparaison entre les deux feuilles ORIGINE et TABLEAU CARRÉ ?

Merci d’avance.

Webperegrino

 

[Webperegrino]

Bonjour Le Forum,
J'ai essayé en vain avec ceci,

en D5 (CTRL+maj+Entrée) puis glissé : {=SI(D$4="";"";SOMME(SI((ORIGINE!$U$28:$AV$37=D$4)+(ORIGINE!$U$28:$AV$37=$B5);1;0)))}

et en C109 (CTRL+maj+Entrée) puis glissé :
{=SI($B109="";"";SOMME(SI((ORIGINE!$U$28:$AV$37=$B109)+(ORIGINE!$U$28:$AV$37=C$4);1;0)))}

On s'approche des résultats mais cette formule me remplit toute les colonnes H, AM, avec toujours la même valeur incohérente avec ce qui est placé dans la Feuille ORIGINE.
Webperegrino

 

[Webperegrino]

Bonjour Le Forum,
J'ai bien réussi à évoluer dans mon travail de vba pour faire tourner l'application Excel selon les conseils de Job75 et Gégé-45550 (#1).

En continuité du #13 et #14, je vais devoir ouvrir une nouvelle discussion, à cause d'un nouveau souci

Celui-ci est survenu lors de l'analyse extraction et comptage des prénoms placés en groupe dans une cellule.
La prochaine discussion consistera à explorer quand un prénom se trouve seul dans une cellule, et quand ce prénom est dans un groupe, pour définir combien de fois, avec ces deux possibilités, il apparaît dans le tableau d'Origine, le résultat étant placé en fin de calculs dans un tableau Destination (nouvelle feuille).
Je reviens donc prochainement vers vous pour exposer mes requêtes.
Cordialement,
Webperegrino