XL 2019 extraire toute les cellules d'une colonne ayant une date identique

[cytise95]

Bonjour,

J'ai beau cherché mais je ne trouve pas de solution à mon besoin ci-dessous :
Ayant une colonne « A » avec une liste de noms, une colonne « B » avec des dates et en colonne « E » un calendrier mensuel.
Je dois récupérer tous les noms ayant la même date en face du jour identique du calendrier dans la colonne « F » ils peuvent tous être dans la même colonne et séparés par un « ; ». Si impossible dans d’autres colonnes.
Je réussi à obtenir seulement le 1er nom correspondant à une date mais pas les éventuels noms suivants, certains en ont 4.
Afin d’être plus explicite, je joins un extrait de mon fichier.

S’il y à une solution je suis preneur.

Merci. Cordialement

 

[Hasco]

Bonjour,

Dans la formule de F5, pour le numéro de ligne vous trouverez la formule :

AGREGAT(15;6;LIGNE($B$5:$B$38)-4/($B$5:$B$38=$E5);1))

Qui permet d'appliquer la fonction Petite.Valeur et d'en retourner un numéro de ligne (-4 pour entête) le dernier argument représente la nième petite valeur souhaitée 1 pour première colonne, 2 pour deuxième etc.

Cordialement

 

[job75]

Bonjour cytise95, bienvenue sur XLD, Roblochon,

Sélectionnez F5:I5, entrez la formule dans la barre de formule et validez matriciellement en bloc par Ctrl+Maj+Entrée :

=SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(B$5:B$38=E5;LIGNE(B$5:B$38));COLONNE(A:D)));"")

A+

 

[job75]

J'ai testé avec une boucle de 1000 itérations sur des formules rendues volatiles.

Les formules matricielles ou celles avec AGREGAT prennent 11,5 secondes chez moi.

[Edit] je repère mon 34000 ème message.

 

[cytise95]

Merci beaucoup a vous,
Je réussi a appliquer la formule utilisant "AGREGAT" (que n’ignorait) dans mon gros fichier, cependant impossible de refaire la matrice dans mon fichier j’obtiens uniquement des cellules vides..
Je vais donc utiliser AGREGAT qui répond à mon attente finale.
Merci beaucoup à tous les deux , je m'absente quelques heures, je vais retester un peu plus tard.
Cordialement

 

[cytise95]

Bonjour Job75,
J'ai beau essayer la formule : SIERREUR(INDEX(A:A;PETITE.VALEUR(SI(B$5:B$38=E5;LIGNE(B$5:B$38));COLONNE(A)));""), mais impossible de l'appliquer dans mon fichier.
J'obtiens uniquement des cellules vides. Je constate que le " =E5 " reste identique sur toutes les lignes, je pense que les cellules devraient s'incrémenter. Autrement je n'ai pas compris " COLONNE(A) " les colonnes C & D ne sont pas utilisées. Dans le fichier test ci joint voir colonnes ajoutées de K à O
Selon votre tableau ça fonctionne, je ne comprend pas mon erreur et je m'arrache les cheveux.
Je ne peux pas valider votre réponse en "non pertinente" vu c'est très certainement moi la cause de l'échec.

 

[job75]

Bonjour,

Ce que j'ai indiqué est clair :

Sélectionnez F5:I5, entrez la formule dans la barre de formule et validez matriciellement en bloc par Ctrl+Maj+Entrée

A+

 

[cytise95]

Effectivement c'est assurément très clair.
Pourtant, je ne suis pas assez attentif, je sélectionnais tout le tableau F5:I32 au lieu de la 1ere ligne.
il faut juste tirer cette 1ere ligne jusqu'en bas du tableau.
Impensable de buter si longtemps sur mon erreur.
Donc merci beaucoup votre formule répond également à mon attente.

 

[cytise95]

Bonsoir,
Encore moi.....
Je constate que mettre automatiquement les noms dans un calendrier c'est parfait, cependant, je copie manuellement les listes de noms dans 4 colonnes différentes pour les regrouper dans 1 seule.
Je pense qu'il serait bien mieux que cette liste soit compilée automatiquement.
J'ai fait des test en vain, voir extrait du fichier ci-joint.
Dans la syntaxe utilisée il faudrait que toutes les colonnes ont un nombre de lignes identiques ce qui n'est pas mon cas. Ce qui fait que tout les noms ne sont pas copié.
De plus dans le fichier original, les cellules apparemment vides contiennent une formule. A défaut d'un texte il y a : « » ce qui n'est pas toujours pris pour une cellule vide.

Si quelqu'un à une idée j'en serai ravi.
Cordialement
Christian

 

[job75]

Bonjour cytise95,

Ce n'est pas très malin de mettre le texte "lun 05 juil - dim 11 juil" dans la cellule fusionnée A2.

Mettez les 2 dates dans les cellules A2 et B2.

A+

 

[cytise95]

Bonjour cytise95,

Ce n'est pas très malin de mettre le texte "lun 05 juil - dim 11 juil" dans la cellule fusionnée A2.

Mettez les 2 dates dans les cellules A2 et B2.

A+

Bonsoir, Job75,

Sauf erreur, cela n'est que pour info et donc n'a aucune incidence sur la formule DECALER($A$3; MOD(LIGNE()-3;NB.SI($A$3:$A$32;">*<")); ENT((LIGNE()-3)/NB.SI($A$3:$A$32;">*<"))*2).

J'ai effacé la ligne 2, cela ne gêne pas.

Merci quand même

 

[job75]

J'ai effacé la ligne 2, cela ne gêne pas.

Bien sûr puisque les données des colonnes A à H sont entrées manuellement.

Mais il faudra bien mettre des dates en ligne 2 si les données sont entrées par formules ou par macro.

Quant à votre formule en colonne J elle ne va pas, je l'ai remplacée par cette fonction VBA :

Function RecapNom$(R As Range, ordre&)
Dim tablo, e, x$, n&
tablo = R.Value2 'matrice, plus rapide
For Each e In tablo
    x = Trim(CStr(e))
    If x <> "" Then
        If Not IsNumeric(x) Then
            n = n + 1
            If n = ordre Then RecapNom = x: Exit Function
        End If
    End If
Next
End Function

Le code doit être impérativement dans un module standard.

Formule en J3 =RecapNom(A$3:G$32;LIGNE()-2)

Nota : il y a des doublons, il serait assez facile de les éviter si vous le souhaitez.

Bonne nuit.

 

[job75]

Bonjour cytise95, le forum,

Nota : il y a des doublons, il serait assez facile de les éviter si vous le souhaitez.

Pour obtenir une liste sans doublon utilisez cette macro évènementielle dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dest As Range, d As Object, tablo, e, x$, a, b$(), i&
Set dest = [J3] '1ère cellule de destination, à adapter
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = vbTextCompare 'la casse est ignorée
tablo = Range("A3:G" & Cells.SpecialCells(xlCellTypeLastCell).Row).Value2 'matrice, plus rapide
For Each e In tablo
    x = Trim(CStr(e))
    If x <> "" Then If Not IsNumeric(x) Then d(x) = "" 'liste sans doublon
Next
Application.EnableEvents = False 'désactive les évènements
If d.Count Then
    a = d.keys
    ReDim b(UBound(a), 0) 'base 0
    For i = 0 To UBound(a): b(i, 0) = a(i): Next 'transposition
    With dest.Resize(d.Count)
        .Value = b 'restitution
        .Interior.Color = RGB(255, 255, 204) 'jaune clair
    End With
End If
dest.Offset(d.Count).Resize(Rows.Count - d.Count - dest.Row + 1).Clear 'RAZ en dessous
Application.EnableEvents = True 'réactive les évènements
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

C'est bien plus rapide que d'utiliser une fonction VBA.

La macro se déclenche quand on modifie ou valide une cellule quelconque.

A+

 

[cytise95]

Bonjour Job75,
Merci beaucoup pour cette macro qui fait exactement ce qui est attendu Super Parfait.
Concernant les noms en double, en fait ce ne sont pas des doublons mais des dates différentes pour certaines personnes.
J'ai accepté d'aider une amie pour améliorer son fichier, tout était saisie manuellement, ce qui provoquait souvent des erreurs.
Cependant cela prend du temps et pas toujours simple de trouver des solutions pour tout "automatiser" mais le plus dur est fait. Certainement qu'il y aurait moyen de simplifier, mais je fais avec mes menues connaissances.
Pour récupérer les dates des 4 colonnes a coté de la colonne recapNom, je pensai que cela aurai été simple a l'aide de "rechercheV" mais pas du tout. Je vais donc me pencher la dessus.
Merci encore de votre aide
Cordialement

 

[cytise95]

Bonjour,
Je suis toujours sur mon fichier, je souhaite récupérer les dates de 4 colonnes dans la colonne en face de la récap des noms.
Tous mes essais récupèrent la 1ére date correspondant à un nom en double. S'il n'y à pas de doublon avec "SIERREUR" cela fonctionne bien, mais impossible de trouver une autre solution satisfaisante.
Cordialement