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

M92_

XLDnaute Junior
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.

1665582405887.png


# Output (souhaité) :

1665582367651.png


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
 

Pièces jointes

  • ConcatBlocs_test_.xlsx
    12.5 KB · Affichages: 7

vgendron

XLDnaute Barbatruc
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

XLDnaute Barbatruc
Supporter XLD
Bonjour M92, Vgendron,
Ne connaissant pas la méthode de Vgendron pour délimiter les zones à analyser, j'ai fait plus "bourrin" avec :
VB:
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. :eek:
 

Pièces jointes

  • ConcatBlocs_test_.xlsm
    32.6 KB · Affichages: 2

vgendron

XLDnaute Barbatruc
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:)

VB:
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
 

M92_

XLDnaute Junior
@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 :

1665588087225.png

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

vgendron

XLDnaute Barbatruc
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
 

M92_

XLDnaute Junior
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.
 

vgendron

XLDnaute Barbatruc
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
 

Pièces jointes

  • ConcatBlocs_test.xlsm
    34.2 KB · Affichages: 6

Statistiques des forums

Discussions
315 095
Messages
2 116 166
Membres
112 675
dernier inscrit
Tazra_IMOU