RECHERCHE date la plus récente dans un fichier comprenant des doublons

SophieCanada

XLDnaute Nouveau
Bonjour,

Dans le fichier AideSVP si joint, dans mon onglet résultat, j'ai besoin d'afficher la date (valide jusqu'à) la plus récente des cours (C2:G2) suivis par les employés. Mes données sont dans l'onglet données mais dans cet onglet j'y trouve des doublons. Comment faire pour avoir seulement la date "valide jusqu'à" la plus récente dans l'onglet résultat?

Merci pour votre aide!
 

Fichiers joints

david84

XLDnaute Barbatruc
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Bonsoir,
à tester :
Code:
=SI(MAX(SI((Données!$B$2:$B$27=Résultat!$B2)*(Données!$D$2:$D$27=Résultat!C$1);Données!$F$2:$F$27))>0;MAX(SI((Données!$B$2:$B$27=Résultat!$B2)*(Données!$D$2:$D$27=Résultat!C$1);Données!$F$2:$F$27));"")
ou
Code:
MAX(SI((Données!$B$2:$B$27=Résultat!$B2)*(Données!$D$2:$D$27=Résultat!C$1);Données!$F$2:$F$27))
+ une MFC pour cacher les 0.
A+
 

R@chid

XLDnaute Barbatruc
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Bonsoir et Bienvenue sur XLD,
Bonsoir David84,
Une autre pour faire plus courte, en C2,
Code:
=SIERREUR(GRANDE.VALEUR(SI((Données!$A$2:$A$27=$A2)*(Données!$D$2:$D$27=C$1);Données!$F$2:$F$27);1);"")
@ valider par Ctrl+Maj+Entree
@ tirer vers le bas et vers la droite

Attention tes cellules sont au format Texte, il vaut mieux de les mettre en format Standard ou Date avant d'appliquer la formule..

Voir PJ

@ + +
 

Fichiers joints

job75

XLDnaute Barbatruc
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Bonjour SophieCanada, David, R@chid,

Si la base de données est très grande, les formules matricielles prennent du temps.

Voici une macro VBA à placer dans le code de la feuille Résultat :

Code:
Private Sub Worksheet_Activate()
Dim t, ub&, derlig&, dercol%, resu, i&, empl$, prem&, j%, cour$, dat&, k&
'---tableau source--
With Feuil1 'CodeName de la feuille
  .[A:F].Sort .[A1], Header:=xlYes, DataOption1:=xlSortTextAsNumbers 'tri
  t = .Range("A2:F" & .Cells(.Rows.Count, 1).End(xlUp).Row)
  ub = UBound(t)
End With
'---tableau résultat---
derlig = Cells(Rows.Count, 1).End(xlUp).Row
dercol = Cells(1, Columns.Count).End(xlToLeft).Column
resu = [A1].Resize(derlig, dercol)
For i = 2 To derlig
  empl = CStr(resu(i, 1)) 'valeur texte
  For prem = 1 To ub
    If CStr(t(prem, 1)) = empl Then Exit For
  Next
  For j = 3 To dercol
    resu(i, j) = "" 'RAZ
    cour = resu(1, j)
    dat = 0
    For k = prem To ub 'détermination de la date la plus récente
      If CStr(t(k, 1)) <> empl Then Exit For
      If t(k, 4) = cour Then If t(k, 6) > dat Then dat = t(k, 6)
    Next
    If dat Then resu(i, j) = dat
  Next
Next
'---restitution---
[A1].Resize(derlig, dercol) = resu
Rows(derlig + 1 & ":" & Rows.Count).ClearContents
Range(Columns(dercol + 1), Columns(Columns.Count)).ClearContents
End Sub
La macro s'exécute quand on active la feuille.

Remarque 1 : les valeurs en colonne A des 2 feuilles peuvent être sous forme de nombres ou de textes.

Remarque 2 : les valeurs en colonne A et B de la feuille Résultat peuvent être entrées automatiquement, ce sera l'objet d'une seconde version que je prépare.

Fichier joint.

A+
 

Fichiers joints

Dernière édition:

job75

XLDnaute Barbatruc
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Re,

Seconde version avec le remplissage automatique des colonnes A et B de la feuille Résultat :

Code:
Private Sub Worksheet_Activate()
Dim t, ub&, d As Object, i&, a, b, dercol%, resu(), titres
Dim prem&, empl$, j%, cour$, dat&, k&
'---tableau source--
With Feuil1 'CodeName de la feuille
  .[A:F].Sort .[A1], Header:=xlYes, DataOption1:=xlSortTextAsNumbers 'tri
  t = .Range("A2:F" & .Cells(.Rows.Count, 1).End(xlUp).Row)
  ub = UBound(t)
End With
'---définition du tableau résultat---
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To ub
  If Not d.exists(CStr(t(i, 1))) Then d(CStr(t(i, 1))) = i 'valeur texte
Next
a = d.keys: b = d.items
dercol = Cells(1, Columns.Count).End(xlToLeft).Column
ReDim resu(1 To d.Count, 1 To dercol)
titres = [A1].Resize(, dercol)
'---remplissage du tableau résultat---
For i = 1 To d.Count
  prem = b(i - 1)
  resu(i, 1) = a(i - 1): resu(i, 2) = t(prem, 2)
  empl = resu(i, 1)
  For j = 3 To dercol
    cour = titres(1, j)
    dat = 0
    For k = prem To ub 'détermination de la date la plus récente
      If CStr(t(k, 1)) <> empl Then Exit For
      If t(k, 4) = cour Then If t(k, 6) > dat Then dat = t(k, 6)
    Next
    If dat Then resu(i, j) = dat
  Next
Next
'---restitution---
[A2].Resize(d.Count, dercol) = resu
Rows(d.Count + 2 & ":" & Rows.Count).ClearContents
Range(Columns(dercol + 1), Columns(Columns.Count)).ClearContents
End Sub
Fichier (2), j'ai aussi créé une MFC pour les bordures et la couleur de fond.

Remarque
: pour éviter tout problème avec les valeurs textes, dans le tri initial, j'ai ajouté l'option :

DataOption1:=xlSortTextAsNumbers

Je vais l'ajouter aussi sur la version (1).

A+
 

Fichiers joints

Dernière édition:

R@chid

XLDnaute Barbatruc
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Bonjour @ tous,
Juste pour passer un salut à Job75 :),
Excellent travail, le seul souci c'est que je n'ai rien compris ;):D
@ + +

Edit :

Ohhh si je maitrise le VBA comme vous le maitrisez !?!?!?
 
Dernière édition:

job75

XLDnaute Barbatruc
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Re,

La version (2) restitue des valeurs textes en colonne A de la feuille Résultat.

Si l'on est sûr qu'en colonne A de la feuille Données il n'y a que des chiffres, on peut utiliser les valeurs nombres.

Par ailleurs utiliser Option Compare Text pour que la casse soit ignorée (Pomme = pomme) :

Code:
Option Compare Text 'la casse est ignorée

Private Sub Worksheet_Activate()
Dim t, ub&, d As Object, i&, a, b, dercol%, resu(), titres
Dim prem&, empl&, j%, cour$, dat&, k&
'---tableau source--
With Feuil1 'CodeName de la feuille
  .[A:F].Sort .[A1], Header:=xlYes, DataOption1:=xlSortTextAsNumbers 'tri
  t = .Range("A2:F" & .Cells(.Rows.Count, 1).End(xlUp).Row)
  ub = UBound(t)
End With
'---définition du tableau résultat---
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To ub
  If Not d.exists(Val(t(i, 1))) Then d(Val(t(i, 1))) = i 'valeur nombre
Next
a = d.keys: b = d.items
dercol = Cells(1, Columns.Count).End(xlToLeft).Column
ReDim resu(1 To d.Count, 1 To dercol)
titres = [A1].Resize(, dercol)
'---remplissage du tableau résultat---
For i = 1 To d.Count
  prem = b(i - 1)
  resu(i, 1) = a(i - 1): resu(i, 2) = t(prem, 2)
  empl = resu(i, 1)
  For j = 3 To dercol
    cour = titres(1, j)
    dat = 0
    For k = prem To ub 'détermination de la date la plus récente
      If Val(t(k, 1)) <> empl Then Exit For
      If t(k, 4) = cour Then If t(k, 6) > dat Then dat = t(k, 6)
    Next
    If dat Then resu(i, j) = dat
  Next
Next
'---restitution---
[A2].Resize(d.Count, dercol) = resu
Rows(d.Count + 2 & ":" & Rows.Count).ClearContents
Range(Columns(dercol + 1), Columns(Columns.Count)).ClearContents
End Sub
Fichier (3).

A+
 

Fichiers joints

Dernière édition:

SophieCanada

XLDnaute Nouveau
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Bonjour à tous,

Merci beaucoup pour vos réponses rapides. Je vais tenter de reproduire la solution de Job75 puisque j'ai près de 2000 employés avec plus de 20 formations différentes à valider les échéances afin de s'assurer que toutes les formations requises à un poste de travail spécifique aient été faites.

N'ayant jamais fait de macro, je vais attendre une collègue, qui reviens la semaine prochaine pour m'aider à la créer. Pour l'instant vos codes me semblent fantastiques et le résultat dans le fichier aideSVP(2) est exactement ce que j'ai besoin, mais je n'ai aucune idée comment les utiliser. PS Je n'ai pas pu voir votre fichier aideSVP(3) car il n'était pas accessible.

Je vous reviens avec le résultat finale la semaine prochaine!

Merci encore pour vos réponses!

Sophie
 

job75

XLDnaute Barbatruc
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Re,

Pour les fichiers (2) et (3) je viens de modifier le code de l'objet d (Dictionary).

Les d.Items mémorisent maintenant le numéro de la 1ère ligne de chaque nom (prem).

La macro sera nettement plus rapide qu'auparavant.

A+
 

R@chid

XLDnaute Barbatruc
Re : RECHERCHE date la plus récente dans un fichier comprenant des doublons

Bonsoir @ tous,
Re R@chid,



Bah je suis sûr que sous peu tu vas te mettre au VBA, comme l'a fait David :D

A+
Oui je dois m'y mettre, un jour ou un autre j'en aurai besoin à titre professionelle

@ + +
 

Créez un compte ou connectez vous pour répondre

Vous devez être membre afin de pouvoir répondre ici

Créer un compte

Créez un compte Excel Downloads. C'est simple!

Connexion

Vous avez déjà un compte? Connectez vous ici.

Haut Bas