XL 2016 Recherche verticale multiples résultats

[Hélène B.]

Bonjour,

J'ai beau lire d'autres discussions, il m'est impossible de trouver la bonne formule.

Mon souci est le suivant:

- J'ai un onglet base de données où se trouvent 2 types de codes: Code Barre et Code Y correspondant
- Un code barre peut être présent en doublon
- Un code Y n'a pas de doublons
- Dans un 2e onglet, j'ai une sélection de codes barre dont j'ai besoin de retrouver le code Y correspondant.

Le souci est qu'avec une RechercheV, le même code Y est renvoye (le premier trouvé par la fonction).

Or, j'ai besoin de tous les codes Y correspondants.

Mon fichier contenant des milliers de lignes, je joins un exemple simplifié:

Onglet "Matching codes": là où j'ai besoin d'appliquer la formule pour faire apparaître les différents codes Y
Onglet "BDD codes Y - total DTB" : la base de données complètes des codes Y.

En termes de format, j'aurais besoin de respecter celui déjà présent dans le 1e onglet, à savoir 2 colonnes où les code barres se répétant correspondent à un seul code Y.

Mille mercis pour votre aide,
Hélène

 

[djidji59430]

Bonjour,

Avec un TCD

 

[Hélène B.]

Bonjour djidji59430,

Merci beaucoup de votre réponse. Un TCD résoud en effet une partie de mon souci.

En revanche le problème est que mon fichier contient des milliers de lignes, et manuellement le mettre au bon format (cf onglet 1 de mon fichier, où les codes barre ont besoin d'être présentés avec 1 ligne par quantité) s'avèrera très compliqué: y aurait-il une formule capable de remonter en format vertical toutes les valeurs des "codes Y"?

Mille mercis encore et meilleurs voeux,
Hélène

 

[djidji59430]

Re
Nouvel essai

 

[BOISGONTIER]

Bonsoir,

=INDEX(codesY;PETITE.VALEUR(SI(codes=A2;LIGNE(INDIRECT("1:"&LIGNES(codes))));NB.SI(A$2:A2;A2)))


Boisgontier

 

[djidji59430]

Re
et pourquoi tu ne filtres pas ta base en fonction des codes ??

 

[Hélène B.]

Bonsoir,

=INDEX(codesY;PETITE.VALEUR(SI(codes=A2;LIGNE(INDIRECT("1:"&LIGNES(codes))));NB.SI(A$2:A2;A2)))


Boisgontier

Bonjour Boisgontier,

Meilleurs voeux et un GRAND MERCI pour votre aide.

J'ai tenté de reproduire la formule dans mon fichier global, la base fonctionne, mais malheureusement certaines cellules figées (dernière partie de la formule) impliquent de changer le numéro de ligne manuellement. Ayant 7 lignes dans mon fichier, cela s'avère compliqué. Y-a-t-il une manière de contourner ceci?

EDIT: j'ai retiré, dans cette dernière partie de la cellule, la partie figée, et cela fonctionne

Un grand merci encore!

Merci beaucoup encore,
Hélène

 

[Hélène B.]

Re
Nouvel essai

Bonsoir,

Mille mercis, vous m'avez été d"une très grande aide! J'ai essayé les deux techniques (TCD et formule) et les deux m'ont aidées: le TDC implique quelques étapes manuelles supplémentaires (j'ai découvert une fonction d'affichage qui répète les intitulés de codes, ce qui perment d'en copier-coller les valeurs pour avoir un tableau exploitable), mais c'est intéressant.

Encore merci beaucoup !

 

[chris]

Bonjour à tous

J'arrive un peu après la bataille mais un truc me dérange : il te faut savoir combien de variantes il y a pour remplir l'onglet Matching codes ce qui parait curieux...

Je pense qu'en donnant les codes à trouver, c'est à Excel de lister les variantes quel qu'en soit leur nombre.

Une solution PowerQuery, intégrée à Excel 2016, : il suffit de lister les codes à trouver puis d'actualiser Tout pour que la liste s'adapte...

 

[Hélène B.]

Bonjour à tous

J'arrive un peu après la bataille mais un truc me dérange : il te faut savoir combien de variantes il y a pour remplir l'onglet Matching codes ce qui parait curieux...

Je pense qu'en donnant les codes à trouver, c'est à Excel de lister les variantes quel qu'en soit leur nombre.

Une solution PowerQuery, intégrée à Excel 2016, : il suffit de lister les codes à trouver puis d'actualiser Tout pour que la liste s'adapte...

Bonjour Chris,

Cette solution a l'air de bien fonctionner également, merci !

En revanche, je crains de ne pas avoir l'expertise Excel suffisante pour reproduire le processus de moi-même: pourriez-vous en détailler les différentes étapes?

Pour vous donner de la visibilité: dans la vraie base de donnée totale, j'ai 10796 Codes barres qui correspondent à des Codes Y

Dans la liste finale qui m'intéresse, j'ai 7648 codes barres dont j'ai besoin de lister le Code Y correspondant.

Même si les autres solutions m'on permis de résoudre mon souci, je suis vraiment intéressée par le système de Query, qui me permettra très probablement de résoudre d'autres soucis.

Merci beaucoup,
Hélène

 

[chris]

RE

Mettre chaque liste sous forme de tableau et nommer les tableaux (j'ai appelé le 1er Tchoix et le second BDD)

Se positionner sur le 1er tableau puis dans l'onglet Données, A partir d'un tableau : cela ouvre PowerQuery.
Les codes ont été typé en numérique : dans Accueil, type de données, sélectionner Texte et confirmer le remplacement du type actuel
Sortir par le bouton Fermer et charger dans, Connexion seulement.
Faire de même avec le second tableau, mais en sélectionnant les deux colonnes pour le changement de type.

On a 2 requêtes dans l'interface PowerQuery.
Dans le volet de gauche, sélectionner Tchoix, puis Accueil, fusionner les requêtes : dans la partie haute de la fenêtre sélectionner l'unique colonne, dans la partie basse, sélectionner la requête BDD puis sa colonne Code barre et en bas type de jointure interne.
Une colonne titrée BDD s'est ajoutée à Tchoix. Cliquer sur le double flèche à droite du titre BDD, décocher tout sauf Codes Y.
Sortir par le bouton Fermer et charger dans, Table et choisir l'emplacement.

C'est tout.

Pour actualiser lorsque le liste de choix change : Actualiser Tout dans l'onglet Données

 

[djidji59430]

Bonjour,

Je suis le post pour powerquery, que je ne maitrise absolument pas. Comment obtient on la liste "code barre a lister" ??

Autrement, si on met directement la liste des 7648 codes barres dans un filtre avancé, on arrive au meme resultat ...

Non ?

Crdlmt

 

[chris]

RE

Oui djidji59430, tu as raison .

Sauf que sans VBA le filtre avancé n'est pas dynamique ni le résultat classé

Le filtre avancé et les fonctions BD... sont un peu les ancêtres de PowerQuery puisque c'étaient les seules fonctionnalités capables de traiter très rapidement des listes de données conséquentes.

 

[job75]

Bonjour Hélène B., djidji59430, JB, chris,

Voici un code pour le filtre avancé dans le fichier joint :

Dim d As Object 'mémorise la variable

Function ALister(c As Range) As Boolean
ALister = d.exists(c.Value)
End Function

Sub MAJ()
Dim tablo, i&
Set d = CreateObject("Scripting.Dictionary")
tablo = Feuil1.[A1].CurrentRegion.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
For i = 2 To UBound(tablo): d(tablo(i, 1)) = "": Next
With Feuil2.[A1].CurrentRegion 'CodeName
    .Cells(2, 4) = "=ALister(A2)"
    .AdvancedFilter xlFilterCopy, .Cells(1, 4).Resize(2), Feuil1.[C1:D1]
    .Cells(2, 4) = ""
End With
Set d = Nothing
End Sub

Normalement l'exécution est rapide, il serait intéressant de comparer avec Power Query sur de grands tableaux.

A+