Copiar referência de célula com incremento de várias linhas

Question

Eu exportei os endereços de entrega do Pastel para uma planilha do Excel. Cada registro de endereço que o Pastel cuspiu tem muitos dados para cada cliente que eu não preciso.

Dada a captura de tela aqui, só preciso dos valores em B2, B3, D4 e D5 para cada conta. Eu posso ver que há 7 linhas dividindo esses valores para cada conta. O que eu preciso fazer é agrupar esses valores para captura no formato CSV, mas não posso descobrir como fazê-lo.

Na Planilha2, eu tenho tentado algo assim e copiado, mas está incrementando em 1 linha, eu preciso disso para incrementar em 7, alguém pode me ajudar a descobrir como fazer isso?

=Sheet1!B2

    

Answer 1

Com base no layout da sua planilha, as seguintes fórmulas selecionam as células que você está procurando.

Cell F1: =INDEX($B:$D,ROW($F1)*7-5,1)
Cell G2: =INDEX($B:$D,ROW($F1)*7-4,1)
Cell H2: =INDEX($B:$D,ROW($F1)*7-3,3)
Cell I2: =INDEX($B:$D,ROW($F1)*7-2,3)

Depois de inserir o primeiro conjunto, basta copiá-los pelo número de linhas necessárias para capturar todos os dados (que deve ser o número total de linhas de dados dividido por 7).

A função INDEX tem a seguinte sintaxe: INDEX(lookup_range, row number, column number) . Por exemplo, INDEX($B:$D,1,2) retornaria o conteúdo da célula na primeira linha e na segunda coluna em $B:$D , que é a célula C1 .

Quando as fórmulas são copiadas, ROW($F1) retorna os números 1,2,3 ..., que são multiplicados por 7 para fazer o salto que você precisa. Esse produto precisa ser ajustado para obter a linha correta nos grupos de dados de 7 linhas. Daí as -5, -4, etc. nas fórmulas. O terceiro argumento apenas seleciona a coluna correta para retornar: 1 = > coluna B e 3 = > coluna D.

    

Answer 2

Para o primeiro, use as células D2: G2 para capturar as informações desejadas. Em seguida, ative o filtro (Classificar e Filtrar - > Filtro) e filtre somente as células que têm 'Conta' na primeira coluna. Em seguida, preencha D2: G2 na parte inferior. Você pode copiar e colar as células preenchidas em um intervalo contíguo em uma nova pasta de trabalho / planilha.