Microsoft 365 Rechercher des blocs de valeurs dans multiples feuilles du même classeur et les empiler

[M92_]

Bonjour le fil,

J'ai un problème que je n'arrive pas à résoudre. Ci-dessous, mes inputs/ouput :

# Input :
Il s'agit d'un nombre aléatoire de blocs qui sont éparpillés aléatoirement dans plusieurs feuilles.
Le seul point commun entre les différents blocs c'est le format : noms des réf (A, B, ..) et la taille.

# Output (souhaité) :

Je me demande s'il est possible d'effectuer cette transfo en Excel (VBA et/ou PowerQuery).
Si oui, pourriez-vous me donner un coup de main, s'il vous plaît ? En PJ, un exemple de fichier test.

Merci beaucoup,

Cdlt,
M92

 

[vgendron]

Hello

Juste une piste
pour trouver les blocs: il suffirait de faire une recherche (Find next) sur la Ref A
et à chaque occurence, en déduire le tableau (currentregion), le nommer avec un nom type "Tab_xx"
une fois que tous les tableaux sont connus==> les compiler..
je ne connais que très peu PQ. je ne vais donc pas pouvoir t'aider plus

 

[sylvanu]

Bonjour M92, Vgendron,
Ne connaissant pas la méthode de Vgendron pour délimiter les zones à analyser, j'ai fait plus "bourrin" avec :

Sub Worksheet_Activate()
    Dim LigneRefC%, LigneRefD%, DL%, i%, j%, Nmax%
    Nmax = 100  ' Nombre max de lignes et colonnes à analyser pour trouver la dernière
    LigneRefC = 2: LigneRefD = 2
    [A2:B1000].ClearContents: [A1:B1000].Borders.LineStyle = xlNone
    For Each F In Worksheets
        If F.Name <> "Synthèse" Then
            For L = 1 To Nmax    ' Dernière ligne
                DL = Application.Max(DL, Sheets(F.Name).Cells(65000, L).End(xlUp).Row)
            Next L
            For C = 1 To Nmax    ' Dernière colonne
                DC = Application.Max(DC, Sheets(F.Name).Cells(C, Columns.Count).End(xlToLeft).Column)
            Next C
            With Sheets(F.Name)
                T = .Range(.Cells(1, 1), .Cells(DL, DC))
            End With
            For i = 1 To UBound(T)
                For j = 1 To UBound(T, 2)
                    If T(i, j) = "REF C" Then
                        Cells(LigneRefC, "A") = T(i, j + 2)
                        LigneRefC = LigneRefC + 1
                    End If
                    If T(i, j) = "REF D" Then
                        Cells(LigneRefD, "B") = T(i, j + 2)
                        LigneRefD = LigneRefD + 1
                    End If
                Next j
            Next i
        End If
    Next F
    DL = Application.Max(Cells(65000, "A").End(xlUp).Row, Cells(65000, "B").End(xlUp).Row)
    Range("A1:B" & DL).Borders.Weight = xlThin
End Sub

J'ai limité la recherche des lignes et colonnes à 100, sinon modifier Nmax.
La macro est automatique lorsqu'on sélectionne la feuille Synthèse.
Mais par pitié, la prochaine fois, éviter les cellules fusionnées.

 

[vgendron]

Hello @sylvanu

je pense que ma méthode n'est pas moins bourrin que la tienne

Le code suivant suppose que les tableaux sont TOUJOURS sous la meme forme
avec REF A en première ligne
que les cellules sont fusionnées sur 2 puis 4 colonnes pour ne former qu'un tableau de 2 colonnes.. (Grrr.. la fusion...)
le code commence par transformer les tableaux en PLAGES Nommées
ensuite, les plages nommées deviennent des tableaux structurés.
pourquoi le passage par plage nommée?? parce que la création d'un tableau structuré ajoute une ligne d'entete.. qu'on la masque ou pas. du coup. les données sont décalées d'une ligne. et donc.. le firstadress.. bah.. il ne sert à rien. et la boucle tourne et tourne et tourne... j'en ai le mal de mer

Sub GenererTab()
Dim c As Range
Dim firstAddress As String

Numtab = 1
For Each ws In ActiveWorkbook.Sheets
With ws
    
    With .Cells
        Set c = .Find("REF A", LookIn:=xlValues)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                Set NewTab = c.CurrentRegion
    
                ActiveWorkbook.Names.Add Name:="Tab_" & Numtab, RefersTo:=NewTab
                Numtab = Numtab + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End With
Next ws

For i = 1 To Numtab - 1
    Application.Goto Reference:="Tab_" & i
    Set NewTab = Range("Tab_" & i)
    ActiveSheet.ListObjects.Add(xlSrcRange, NewTab, , xlNo).Name = "Tab_" & i
    ActiveSheet.ListObjects("Tab_" & i).TableStyle = "TableStyleMedium2"
    ActiveSheet.ListObjects("Tab_" & i).ShowHeaders = False
Next i
End Sub

 

[sylvanu]

je pense que ma méthode n'est pas moins bourrin que la tienne

C'est bien ce que j'ai dit !

j'ai fait plus "bourrin"

 

[vgendron]

non non !! PAS moins bourrin. donc.. aussi bourrin que toi..
bref.. on bourrine tous les deux
on va voir que dit @M92_ Le Temps (bourrin) nous dira .. Tadaaaaa !!

 

[M92_]

@sylvanu, @vgendron,

Honnêtement, je ne sais comment vous remercier.
Vous m'avez fait gagné des heures de prod/travail manuel.

Juste une petite question: Admettons que REF C et REF D ne sont pas voisines, par exemple :

Est-ce que vos codes prendraient en considération cette configuration et seraient capables de gérer ce scénario ?

 

[vgendron]

essaie..tu verras

 

[vgendron]

autre contrainte avec mon code.. il faut que tous les tableaux soient séparés d'au moins une ligne et une colonne..==> pas de voisinage proche avec un autre tableau
en cause: c.currentregion==> le currentregion est un équivalent d'un Ctrl+A sur une cellule d'une zone de données

 

[sylvanu]

Une version où on peut modifier ce qu'on recherche en Synthèse A1 et B1. La mise à jour est automatique.

 

[M92_]

autre contrainte avec mon code.. il faut que tous les tableaux soient séparés d'au moins une ligne et une colonne..==> pas de voisinage proche avec un autre tableau
en cause: c.currentregion==> le currentregion est un équivalent d'un Ctrl+A sur une cellule d'une zone de données

Le fichier que je reçois par le client respecte la contrainte que tu décris. Les blocs sont séparés horizontalement par deux colonnes et verticalement par au moins quatre lignes.

 

[M92_]

Une version où on peut modifier ce qu'on recherche en Synthèse A1 et B1. La mise à jour est automatique.

Là c'est vraiment du Excel avancé. Alors, j'ai tenté de jouer avec l'emplacement des références dans chaque bloc, non seulement la mise à jour était instantanée mais les valeurs récupérées sont correctes aussi.

Merci beaucoup @sylvanu.

 

[sylvanu]

mais les valeurs récupérées sont correctes aussi.

Evidemment. J'avais testé.
D'ailleurs on pourrait mettre plus de références en recherche, c'est guère plus complexe.

 

[M92_]

Evidemment. J'avais testé.
D'ailleurs on pourrait mettre plus de références en recherche, c'est guère plus complexe.

Désolé j'étais pris par l'émotion. J'arrivai pas à croire que enfin j'aurai plus à faire de la mano (sachant qu'on parle parfois d'une centaines de blocs).

 

[vgendron]

Juste parce que je suis content d'avoir fait un power query, je poste
1) bouton pour lancer la génération des tableaux structurés
2) clic droit sur le tableau de la feuille "Résultat" / actualiser