Besoin de correspondances multiples. INDEX/EQUIV trop limité

[logx]

Bonjour à tous,

Après avoir "googlisé" un bon moment et n'avoir que que des solutions incomplètes ou inadaptées à mon cas; et que je ne comprenais qu'à moitié, je me résous à créer mon propre post.

LE CAS : Nous allons épurer l'arborescence de fichiers d'un intranet d'entreprise. La structure fichier de cet intranet à évolué au fil des années (plus de 10 ans) sans jamais être nettoyée et aujourd'hui, c'est un enchevêtrement de plus de 15000 répertoires et sous-répertoires (!) dont une grosse et majeure partie n'est plus utilisée.

LA MISSION : Mon job est de repérer un maximum de tous ces répertoires morts.

LA MÉTHODE : Dans un premier temps, j'ai reconstitué dans une feuille Excel toute l'arborescence complète (merci à la puissance de cmd et à son exporation de résultat vers un fichier!) dans une colonne unique "arborescence". Ensuite, j'ai reconstitué la structure de navigation de cet intranet bilingue dans 2 autres feuilles du même classeur (une feuille pour chaque langue sinon ça devenait illible) et avec, pour chaque lien hypertexte, sa cible dans la colonne voisine.

MON SOUCI : Vous commencez peut-être à l'entrevoir: je voudrais que, dans la feuille "arborescence" et pour chaque répertoire concrètement utilisé, soit affiché dans une colonne voisine le OU LES élément(s) "appelants". En jargon Excel, je voudrais donc afficher dans cette nouvelle colonne, pour chaque ligne dont la chaine de caractère de la colonne "arborescence" est égale à la chaine de caractère de la colonne "cible" dans l'une des deux autres feuilles, la valeur de la colonne "lien" qui est en regard de la valeur dans la colonne "cible". Vous me suivez ?

J'ai cru après mes recherches que le couple INDEX/EQUIV pouvait être une solution. Mais je suis assez mal à l'aise avec ses fonctions que je ne connais pas bien. Et, de plus, un problème de taille subsiste: EQUIV s'arrête à UNE correspondance. Or il est tout à fait possible qu'un même répertoire soit appelé depuis plusieurs liens ! Il faudrait que chacun de ces liens puissent être repris, dans la même colonne avec un séparateur ou dans des colonnes consécutives. Je préfère dans la même colonne mais là où j'en suis, c'est presque un détail...

Une idée de formule "magique" ?? Une macro peut-être (je ne pige rien aux macros mais suis de bonne volonté) ?? Je me demande même si je ne ferais pas mieux de tout exporter vers Access (que je ne gère pas tip top non plus :-/ ). Quitte à réexporter le résultat vers Excel après (le tableau résultant ne doit pas nécessairement être dynamique; je cherche surtout à gagner du temps avec des automatismes et ne pas faire des centaines voire des milliers de liens manuellement) Merci d'avance pour votre aide et vos conseils précieux !

 

[Grand Chaman Excel]

Re : Besoin de correspondances multiples. INDEX/EQUIV trop limité

Bonjour logx et bienvenue sur le forum,

Par formules, je ne saurais pas trop comment faire, mais par macro, tout est faisable... ou presque!
Est-ce possible d'envoyer un bout de ton fichier avec quelques lignes et avec un exemple de résultat voulu...

 

[logx]

Re : Besoin de correspondances multiples. INDEX/EQUIV trop limité

Merci pour ta réponse Le classeur original est assez complexe et potentiellement confidentiel

Mais j'ai monté un exemple fictif beaucoup plus simple qui reprend la même idée (voir attachement)

Les deux premières feuilles de calcul reprennent toutes les deux la structure de menu du site (pour l'exemple, le site d'un collectif artistique de musiciens et de photographes), chacune dans une des deux langues du site. Même structure donc, mais pas les mêmes pages ciblés...

Le troisième onglet reprend en colonne A l'arborescence sur le serveur. La colonne B reprend en regard de chaque répertoire le ou les lien(s) qui cible(nt) ce répertoire. Certains répertoires ne sont ciblés par aucun lien. Dans mon job réel, c'est précisément ces répertoires "morts" qui m'intéressent puisqu'il s'agit d'épurer cette énorme arborescence surparasitée par des milliers de répertoires devenus inutiles.

Je voudrais donc trouver une solution qui permettent automatiquement de retrouver dans les deux autres feuilles les liens concernés et les afficher dans cette colonne B en regard du répertoire concerné.

Ca existe ça ?

 

[Grand Chaman Excel]

Re : Besoin de correspondances multiples. INDEX/EQUIV trop limité

Bonjour logx,

Voici une macro qui devrait fonctionner. Je n'ai pas testé avec 15000 lignes, le processus pourraît être long. Un compteur s'affiche dans le bas de la fenêtre pour indiquer la progression.

Sub MenageRepertoires()

'   Pour utiliser la macro, il faut activer la référence à l'objet Microsoft Scripting Dictionary
'   Dans Outils > Références
'   Activer : Microsoft Scripting Runtime

    Dim wb As Workbook
    Dim wsF As Worksheet, wsE As Worksheet, wsA As Worksheet
    Dim rg As Range, c As Range
    Dim rgF As Range, rgE As Range, rgT As Range
    Dim rFirstAddress As String
    
    Application.ScreenUpdating = False
    
    Set wb = ThisWorkbook
    
'   Noms des feuilles en variables, à adapter au besoin
    Set wsF = wb.Sheets("Structure Navig FR")
    Set wsE = wb.Sheets("Structure Navig EN")
    Set wsA = wb.Sheets("Arborescence serveur")
    
'   On crée 3 plages de recherche avec le noms des répertoires
    Set rg = wsA.Range("A2:A" & wsA.Range("A65536").End(xlUp).Row)
    Set rgF = wsF.Range("B2:B" & wsF.Range("A65536").End(xlUp).Row)
    Set rgE = wsE.Range("B2:B" & wsE.Range("A65536").End(xlUp).Row)
    
    For Each c In rg
    
'   Compteur à tous les 10 : commenter cette ligne pour accélérer le processus
    If (c.Row - 1) Mod 10 = 0 Then Application.StatusBar = "Recherche de l'élément : " & c.Row - 1 & "/" & rg.Rows.Count
'       Recherche dans FR
        Set rgT = rgF.Find(c, , , xlWhole)
        If Not rgT Is Nothing Then
            rFirstAddress = rgT.Address
            Do
                Set rgT = rgF.FindNext(rgT)
                If Len(c.Offset(0, 1)) = 0 Then
                    c.Offset(0, 1) = rgT.Offset(0, -1)
                Else
                    c.Offset(0, 1) = c.Offset(0, 1) & Chr(10) & rgT.Offset(0, -1)
                End If
            Loop Until rgT.Address = rFirstAddress
        End If
        
        rFirstAddress = ""
        
'      Recherche dans EN
        Set rgT = rgE.Find(c, , , xlWhole)
        If Not rgT Is Nothing Then
            rFirstAddress = rgT.Address
            Do
                Set rgT = rgE.FindNext(rgT)
                If Len(c.Offset(0, 1)) = 0 Then
                    c.Offset(0, 1) = rgT.Offset(0, -1)
                Else
                    c.Offset(0, 1) = c.Offset(0, 1) & Chr(10) & rgT.Offset(0, -1)
                End If
            Loop Until rgT.Address = rFirstAddress
        End If
    Next c

    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub

A+