Como combinar valores de várias linhas em uma única linha? Tem um módulo, mas precisa das variáveis explicando

Question

Sou completamente novo no VBA e estou tentando implementar a excelente solução encontrada aqui por Raystafarian (a versão revisitada). No entanto, como eu realmente não entendo a solução, estou com dificuldades para executá-la onde os números das colunas, etc., são diferentes. Por favor, alguém poderia me explicar a que as variáveis se referem para que eu possa aplicá-lo a um exemplo diferente? Meu problema é exatamente o mesmo - várias linhas para um único ponto de dados e procurando obter uma tabela com nome / data1 / data2 / data3 na mesma linha para a análise de dados do Spotfire.

    

Answer 1

É uma macro bem difícil, mas

Option Explicit

Sub CombineRowsRevisited()

'c is a CELL or a range
Dim c As Range
'i is a number
Dim i As Integer

'for each CELL in this range
For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
'if the CELL is the same as the cell to the right AND
'if the cell 4 to the right is the same as the cell below that one
If c = c.Offset(1) And c.Offset(, 4) = c.Offset(1, 4) Then
            'then make the cell 3 to the right the same as the cell below it
            c.Offset(, 3) = c.Offset(1, 3)
            'and delete the row below the CELL
            c.Offset(1).EntireRow.Delete
End If

Next

End Sub

Isso seria mais fácil de entender, dado o acima

Sub CombineRowsRevisitedAgain()
    Dim myCell As Range
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For Each myCell In Range(Cells("A2"), Cells(lastRow, 1))
        If (myCell = myCell.Offset(1)) And (myCell.Offset(0, 4) = myCell.Offset(1, 4)) Then
            myCell.Offset(0, 3) = myCell.Offset(1, 3)
            myCell.Offset(1).EntireRow.Delete
        End If
    Next
End Sub

No entanto, dependendo do problema, talvez seja melhor usar step -1 em um número de linha para que nada seja ignorado.

Sub CombineRowsRevisitedStep()
    Dim currentRow As Long
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For currentRow = lastRow To 2 Step -1
        If Cells(currentRow, 1) = Cells(currentRow - 1, 1) And _
        Cells(currentRow, 4) = Cells(currentRow - 1, 4) Then
            Cells(currentRow - 1, 3) = Cells(currentRow, 3)
            Rows(currentRow).EntireRow.Delete
        End If
    Next

End Sub

    

Answer 2

Estas são as explicações para as linhas mais importantes do script:

For each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))

Itere toda a coluna A de A2 para a última célula usada, atribuindo o valor à variável c .

Como c é uma célula, ela tem um valor de linha e coluna, então age como um ponto de referência de onde podemos nos mover usando Offset .

If c = c.Offset(1) And c.Offset(,4) = c.Offset(1,4) Then

Compare a célula c com a célula na próxima linha c.Offset(1) AND a célula na mesma linha, mas na coluna 4 c.Offset(,4) com a célula na linha seguinte da coluna 4 c.Offset(1,4) .

c.Offset(,3) = c.Offset(1,3)

Atribua à célula na linha e coluna 3 c.Offset(,3) o valor da célula na próxima linha e coluna 3 c.Offset(1,3) .

c.Offset(1).EntireRow.Delete

Exclui a próxima linha

    

Answer 3

Sugiro uma maneira diferente de o VBA lidar com essa etapa de transformação.

Você pode experimentar o Microsoft Add-In gratuito Consulta de energia (do Excel 2010). É bastante intuitivo para carregar e transformar seus dados. Basicamente, você importa os dados no Power Query, desativa e repete esses dados.

Primeiro, defina seus dados como uma tabela no Excel.

| Customer   | Value A | Value B | Year |
|------------|---------|---------|------|
| Customer 1 | 134     |         | 2009 |
| Customer 1 |         | 3       | 2009 |
| Customer 1 | 175     |         | 2010 |
| Customer 1 |         | 5       | 2010 |
| Customer 1 | 7784    |         | 2011 |
| Customer 2 | 515     |         | 2009 |
| Customer 2 | 1943    |         | 2010 |
| Customer 2 |         | 1       | 2010 |
| Customer 2 | 9745    |         | 2011 |
| Customer 2 |         | 154     | 2011 |

Coloque o cursor em algum lugar da tabela

Vá para a guia Consulta de energia e clique em "Da tabela". Ele abrirá o Power Query Editor.

Selecione as colunas Value A / B (com Ctrl). Clique com o botão direito do mouse em um dos cabeçalhos de coluna que você selecionou para exibir um menu de opções. Clique no menu Unpivot Columns.

Próximo passo: Selecione a coluna Atributo (Valor A / B) e Gire a tabela. Escolha a coluna Valor para Dinâmica.

A saída será assim:

| Customer   | Year | Value A | Value B |
|------------|------|---------|---------|
| Customer 1 | 2009 | 134     | 3       |
| Customer 1 | 2010 | 175     | 5       |
| Customer 1 | 2011 | 7784    |         |
| Customer 2 | 2009 | 515     |         |
| Customer 2 | 2010 | 1943    | 1       |
| Customer 2 | 2011 | 9745    | 154     |

O script do Power Query das três etapas é

let
    Source= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Customer", "Year"}, "Attribut", "Value"),
    #"Pivot Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribut]), "Attribut", "Value", List.Sum)
in
    #"Pivot Column"

Você também pode encontrar um tutorial passo-a-passo sobre como cancelar a publicação aqui