PowerQuery est un outil d'analyse de données intégré à Excel, qui permet de regrouper différents tableaux/tables en 1 seul pour ensuite y effectuer toutes sortes d'analyses. A condition, comme le mentionnait JFL, que vos données soient consistantes.
Votre demande fait directement penser à l'usage d'un TCD, cependant puisque vos données sont regroupées par "BOM" = feuilles différentes, il semble nécessaire d'utiliser power query pour les regrouper / synthétiser. Malheureusement j'ai encore du mal à comprendre votre demande, je pense que les entetes ne sont pas très explicites.
Ci-après une autre proposition.