Criei uma coluna auxiliar J repetindo o texto de C1 na primeira linha e com esta fórmula em J2, copiada para baixo:
Esta fórmula procura a palavra "rua" na coluna C e quando encontra, copia o texto de C para J. Quando não encontra, as linhas com Nr de casas, repete o texto da linha anterior. Isto serve para indexar as casas conforme a rua. Mas só vai funcionar se encontrar a palavra "rua", conforme o seu modelo.
No intervalo L1:O1, listei manualmente os títulos CASA DA RUA X, CASA DA RUA Y, etc. Poderia fazer com fórmula, porém matricial, e ficaria ainda mais lenta a planilha. Daí as vantagens do Excel 365.
Para cada CASA DA RUA listo as casas Nr com esta fórmula matricial em L2, copiada até Z2 e para baixo copiei até a linha 100:
Código: Selecionar todos=SEERRO(ÍNDICE($C$2:$C$1000;CORRESP(1;(CONT.SE(L$1:L1;$C$2:$C$1000)=0)*($C$2:$C$1000<>"")*($J$2:$J$1000=L$1);0));" ")
Em G1 uso Validação de dados para selecionar CASA DA RUA usando esta fórmula, que lista as casas rua listadas no intervalos L1:Z1:
Em G2 uso Validação de dados para selecionar Casa Nr usando esta fórmula, que lista as casas Nr listadas no intervalos L2:Z100, conforme a casa rua selecionada em G1:
Código: Selecionar todos=DESLOC($K$2;;CORRESP($G$1;$L$1:$Z$1;0);CONT.SE(ÍNDICE($L$2:$Z$100;0;CORRESP($G$1;$L$1:$Z$1;0));"<> ");1)
Em E2, copiado para F2 e para baixo, uso esta outra fórmula matricial para listar códigos e pessoas:
Código: Selecionar todos=SEERRO(ÍNDICE(A$2:A$1000;MENOR(SE($J$2:$J$1000=$G$1;SE($C$2:$C$1000=$G$2;LIN($A$2:$A$1000)-LIN($A$2)+1));LINS($A1:$A$1)));" ")
Atente para as fórmulas matriciais acima. Depois de editar, confirme com Ctrl+Shift+Enter em vez de Enter apenas.
Confira no anexo se atende.
Ab.