Recherche numéro de ligne selon triple critère

[Brigitte]

Bonjour,

Il est possible de trouver un numéro de ligne répondant à plusieurs critères, je l'ai déjà vu maintes fois, mais une fois devant le fichier, j'ai beau tenter equiv... je n'y parviens pas.

Pourriez-vous m'aider ?

Il s'agit dans le fichier joint, dans l'onglet GRH, de récupérer en colonne A (par formule svp) le numéro de ligne correspondant dans l'autre onglet "archives historique". Ce numéro combine une boîte, un numéro (colonnes E et F du fichier source archives historique) et aussi un code (en G1) qui sera fixe (2.3).

Le fichier source est ici simplifié, en fait il comporte déjà 7000 lignes et donc plusieurs fois la boîte 2009-2, avec numéro 1... mais seulement une seule fois avec le Code 2.3

J'ai essayé aussi une formule avec sommeprod :

=SOMMEPROD(('ARCHIVES - Historique'!$E$2:$E$10000=GRH!B2)*('ARCHIVES - Historique'!$F$2:$F$10000=GRH!C2)*('ARCHIVES - Historique'!$D$2:$D$10000="2.3"))

mais je ne sais pas récupérer ensuite le numéro de ligne... et de toute manière ca me donne comme résultat toujours 1... Pourquoi ?

Merci à vous.

 

[Brigitte]

Re : Recherche numéro de ligne selon triple critère

Re,

Je crois être sur la piste avec ceci :

=
EQUIV(B2;'ARCHIVES - Historique'!$E$2:$E$4;0)+EQUIV(GRH!C2;'ARCHIVES - Historique'!$F$2:$F$4;0)+EQUIV(GRH!$G$1;'ARCHIVES - Historique'!$D$2:$D$4;0)-1

Mais ca doit clocher, car ca ne fonctionne pas dans mon VRAI fichier...

 

[job75]

Re : Recherche numéro de ligne selon triple critère

Bonjour Brigitte,

Essaye :

=SOMMEPROD(('ARCHIVES - Historique'!$E$2:$E$10000=GRH!B2)*('ARCHIVES - Historique'!$F$2:$F$10000=GRH!C2)*('ARCHIVES - Historique'!$D$2:$D$10000="2.3")*LIGNE('ARCHIVES - Historique'!$2:$10000))

Fonctionne si effectivement ta formule (sans LIGNE) renvoie 1

A+

 

[Brigitte]

Re : Recherche numéro de ligne selon triple critère

Yesssssssssssssss, oh la la merci beaucoup.

Je suis tarte, voilà une heure que j'essaie toutes les formules possibles.

La précédente... le souci c'est que je savais pas renvoyer le numéro de ligne, car je peux pas rajouter index, n'ayant pas envie de retourner un résultat mais le numéro de ligne.

Si tu as aussi, tant que tu y es, une idée de pourquoi ma formule me renvoit un chiffre faux sur mon GRAND et VRAI tableau..

Mais tu m'as sauvée.

 

[job75]

Re : Recherche numéro de ligne selon triple critère

Re Brigitte,

S'il y a plusieurs lignes concernées, SOMMEPROD ne va pas, utiliser GRANDE.VALEUR dans une formule matricielle.

Pour la dernière ligne trouvée :

=GRANDE.VALEUR(('ARCHIVES - Historique'!$E$2:$E$10000=GRH!B2)*('ARCHIVES - Historique'!$F$2:$F$10000=GRH!C2)*('ARCHIVES - Historique'!$D$2:$D$10000="2.3")*LIGNE('ARCHIVES - Historique'!$2:$10000);[SIZE="3"][COLOR="Red"][B]1[/B][/COLOR][/SIZE])

puis 2 pour la ligne précédente...

Mais à la place du 1 ou du 2, on peut utiliser LIGNE() ou COLONNE() et tirer la formule.

Edit : on peut aussi utiliser PETITE.VALEUR avec un test SI . Pour avoir la 1ère ligne (toujours en matriciel) :

=PETITE.VALEUR(SI(('ARCHIVES - Historique'!$E$2:$E$10000=GRH!B2)*('ARCHIVES - Historique'!$F$2:$F$10000=GRH!C2)*('ARCHIVES - Historique'!$D$2:$D$10000="2.3");LIGNE('ARCHIVES - Historique'!$2:$10000));[SIZE="3"][COLOR="Red"][B]1[/B][/COLOR][/SIZE])

ou directement avec MIN :

=MIN(SI(('ARCHIVES - Historique'!$E$2:$E$10000=GRH!B2)*('ARCHIVES - Historique'!$F$2:$F$10000=GRH!C2)*('ARCHIVES - Historique'!$D$2:$D$10000="2.3");LIGNE('ARCHIVES - Historique'!$2:$10000)))

A+

 

[Tibo]

Re : Recherche numéro de ligne selon triple critère

Bonjour Brigitte, bonjour job75

Une autre formule, petite soeur de celle de job75 (matricielle) :

=INDEX('ARCHIVES - Historique'!$F$2:$F$4;EQUIV(B2&C2;
'ARCHIVES - Historique'!$E$2:$E$4&'ARCHIVES - Historique'!$F$2:$F$4;0))+1

A valider par CTRL + MAJ + ENTREE

@+

 

[Brigitte]

Re : Recherche numéro de ligne selon triple critère

Re,

Seul souci, ca me prend trop de ressource le sommeprod sur 7000 lignes, ca met un temps fou à traiter...

Et comme le but est que ca mette à jour si je rajoute une ligne dans le fichier source, c'est un peu fastidieux...

Merci Tibo, je vais tenter aussi cela... pour voir si ca va plus vite.

 

[Brigitte]

Re : Recherche numéro de ligne selon triple critère

Tibo, il me semble qu'il manque le troisième critère (le fameux code 2.3), non ?

 

[James007]

Re : Recherche numéro de ligne selon triple critère

Bonjour à tous,

Tu peux toujours transformer ta formule en fonction personnalisée ... sous VBA, tu devrais gagner pas mal de temps sur le traitement de tes 7'000 lignes et plus ...

A +

 

[Tibo]

Re : Recherche numéro de ligne selon triple critère

re,

Euh... oui, j'avais pas tout lu ... 😱

Donc, avec une petite modif :

=INDEX('ARCHIVES - Historique'!$F$2:$F$4;EQUIV($G$1&B2&C2;
'ARCHIVES - Historique'!$D$2:$D$4&'ARCHIVES - Historique'!$E$2:$E$4&
'ARCHIVES - Historique'!$F$2:$F$4;0))+1

Toujours matricielle, donc CTRL + MAJ + ENTREE

@+

 

[Brigitte]

Re : Recherche numéro de ligne selon triple critère

Merci James... Mais bon ce fichier est déjà très complexe, et j'ai surtout pas le niveau requis.

Celle de Tibo me retourne 2 au lieu du bon numéro de ligne dans le VRAI fichier... ??? et ne tient pas compte du code qui différencie les numéro/boîte...

car il peut y avoir une boîte :

3 2009-2 1
2.3 2009-2 1
1 2009-2 1

Donc le code (ici 3, 2.3, et 1) est primordial pour retourner le bon numéro de ligne QUI SE TROUVE dans le fichier ARCHIVES HISTORIQUE (pas onglet GRH)

 

[Brigitte]

Re : Recherche numéro de ligne selon triple critère

Merci Tibo, je t'envoie mon fichier (par mail) pour que tu voies où j'ai dû faire une bêtise, car ca me retourne pas le bon numéro de ligne en S... qui doit être identique à celui retourné en A par la formule de job75.

La feuille en question (GRH) est une feuille qui se cache à la fermeture, il faut pour l'activer, cliquer sur le bouton GRH ok dans la feuille ARCHIVES HISTORIQUE (cellule G1).

Question annexe : si jamais ta formule matricielle traitait plus vite... car là mon fichier met trois heures à s'ouvrir avec le sommeprod (snif), est ce que ca se mettrait à jour tout seul en cas de modif du fichier source ? et d'autre part, il y a un lien qui se fabrique je crains que lui ne se mette pas à jour.

Je crois que je vais être obligée de renoncer...

Bon je vais m'aérer un peu et profiter du soleil, sinon je vais encore passer la journée là dessus. Mais merci tout le monde pour votre disponibilité.

 

[Tibo]

Re : Recherche numéro de ligne selon triple critère

re,

Simplification de la formule proposée plus haut (et adaptée à ton vrai fichier) :

=EQUIV($R$1&B2&C2;'ARCHIVES - Historique'!$D$2:$D$10000&
'ARCHIVES - Historique'!$E$2:$E$10000&'ARCHIVES - Historique'!$F$2:$F$10000;0)+1

Donne les mêmes résultats que la formule de Job75

Quant à la lenteur, la formule à base de SOMMEPROD ou la formule matricielle que je t'ai proposé seront forcément longues sur une base de données aussi importante.

Il est possible de nommer certaines plages de celllules, ce qui devrait faire gagner "un peu" de temps.

Une autre approche serait d'insérer une colonne dans ta feuille ARCHIVES - Historique (par exemple juste après la colonne F), d'y faire la concaténation des 3 colonnes précédentes, puis dans GRH, faire la recherche sur cette colonne.

Ainsi, plus besoin de formule matricielle.

@+

 

[Brigitte]

Re : Recherche numéro de ligne selon triple critère

Merci Tibo...

Je vais penser alors à ta solution de rajouter une colonne, mais ce sera bien loin à droite dans le tableau, car insérer, supprimer une seule colonne, flanque par terre tout le module de recherche. Et je n'ai pas Hervé sous la main, ni envie de l'embêter avec ca.

Donc je vais rajouter ca très à droite... (avec une formule de concaténation qui se rajoutera dans les lignes vides : je le rajouterai à la ligne modèle masquée qui sert de support à la macro Rajout lignes aussi).

Et après j'essaie, si je n'y parviens pas... je te demanderai.

Quelle serait alors la formule si tout est concaténé ? Je suppose qu'il faudrait que je concatène aussi dans l'onglet grh... ce qui me pose plus de question... mais c jouable.

Dis moi, juste pour le fun, ta formule : si je rajoute une ligne dans l'onglet ARCHIVES HISTORIQUE, elle va remettre à jour automatiquement les numéros dans l'onglet GRH ? Le souci restera les liens... à mon avis, eux ne vont pas suivre (macro de Roger "lien interne").

Ta formule matricielle marche du feu de dieu... pas testé sur le temps... car pour l'instant j'oublie la formule, j'attends de prendre le temps de la concaténation si réalisable sur mes deux onglets.

 

[Tibo]

Re : Recherche numéro de ligne selon triple critère

re,

Dans l'hypothèse du rajout d'une colonne (avec concaténation), la formule deviendrait :

=EQUIV($R$1&B2&C2;colonne de concaténation;0)

avec une simple validation et donc beaucoup plus rapide en temps de calcul.

L'inconvénient de cette solution, c'est en cas de rajout de ligne. Il faudra recopier la formule de concaténation dans la colonne.

Le plus simple est de te laisser tester pour voir quelle solution est la plus souple en terme d'exploitation.

Bonne fin de dimanche.

@+