Como divido uma linha em várias linhas com o Excel?

Question

Eu tenho um banco de dados de produtos no Excel com várias centenas de entradas, cada uma com de 1 a 3 "camadas" de preços: Standard, Deluxe e Premium. Cada camada tem seu próprio SKU (A, B ou C adicionado ao final do SKU base) e o preço. Meus dados são assim:

Name, Description, Price A, Price B, Price C, SKU A, SKU B, SKU C
name1,      desc1,   14.95,   19.95,        , sku1A, sku1B, 
name2,      desc2,    4.95,    9.95,   12.95, sku2A, sku2B, sku2C
name3,      desc3,   49.95,        ,        , sku3A,      ,

Como eu faria para que os dados ficassem assim:

Name, Description,   SKU, Price
name1,      desc1, sku1A, 14.95
name1,      desc1, sku1B, 19.95
name2,      desc2, sku2A,  4.95
name2,      desc2, sku2B,  9.95
name2,      desc2, sku2C, 12.95
name3,      desc3, sku3A, 49.95

Se isso ajudar, importarei esses produtos para uma instalação do Magento.

Obrigado em avançado.

    

Answer 1

Essas tarefas geralmente são mais rápidas com o VBA. Na verdade, levei ~ 10 minutos para configurá-lo.
Estou assumindo que seus dados estão na coluna A para a coluna H.

Vá para Excel » Developer » Visual Basic »No painel esquerdo, abra sheet1 (ou) a planilha onde seus dados residem» Insira o código na janela da direita »Execute o código

código VBA

1 |Sub NewLayout()
2 |    For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
3 |        For j = 0 To 2
4 |        If Cells(i, 3 + j) <> vbNullString Then
5 |            intCount = intCount + 1
6 |            Cells(i, 1).Copy Destination:=Cells(intCount, 10)
7 |            Cells(i, 2).Copy Destination:=Cells(intCount, 11)
8 |            Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
9 |            Cells(i, 6 + j).Copy Destination:=Cells(intCount, 13)
10|        End If
11|        Next j
12|    Next i
13|End Sub

Explicação

Minha intenção era manter o código o mais curto possível para explicá-lo melhor. Basicamente, usamos dois loops. O loop externo ( i ) é para as linhas e o loop interno ( j ) para as colunas de preço.

Nós usamos muito o cells(rowNumber,columnNumber) para ler / gravar células.

• Linha 2 | Inicie um loop da linha 2 para a última linha. Nós iteramos através de cada linha usada

• Linha 3 | Inicie um segundo loop de 0 a 2 (que são na verdade 3 loops, um para cada coluna Price)

• Linha 4 | Usamos esse loop interno para verificar valores em nossa linha e coluna atuais Preço A, depois Preço B e no último loop Preço C. Se encontrarmos um valor em uma coluna Preço, continuaremos e copiaremos as células. Se nenhum preço for inserido, não faremos nada e passaremos para a próxima coluna Preço

• Linha 5 | Conte um contador para saber quantas linhas já copiamos,
  então sabemos depois de qual linha podemos copiar nossa linha atual

• Linha 6 | Copie a coluna de nome

• Linha 7 | Copie a coluna de descrição

• Linha 8 | Copie a coluna Preço A ou B ou C, dependendo do loop interno que atualmente estamos

• Linha 9 | Copie a coluna SKU A ou B ou C, dependendo do loop interno que estamos atualmente

Captura de tela do resultado

    

Answer 2

Aqui está uma solução de função de planilha. As fórmulas são um pouco densas, então esteja avisado, mas isso vai dar o que você quer.

Etapas:

1. Na primeira linha da sua nova tabela, em Name , insira uma referência direta ao primeiro Name em seus dados. No seu exemplo, você digitaria =A2 , em que A2 é o primeiro nome listado nos seus dados. Na captura de tela de exemplo que forneci abaixo, essa fórmula entra em A8 . Todas as fórmulas a seguir seguirão o layout usado na captura de tela. É claro que você terá que atualizar todas as referências de intervalo para corresponder à (s) sua (s) planilha (s).
2. Na célula abaixo, insira a seguinte fórmula:

   =IF(COUNTIF($A$9:A9,A9)=COUNTA(OFFSET($C$1:$E$1,MATCH(A9,$A$2:$A$5,0),0)),INDEX($A$2:$A$5,MATCH(A9,$A$2:$A$5,0)+1),A9)
   

   Isso basicamente verifica quantas linhas devem existir para o nome listado acima (em A9 ), e se o número de linhas já em sua nova tabela corresponder a isso, então passa para o próximo nome. Caso contrário, outra linha para o nome acima será adicionada.
   Preencha esta fórmula até onde você precisa (até que retorne um 0 em vez de um nome).
3. Na primeira linha em Description , insira a seguinte fórmula e preencha.

   =INDEX($B$2:$B$5,MATCH(A9,$A$2:$A$5,0))

4. Na primeira linha em SKU , cole a seguinte fórmula na barra de fórmulas e pressione Ctrl + Deslocar + Inserir .% pré% Esta é uma fórmula de matriz; Se inserida corretamente, a fórmula aparecerá na barra de fórmulas entre chaves. Preencha esta fórmula na sua tabela (cada instância também deve aparecer entre chaves).
5. Da mesma forma, na primeira linha em Price , cole a seguinte fórmula na barra de fórmulas e insira-a como uma fórmula de matriz (pressionando Ctrl + Deslocamento + Digite ).

   =INDEX(OFFSET($A$1:$H$1,MATCH(A9,$A$2:$A$5,0),0),SMALL(IF(OFFSET($F$1:$H$1,MATCH(A9,$A$2:$A$5,0),0)<>"",COLUMN($F$1:$H$1)),COUNTIF($A$9:$A9,$A9)))

   Preencha, e isso deve completar sua tabela.