Transforme uma linha em várias linhas no Excel

Question

Tenho vários itens disponíveis em vários centros de distribuição (ou seja, um relacionamento muitos para muitos). Atualmente, há uma linha por item, com uma coluna para cada centro de distribuição. Uma célula na linha do item X e a coluna do centro de distribuição Y é marcada com o código do centro de distribuição Y se o item X está disponível lá e em branco. Um item com vários centros de distribuição terá vários códigos do centro de distribuição (em suas respectivas colunas). Então, a folha atual se parece com:

    |   A    |         B         |*|                      S-AJ                      |
1   |  ID #  |   Description     |…|              Distribution Centers              |
2   |   17   |   Ginkgo Biloba   |…|      |      |      |      |      |      |  SE  |
3   |   42   |   Ginseng         |…|      |  MP  |  MS  |      |  NW  |      |      |
                     ︙

As colunas C a R contêm outros atributos dos itens, como código UPC, custo e preço, que não são relevantes para essa pergunta. Minha planilha atual tem 18 centros de distribuição, abrangendo as colunas S a AJ ; Reduzi isso para que o exemplo se encaixe na janela do Stack Exchange.

Eu preciso ter uma única coluna do centro de distribuição, com um único código de distribuição por linha, e duplicar as linhas conforme necessário para os itens que atualmente contêm vários códigos. O resultado deve se parecer com:

    |   A    |         B         |*|   S  |
1   |  ID #  |   Description     |…|  DC  |
2   |   17   |   Ginkgo Biloba   |…|  SE  |
3   |   42   |   Ginseng         |…|  MP  |
4   |   42   |   Ginseng         |…|  MS  |
5   |   42   |   Ginseng         |…|  NW  |
                     ︙

em que as células A3:R3 , A4:R4 e A5:R5 contêm as mesmas informações.

A única maneira que posso pensar em fazer isso, o que seria demorado, seria copiar o número do item em várias linhas; e na coluna que tem o código de distribuição, eu mudaria o código para o item que está disponível em cada centro de distribuição. Eu vou estar fazendo isso por 900 itens. Existe uma maneira mais fácil de fazer isso?

    

Answer 1

1. Crie uma nova planilha. Copie a (s) linha (s) de cabeçalho, as larguras das colunas e os formatos, exceto não copie as colunas T - AJ . Pode ser mais fácil copiar a folha inteira, em seguida, exclua todas as linhas que não sejam 1 e desative as colunas S - AJ .

2. Primeiro, queremos replicar cada linha de item de Sheet1 para Sheet2 18 vezes - uma vez para cada centro de distribuição. Digite =INDEX(Sheet1!A:A, INT((ROW()-2)/18)+2, 1) & "" em Sheet2!A2 . INT((ROW()-2)/18)+2 mapeia as linhas 2-19 em Sheet2 para a linha 2 em Sheet1 , linhas 20-37 em Sheet2 para linha 3 em Sheet1 , etc. O & "" faz com que o Excel exiba um espaço em branco ao referenciar uma célula em branco em Sheet1 . Se você não tem espaços em branco em Sheet1 , pode deixar isso de lado. Se você não gosta desta solução em particular, você pode usar uma das outras soluções de Exibir em branco ao referenciar célula em branco no Excel .

   Arraste / preencha isto para a direita, para a célula R2 .

3. Insira =INDEX(Sheet1!$S:$AJ, INT((ROW()-2)/18)+2, MOD(ROW()-2, 18)+1) em Sheet2!S2 . Isso faz referência à mesma linha em Sheet1 da fórmula acima, mas Sheet2!S2 obtém o valor de Sheet1!S2 , Sheet2!S3 obtém o valor de Sheet1!T2 , Sheet2!S4 obtém o valor de Sheet1!U2 etc. Isso exibirá 0 s para espaços em branco.
4. Selecione a linha inteira A2:S2 e arraste / preencha para obter todos os seus dados. Isso precisará ter 18 vezes mais linhas do que você tem em Sheet1 ; isto é, 18 × 900 = 16200.
5. Copie todos os Sheet2 e cole os valores.
6. Filtrar coluna S . Exibe apenas os zeros. Exclua todas as linhas (diferente da linha 1). Remova o filtro.

Feito.