Isso parece um trabalho para o Power Query, um suplemento gratuito da Microsoft. Você pode combinar dados de várias pastas de trabalho do Excel, filtrar linhas desnecessárias e usar a lista resultante para relatórios.
Após a configuração, uma consulta pode ser atualizada e extrairá as instâncias mais recentes das fontes de dados especificadas.
Uma consulta pode ser definida com parâmetros variáveis, por exemplo, uma data inicial e final (se você quiser usar apenas dados em um determinado intervalo de tempo).
Várias funcionalidades do Power Query podem ser definidas com cliques e diálogos de fita. Para manipulações mais envolvidas, a linguagem M subjacente é uma ferramenta poderosa.
A web tem muitos recursos para o Power Query, que é incorporado ao Excel na versão 2016.