XL 2016 Transformation d'une table de référence pour concordance.

[MCOTTEND]

Ma problématique :

Dans mon fichier de départ j'ai les valeurs ci-dessous séparées par une virgule :

. YT,

. Col,

. Mi,

. Sn,

. Sg,

. AFA,

. Un,

. WAD,

. MTM,

. De,

Ma table de référence varie en nombre de lignes. Toutes ces valeurs sont de longueurs variables et de typologie différentes :

. nombre + lettre,

. Lettre + nombre,

. nombre seul,

.etc.

Mon souhait :

Mettre leurs valeurs dans des colonnes individualisées comme le montre l'exemple à l'aide d'une macro. Et c'est là que je coince complètement, mes connaissance de programmation n'étant pas suffisantes pour mener mon projet à destination. Est-ce quelqu'un du forum pourrait m'aide ?

Je joins un fichier exemple qui facilitera la compréhension de chacun. Merci d'avance à toutes et tous.

 

[chris]

Bonjour

Une solution PowerQuery

Actualiser par Données, Actualiser Tout

 

[mapomme]

Bonjour à tous,

Par Formule en F7 à recopier vers la droite et vers le bas :

=SIERREUR(SUPPRESPACE(STXT(GAUCHE(STXT($B7;CHERCHE(F$6;$B7);99);CHERCHE(",";STXT($B7;CHERCHE(F$6;$B7);99) &",")-1);NBCAR(F$6)+4;99));"")

nota : il faut d'abord avoir saisi les valeurs qui vont bien sur la ligne 6 à compter de F6.

 

[_Thierry]

Bonjour @MCOTTEND Bienvenu sur XLD !, Bonjour @chris @mapomme

Tu es gâté pour ta première visite, trois solutions pour le prix d'une !!!

• PowerQuery (Merci Chris)
• Formule (Merci MaPomme)
• Et Maintenant VBA ...

Je me suis appliqué à un petit exercice pour vérifier mes retours, car ton propre tableau des résultats attendus fait manuellement est érroné à certains endroits... (!)

Avec MaPomme on est 100% matching 😉
Avec Chris, je pense qu'une petite modif est nécessaire sur la requête pour "FR012.04" notamment.

Bonne découverte
et bonne journée
@+Thierry

 

[chris]

BOnjour à tous

J'étais déjà dans les bras de Morphée, pour compliquer à ce point inutilement

Voici un PowerQuery bien plus simple et corrigé pour le FR sans espace

 

[_Thierry]

Re à tout le monde !

Bon on est tous d'accord !!

Et Chris avec son PowerQuery nous remonte même les 472 en "MTM doublette" dans :

La seule chose ce n'est plus aligné, mais peut-être ça n'a aucune importance pour le demandeur !

Bien à vous
@+Thierry

 

[chris]

RE

Si on veut retrouver la logique des lignes initiales
v2 sans conserver le classement des colonnes par 1er, 2ème, etc des lignes
v3 en conservant ce classement

Doublette supprimée

 

[mapomme]

Je n'avions point vu hier soir : Bienvenue @MCOTTEND 🙂

Et bonjour à @chris 😉 et à @_Thierry 😉.

 

[mapomme]

Re,

En VBA, je trouve plus simple d'utiliser une fonction personnalisée : =Extract(dans;ref) où dans est le texte au sein duquel rechercher la référence ref. Extract traite la première occurrence de ref au sein du texte dans.
Si on ajoute un 3ème argument (n'importe lequel) alors Extract traite la dernière occurrence de ref au sein du texte dans. Exemple : =Extract(dans;ref;1)

Le code est dans module1:

Public Function Extract(dans As String, ref As String, Optional dernier) As String
Dim t, s As String
   t = Filter(Split(dans, ","), ref, , vbTextCompare)
   If UBound(t) < LBound(t) Then Exit Function
   s = IIf(IsMissing(dernier), Trim(t(0)), Trim(t(UBound(t))))
   s = "" & Trim(Mid(s, Len(ref) + 4))
   Extract = s
End Function

 

[MCOTTEND]

Bonjour @MCOTTEND Bienvenu sur XLD !, Bonjour @chris @mapomme

Tu es gâté pour ta première visite, trois solutions pour le prix d'une !!!

• PowerQuery (Merci Chris)
• Formule (Merci MaPomme)
• Et Maintenant VBA ...

Je me suis appliqué à un petit exercice pour vérifier mes retours, car ton propre tableau des résultats attendus fait manuellement est érroné à certains endroits... (!)

Avec MaPomme on est 100% matching 😉
Avec Chris, je pense qu'une petite modif est nécessaire sur la requête pour "FR012.04" notamment.

Bonne découverte
et bonne journée
@+Thierry

Merci à tous pour vos différentes solutions.
Elles répondent à 100% à mon besoin.
Celle qui correspond le mieux à mon usage est celle de Thierry en VBA.
Une petite question complémentaire pour ce code :
Dans la ligne "Set RngSource = WS.Range("B7:B" & WS.Range("B15").End(xlUp).Row))", comment mettre en variable "B15" puisque mes fichiers ont tous un nombre de lignes différent ?
Si c'est possible, ce serait merveilleux. Plus besoin d'entrer dans le code pour changer manuellement la valeur "B15".
Cordialement à tous.
Michel.

 

[_Thierry]

Bonjour @MCOTTEND , @chris @mapomme , le Forum

Heureux que tu aies trouvé ton bonheur ! 😉

Pour ceci

Set RngSource = WS.Range("B7:B" & WS.Range("B15").End(xlUp).Row))

Ne t'inquiètes pas c'était pour la démo vu, qu'il y avait d'autres lignes en dessous !

En fait "B15" peut être "B500" c'est le End(xlUp) qui trouvera la dernière lignes NON-Vide ne partant du bas...

Donc si tes fichiers ont un nombre variable de ligne, tu fixes à 500 ou 100 sur la base du fichier qui a le plus de lignes... Par contre ils doivent tous démarrer à "B7" ....

Evidemment ce peut être (logiquement, si il y a une ligne d'entête en 1ère Ligne) :
Set RngSource = WS.Range("A2:A" & WS.Range("A500").End(xlUp).Row))

Bien à toi, à vous
@+Thierry

 

[chris]

Bonjour à tous

Mettre la source sous forme de tableau structuré reste la meilleure options ainsi le nombre de lignes est aisément géré

 

[MCOTTEND]

Bonjour @MCOTTEND , @chris @mapomme , le Forum

Heureux que tu aies trouvé ton bonheur ! 😉

Pour ceci


Ne t'inquiètes pas c'était pour la démo vu, qu'il y avait d'autres lignes en dessous !

En fait "B15" peut être "B500" c'est le End(xlUp) qui trouvera la dernière lignes NON-Vide ne partant du bas...

Donc si tes fichiers ont un nombre variable de ligne, tu fixes à 500 ou 100 sur la base du fichier qui a le plus de lignes... Par contre ils doivent tous démarrer à "B7" ....

Evidemment ce peut être (logiquement, si il y a une ligne d'entête en 1ère Ligne) :
Set RngSource = WS.Range("A2:A" & WS.Range("A500").End(xlUp).Row))

Bien à toi, à vous
@+Thierry

Bonsoir Thierry
J'avais bien compris que je pouvais changer la valeur "B17". Je souhaitais juste mettre cette référence en variable pour éviter d'avoir un nombre de lignes trop important pour certains de mes fichier qui dépassent les 10 000 lignes.
Je pense avoir trouver la solution en modifiant le début de ta macro par :

Cell_Fin = "B" & Right((Columns("B:B").Find("*", Range("B1"), , , xlByRows, xlPrevious).Offset(10, 0).Address), 3)
Set WS = ThisWorkbook.Worksheets(ActiveSheet.Name)
Set RngSource = WS.Range("B7:B" & WS.Range(Cell_Fin).End(xlUp).Row)
Set RngCible = WS.Range("F6:O6")

Encore merci pour ta proposition.
Ce week-end je vais traiter quelques fichiers afin de voir si tout se déroule correctement et te tiens informé.
Bonne soirée.
Cordialement.
Michel

 

[_Thierry]

Bonjour Michel, le Fil, e Forum

un nombre de lignes trop important pour certains de mes fichier qui dépassent les 10 000 lignes.

Ouh là, + de 10 000 lignes ... Ca va mettre des plombes ! Dans ce cas le PowerQuery risque d'être mieux adapté !

Bon courage !
@+Thierry

 

[mapomme]

Bonjour à tous,

Une version VBA plutôt rapide (moins de 0,3 s pour 12 259 lignes traitée)

• Cliquer sur le bouton Hop !
• Sélectionner la cellule d'en-tête de la colonne Références (ici B6)
• Sélectionner la première cellule de la ligne d'en-têtes des résultats (ici F6)
• Répondez à la question en cas de doublon sur une ligne
• La colonne des références peut être sélectionnée sur n'importe quelle feuille de n'importe quel classeur ouvert
• La 1ère cellule de la plage résultat peut-être sélectionnée sur n'importe quelle feuille de n'importe quel classeur ouvert
• Les références et les résultats ne sont pas forcément sur le même feuille ni dans le même classeur
• on peut choisir si en cas de doublon dans une référence on retiens la dernière ou la première occurrence

nota : on peut, à partir du bouton Hop, traiter n'importe quelle colonne de références située dans n'importe quel classeur ouvert sans que ce dernier ne comporte la macro.