Repérer toutes les occurrences dans des textes et renvoyer une référence correspondante

[mis_en_abime]

Bonjour,
Sur une grosse base que je suis en train de nettoyer pour integration sous access, on me demande une manip pour laquelle je sèche:

• D’un coté du ring, une table qui liste tout un tas de documents groupés entre eux, sous des références numérotées. Un même document peut apparaitre sous plusieurs références.
• De l’autre coté, une liste simple de tous ces documents, sans doublons, aux cotés desquels je dois afficher toutes les références dans lesquelles chacun des documents apparait.
  Je sens bien que c’est assez peu clair, désolé. => En PJ, un exemple de la manip à réaliser Classeur1.xlsx (12,5 Ko)

J’ai tenté tout un tas de solution avec RECHERCHEX INDEX et MATCH… sans succès.
Merci d’avance à ceux et celles qui trouveront cette bouteille à la mer.
François

[FFO]

Bonjour

Je ne pense pas qu’une solution par formule puisse convenir à mon avis bien que n’étant expert dans ce domaine
Je te propose une solution par macro
Cliques sur le bouton « Traitement »

Testes et dis moi

Classeur1 (1).xlsm (22,0 Ko)

[mis_en_abime]

Bonjour FFO,
Bravo, ça marche. Merci mille fois

J’essaie d’analyser la macro pour la copier sur la vraie base… …mais c’est dur …car je n’y bite rien.
C’est pas avec le bouton « enregister » les macros normalement? : D

[FFO]

Dans le deuxième classeur enregistrer une macro puis arrêter l enregistrement
Dans ce classeur
Touche CTRL+F11
Dans l éditeur de macro tout sélectionner puis CTRL+C
Dans le deuxième classeur
Même manipulation mais faire CTRL+V
Reste à mettre un bouton de commande ou utiliser la macro en allant la chercher et l exécuter
Attention macro à exécuter avec l onglet à traiter actif à l écran

Si problème ne pas hésiter à me solliciter avec tout son détail pour solution à appoter

Tiens moi informé

[FFO]

Une autre solution peut être plus simple

Ouvre le classeur de la macro et le classeur à traiter

Important : Classeur à traiter onglet à traiter actif à l’écran

Fais ceci :

bandeau excel sélectionnes le choix « Affichage » puis cliques sur l’icone « Macro » sélectionnes « Afficher les macros »

tu obtiens ceci :

en bas de la fenêtre qui vient de s’afficher dans « Macro dans » choisi le classeur de la macro

Puis dans la partie au dessus choisi la seule macro qui porte dans son nom en fin « Traitement » et cliques à droite sur le choix « Exécuter »

Cela devrait fonctionner

Attention la structure des données doit être rigoureusement la même que celle de l’outil :

Colonne B/C les données à traiter
Colonne H à O les recopies à effectuer

Essaies et dis moi

[mis_en_abime]

Bonjour FFO,
Merci beaucoup pour tout.
J’ai créé une macro dans mon classeur (après avoir tout regroupé car les données étaient sur plusieurs onglets) dans laquelle j’ai copié ton code en appliquant les modifications suivantes. La syntaxe est un peu énigmatique et je me demande si je n’ai pas fait de bêtise… exécuter la macro ne produit aucun effet.

[mis_en_abime]

Et pour plus de clarté voilà la fameuse table qui fait 6000+ lignes.

!

[FFO]

Bonjour

Dans ta copie d’écran concernant la structure de ton onglet qui doit être traité la liste des documents sur laquelle il faut que le traitement ramène les références est en colonne « R » :

Donc le début de ton code doit s’y reporter or il considère non pas la colonne R mais la colonne Y :

ta ligne

Do While i < Range(« Y » & Rows.Count).End(xlUp).Row + 1

ne convient pas il faut la mettre ainsi :

Do While i < Range(« R » & Rows.Count).End(xlUp).Row + 1

J’ai construit avec exactement la même structure un onglet à traiter reportant dans les mêmes colonnes que celles de ta copie d’écran les données que tu m’as fourni ce code ci-après adapté fonctionne parfaitement :

i = 2
Do While i < Range(« R » & Rows.Count).End(xlUp).Row + 1
colonne = 25
j = 2
Do While j < Range(« L » & Rows.Count).End(xlUp).Row + 1
If UBound(Split(Range(« L » & j), Range(« R » & i) & vbLf)) > 0 Or Right(Range(« L » & j), Len(Range(« R » & i))) = Range(« R » & i) Then
Cells(i, colonne) = Range(« M » & j)
colonne = colonne + 1
End If
j = j + 1
Loop
i = i + 1
Loop

Remplace tous les « Y » par des « R »

Et cela devrait le faire

Fais moi un retour

[mis_en_abime]

Encore merci FFO,

J’ai modifié le code comme indiqué => la machine mouline avec un coeur /6 à 100%, et excel ne répond plus. Je laisse faire en croisant les doigts

Je me demande si le fait qu’il y ait des cellules vides dans les REFERENCES (colonne S), puisse bloquer la macro?
Je vais essayer de tester avec un plus petit bout de base (100 lignes)
Bon appétit!

[FFO]

Combien de ligne à traiter colonne R et colonne L ?

[mis_en_abime]

6000 lignes, dont encore pas mal qui méritent nettoyage.
J’ai tronqué la base avec 42 lignes propres, et le phénomène est le même dans un premier temps => blocage… puis résultats corrects affichés! GENIAL

Par contre je me demande combien de temps ça va prendre sur 6000 lignes… je laisserai tourner toute une nuit et on verra bien.

Un grand merci FFO, je suis admiratif de cette aisance pour comprendre les problèmes (pourtant fort mal expliqué à la base!) et cette rapidité pour trouver la solution.

[FFO]

essaies en rajoutant cette ligne de code en début :

Application.Calculation = xlCalculationManual
et celle-ci en fin
Application.Calculation = xlCalculationAutomatic

ainsi d’interdire pendant le traitement le calcul des formules qui alourdi dans certain cas le fonctionnement Excel

Tu peux aussi rajouter cette ligne de code en début :

Application.ScreenUpdating = False
et celle-ci en fin
Application.ScreenUpdating = True

Et d’éviter pendant le traitement le rafraichissement de l’écran pour économiser de la ressource

Combiens de temps de traitement ?

Pour cerner la raison il faudrait que je puisse analyser sur un certain nombre de données peut être quelque chose qui en aveugle m’échappe

« J’ai tronqué la base avec 42 lignes propres, et le phénomène est le même dans un premier temps => blocage… puis résultats corrects affichés! GENIAL »

42 lignes sur colonne R ou L

et l’autre combiens ?

l’ampleur des 2 colonnes impacte le délais

[mis_en_abime]

Pour ajouter les lignes dans le code, peux-tu me dire où exactement? Comme ça?
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
i = 2
Do While i < Range(« R » & Rows.Count).End(xlUp).Row + 1
colonne = 25
j = 2
Do While j < Range(« L » & Rows.Count).End(xlUp).Row + 1
If UBound(Split(Range(« L » & j), Range(« R » & i) & vbLf)) > 0 Or Right(Range(« L » & j), Len(Range(« R » & i))) = Range(« R » & i) Then
Cells(i, colonne) = Range(« M » & j)
colonne = colonne + 1
End If
j = j + 1
Loop
i = i + 1
Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Nombre de lignes en l’état:
=> dans la base de référence, dans laquelle on cherche (colonnes L et M) = 40 lignes (à terme 6000)
=> dans la table sur laquelle on écrit (colonnes Y jusqu’à …ce qu’il n’y ai plus d’occurrences) => 277 lignes.

??
Pour le temps de traitement, environ 2 minutes pour ces 42 lignes.

Peut-être que ce qui te manque c’est qu’il y a plein de doublons. Par exemple, le résultat (exact) de la première ligne, c’est:
REF1 REF1 REF1 REF1 REF1 REF1 REF1 REF2 REF2 REF2 (car sur ces 40 lignes dans la base de test, ce document apparait effectivement 7 fois en face de REF1 et 3 fois en face de REF2)
Car en fait, il y a pas mal de données derrière tout cela (de la colonne A à K) avec d’autres références uniques et groupées entre elles sous d’autres REF#

EXEMPLE:
doc1 l REF1
doc2
docx
--------------
doc1 l REF1
doc2
docY
--------------
doc3 l REF2
doc4
--------------
doc3 l REF2
doc4
doc7
--------------
docY l REF3
doc7
--------------
etc…

Le mieux se serait que je te montre cette table bien évidemment (je ne peux pas te la transmettre). Si ça t’intéresse ou que tu veux faire une visio, ce sera avec plaisir.

[FFO]

Ok pour l’insertion des lignes de code
A voir si cela apporte du gain
40 lignes pour une colonne et 277 pour l’autre cela fait 40X277 soit 11080 analyses à effectuer
2 minutes est tout à fait honorable en terme de délais

De plus ce temps est très négligeable car pour 6000 lignes et 227 lignes : 1662000 analyses

soit après calcule si 11080 nécessite 2 minutes 1662000 en nécessitera 300 minutes soit 5 heures ce qui est nettement plus handicapant
voir si l’ajout des lignes de codes en début et en fin apporte une amélioration à ce niveau

Quel intérêt que de répéter les Ref n’est il pas possible avec un tri et regroupement de les réduire à l’unité ?
Ainsi de limiter le nombre de lignes

je te propose cette macro qui ne prendra en compte pour chaque document qu’une seule fois une même référence
Cela fera plus propre et peut être un gain dans le délais d’exécution

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
i = 2
Do While i < Range(« R » & Rows.Count).End(xlUp).Row + 1
références = « / »
colonne = 25
j = 2
Do While j < Range(« L » & Rows.Count).End(xlUp).Row + 1
If références Like « /" & Range(« M » & j) & "/ » = False Then
If UBound(Split(Range(« L » & j), Range(« R » & i) & vbLf)) > 0 Or Right(Range(« L » & j), Len(Range(« R » & i))) = Range(« R » & i) Then
Cells(i, colonne) = Range(« M » & j)
références = références & Range(« M » & j) & « / »
colonne = colonne + 1
End If
End If
j = j + 1
Loop
i = i + 1
Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Essais et dis moi

[mis_en_abime]

J’ai mis le code à jour (attention les étoiles disparaissent quand le code est collé dans cet éditeur) et il n’y a plus de doublons YES!

Par contre pour le délais ça ne change rien. En fait j’ai chronométré et c’est 4 minutes, mais ça n’est pas grave, je laisserai tourner une nuit ou plus, et ma machine a 16go de ram, elle devrait y arriver. Ce qui me chagrine, c’est qu’excel n’utilise qu’un coeur du processeur… j’imagine que ça irait plus vite s’il utilisait les 6 disponibles? (i7@2,2 ghz)

Ce qui pourrait aussi expliquer le délais, c’est qu’il y a à l’intérieur des cellules où l’on cherche (colonne L) parfois plus de 10 lignes de texte, et ces mêmes lignes de texte peuvent se répéter sur tout un tas de cellules dans la colonne.
Pour l’intérêt de répéter les Ref: En fait il n’y en a pas. Ce qui ce passe, c’est qu’il y a pas mal de données derrière tout cela (de la colonne A à K) avec 6000 références uniques et qui sont groupées entre elles sous nos REF#. => En gros les 6000 lignes uniques sont « couvertes » par ±400 REF#

En tous les cas, chapeau bas FFO. Et encore merci.
Si tu as d’autres suggestions, n’hésites surtout pas. Tu es d’une très grande aide.

[FFO]

Merci pour ce retour

" Ce qui pourrait aussi expliquer le délais, c’est qu’il y a à l’intérieur des cellules où l’on cherche (colonne L) parfois plus de 10 lignes de texte,"

Non tel que j ai conçu mon code que dans la cellule L qu il analyse il y ai du monde ou pas celui-ci en est insensible
Seule leur nombre conditionne le temps de traitement et pour le limiter il faudrait surtout enlever ces doublons de références qui se répètent en les regroupant
On pourrait étudier une procédure à cette fin mais le gain au final obtenu sera mise à mal par le délai que celui-ci nécessitera
Pas simple surtout que je n ai pas trop la vision réelle de l enjeu n ayant pas l intégralité des données concernées
Seule la diminution des lignes colonne L permettra de gagner à ce niveau

A toi de voir
Je reste à ta disposition si besoin

[mis_en_abime]

@FFO

Pas simple surtout que je n ai pas trop la vision réelle de l enjeu n ayant pas l intégralité des données concernées

C’est vrai, et la solution que tu apportes est d’autant plus appréciable!
Malheureusement, je suis contraint par les doublons de REF#.
J’ai cherché un peu => les macros VBA ne sont pas multithreadées… c’est comme ça. Tant pis! Ma machine moulinera toute la nuit.

[FFO]

Pauvre machine snif!!!
A cause de moi
Elle va me maudire
Surtout pour ne pas la traumatiser qu elle n ait que mon code à s occuper rien d autre
Et si le résultat est à la hauteur de sa performance nocturne félicite là de ma part elle l aura bien mérité
Viens au rapport pour m en dire plus