Manter colunas específicas de repetição no Excel

Question

Existe um relatório que eu pego do nosso sistema ERP que lista as informações detalhadas do pedido. Ele listará o número do pedido, código do cliente, nome do cliente, data do pedido, status do pedido, total do pedido, código do produto, nome do produto e quantidade ordenada, preço unitário e preço estendido. Se um pedido tiver várias linhas, as informações do cabeçalho serão listadas várias vezes.

Isto é o que os dados brutos parecem.

EstoutentandoencontrarumamaneiranoExceldeimpedirqueaslinhasdecabeçalhoserepitamparacadaumadaslinhasdedetalhedalinha.

Euprefeririaqueosdadosficassemassimquandoeuterminasse.Basicamente,cadaumadaslinhasserepetindosobsuasrespectivaslinhasdecabeçalho.

Outro formato aceitável pode estar abaixo. Acho que isso pode ser mais simples.

Eu consegui fazer isso com uma tabela Pivot.

Não tenho certeza se isso exigiria VBA ou não. Eu tentei destacar as linhas e usar remover duplicatas, mas isso move todas as linhas juntas. Qualquer ajuda seria muito apreciada.

    

Answer 1

Eu sei que isso é desaprovado, mas o problema parecia interessante, então eu apenas escrevi o VBA

Experimente o código abaixo. Eu defino as constantes para o exemplo que você deu, mas você pode querer alterá-las para o aplicativo real.

Function CompressReport()

    'Settings for which columns are the header and details
    Const fHC As Long = 1   'First header column number
    Const lHC As Long = 6   'Last header column number
    Const fDC As Long = 7   'First detail column number
    Const lDC As Long = 11  'Last detail column number

    'Declarations
    Dim rStart&, rStop&, rNew As Long
    Dim r&, c As Long
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim s1$, s2 As String

    'Set the source worksheet to be compressed
    '(Here are a few methods to do this. Pick one.)
    Set ws = Sheet1
    Set ws = Worksheets(1)
    Set ws = Worksheets("Sheet1")

    'Add a new worksheet for our results
    Set wsNew = Worksheets.Add(After:=ws)

    With ws
        'Copy the first row of headers
        .Range(.Cells(1, fHC), .Cells(1, lHC)).Copy wsNew.Cells(1, 1)
        rNew = 2

        'Loop through all the rows
        For rStart = 2 To ws.UsedRange.Rows.Count

            'Copy the header information
            .Range(.Cells(rStart, fHC), .Cells(rStart, lHC)).Copy wsNew.Cells(rNew, 1)

            'Add a thick border (This wasn't in the OP but I recommend it)
            With wsNew.Range(wsNew.Cells(rNew, 1), wsNew.Cells(rNew, lHC - fHC + 1)).Borders(xlEdgeTop)
                .LineStyle = xlContinuous   'You could also try xlDouble
                .Weight = xlThick
            End With

            'Collect the header information into a single unique ID
            s1 = ""
            For c = fHC To lHC
                s1 = s1 & "|" & .Cells(rStart, c).Value
            Next

            'Find the next row with different information
            For rStop = rStart + 1 To .UsedRange.Rows.Count
                s2 = ""
                For c = fHC To lHC
                    s2 = s2 & "|" & .Cells(rStop, c).Value
                Next
                If s2 <> s1 Then Exit For
            Next
            rStop = rStop - 1

            'Copy the detail headers and information
            .Range(.Cells(1, fDC), .Cells(1, lDC)).Copy wsNew.Cells(rNew + 1, 2)
            .Range(.Cells(rStart, fDC), .Cells(rStop, lDC)).Copy wsNew.Cells(rNew + 2, 2)

            'Increase the row we're pasting in the new worksheet
            ' +1 for header data, +1 for detail headers, +n for detail information
            rNew = rNew + 1 + 1 + (rStop - rStart + 1)

            'Increase the row we're copying in the source worksheet
            rStart = rStop  'The FOR loop will iterate it +1

        Next

    End With

    'Formatting (feel free to add to this part)
    With wsNew
        .Columns.AutoFit
    End With

    'Cleanup
    Set wsNew = Nothing
    Set ws = Nothing

End Function

    

Answer 2

Aqui está um pequeno truque para atingir seu objetivo. Pode ser aplicado a células em qualquer coluna. Digamos que começamos com:

EqueremosevitarvertodososextrasMike,etc.ClicamosnacélulaA2eaplicamosaformataçãocondicionalparaque,seovalordacélulaforomesmoqueacélulaacima,façaacordafonteigualàcordoplanodefundodacélula:

Em seguida, copiamos a célula A2 e o PasteSpecialFormats pela coluna. Isso "oculta" os valores repetidos:

Os dados atuais são deixados intactos, somente a exibição é alterada!

    

Answer 3

eu coloquei

naSheet1,eeuconseguifazercomqueSheet2ficasseassim:

Ele usa duas colunas auxiliares que, claro, você pode ir tão longe quanto quiser (ou precisar), e que você pode esconder.

• Defina A1 (na Folha2) como =Sheet1!A1 e arraste para a direita para cobrir as colunas que seriam duplicadas em várias linhas. No seu exemplo, isso seria Coluna F . (No meu exemplo, é Coluna C .)
• Defina Y2 a 2 e Z2 a 1 . O valor na Coluna Y indica o que a linha Sheet1 esta linha está obtendo dados. Coluna Z é 1 se esta for uma linha de cabeçalho (puxando dados das colunas da esquerda da Folha1; isto é, campos-chave), 2 se esta for uma linha de subtítulo, 3 se esta for uma linha de sub-dados (puxando dados das colunas da direita da Folha1), e 0 se esta for uma linha em branco (abaixo da última linha de dados).
• Defina A2 para =IF($Z2=1, INDEX(Sheet1!A:A, $Y2), "") . Se aplicável, arraste para a direita para cobrir as colunas usadas apenas para dados-chave. No seu exemplo, isso não é aplicável, porque você tem dados não chave começando na coluna B . (No meu exemplo, é através da coluna B .) Isso implementa as definições das colunas auxiliares: se Z for 1 , puxe os dados-chave da Planilha1, caso contrário, em branco.

• No meu exemplo, defino C2 para

  =CHOOSE($Z2+1, "", INDEX(Sheet1!C:C, $Y2), Sheet1!D$1, INDEX(Sheet1!D:D, $Y2))
  

  No seu exemplo, você deve definir B2 para

  =CHOOSE($Z2+1, "", INDEX(Sheet1!B:B, $Y2), Sheet1!G$1, INDEX(Sheet1!G:G, $Y2))
  

  refletindo as duas colunas Sheet1 que a Sheet2 Column B pode estar obtendo de:

  • Coluna B ("Cust Code") ou
  • Coluna G ("Código do produto")

  Novamente, isso faz o que as colunas auxiliares dizem para fazer. Adicionamos 1 ao valor Z para mapear 0 , 1 , 2 e 3 para 1 , 2 , 3 e 4 . CHOOSE usa o primeiro argumento para indexar nos argumentos a seguir, então

  • Se Z for 0 , em branco,
  • Se Z for 1 , obtenha os dados principais,
  • Se Z for 2 , obtenha o cabeçalho da Folha1 Linha 1 e
  • Se Z for 3 , obtenha os dados não principais.

• Defina Y3 para =IF($Z2<3, $Y2, $Y2+1) e Z3 para

  =IF($Z2=0, 0, IF($Z2<3, $Z2+1, IF(INDEX(Sheet1!A:A,$Y2+1)="", 0,
                      IF(INDEX(Sheet1!A:A,$Y2)=INDEX(Sheet1!A:A,$Y2+1), 3, 1))))
  

  (tudo em uma linha). Dizem que, se o valor Z na linha anterior for 1 ou 2 (ou 0 ), defina este valor Y para o mesmo valor da linha anterior. Isso ocorre porque cada linha na tabela do banco de dados (cada conjunto de valor exclusivo em Colunas A - F na Folha1) resulta em pelo menos três linhas na Planilha2. Caso contrário, incremente o valor Y , para endereçar a próxima linha na Planilha1.

  Se o valor Z anterior for 0 , pronto, preencha com zeros. Se o valor Z anterior for 1 ou 2 , aumente para o próximo valor. Caso contrário, observe os dados da chave Sheet1. Se estiver em branco, presuma que estamos no final dos dados e defina Z para 0 . Se for igual à linha anterior, use 3 para continuar o que estamos fazendo. Caso contrário, estamos em um novo conjunto de valores exclusivos, então reinicie o ciclo com um 1 .

• Arraste para baixo o suficiente para obter todos os seus dados.

Se seus valores exclusivos não forem individualmente únicos (por exemplo, se você tiver A4 = A5 mas B4 ≠ B5 ), expanda os testes na coluna Z para testar quantas colunas forem necessárias (combinando-as com AND(…) ).

Obviamente eu usei formatação condicional, com uma fórmula de =$Z2=2 , para formatar os subtítulos de forma adequada.