Evite que colunas específicas se repitam no Excel

Question

Há um relatório que extraí de nosso sistema ERP que lista 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 solicitada, preço unitário e preço estendido. Se um pedido tiver diversas linhas, as informações do cabeçalho serão listadas diversas vezes.

Esta é a aparência dos dados brutos.

Estou tentando encontrar uma maneira no Excel de evitar que as linhas de cabeçalho se repitam para cada uma das linhas de detalhe da linha.

Eu preferiria que os dados ficassem assim quando terminar. Basicamente, cada uma das linhas se repete sob suas respectivas linhas de cabeçalho.

Outro formato aceitável pode estar abaixo. Acho que isso pode ser mais simples. Consegui fazer isso com uma tabela dinâmica.

Não tenho certeza se isso exigiria VBA ou não. Tentei destacar as linhas e 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 acabei de escrever o VBA

---

Experimente o código abaixo. Eu configurei as constantes para funcionarem no exemplo que você deu, mas você pode querer alterá-las para seu 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:

E queremos evitar ver todos os extrasMike's, etc.Clicamos na célulaA2e aplique a Formatação Condicional para que, se o valor da célula for igual ao da célula acima dela, torne a cor da fonte igual à cor de fundo da célula:

Em seguida, copiamos a célulaA2e PasteSpecialFormats na coluna. Isso "oculta" os valores repetidos:

Os dados reais permanecem intactos, apenas a exibição é alterada!

Answer 3

Eu coloco

                       

na Planilha1 e consegui fazer com que a Plan2 ficasse assim:

Ele usa duas colunas auxiliares, que, é claro, você pode mover para a direita tanto quanto quiser (ou precisar), e que você pode ocultar.

• Defina A1(na Planilha2) como =Sheet1!A1e arraste para a direita para cobrir as colunas que, de outra forma, seriam duplicadas em várias linhas. No seu exemplo, seria Column  F. (No meu exemplo, é Column  C.)
• Defina Y2como 2e Z2como 1. O valor na Coluna  Ydiz qual linha da Planilha1esserow está extraindo dados. Coluna  Zé 1se for uma linha de cabeçalho (extrair dados das colunas da esquerda da Planilha1; ou seja, campos-chave), 2se for uma linha de subtítulo, 3se for uma linha de subdados (extrair dados das colunas da direita da Planilha1) e 0se esta for uma linha em branco (abaixo da última linha de dados).
• Definido A2como =IF($Z2=1, INDEX(Sheet1!A:A, $Y2), ""). Se aplicável, arraste para a direita para cobrir as colunas usadas apenas para dados principais. No seu exemplo, isso não é aplicável, porque você tem dados não-chave começando em Column  B. (No meu exemplo, é por meio de Column  B.) Isso implementa as definições das colunas auxiliares: se Zfor 1, extraia os dados principais da Planilha1, caso contrário, em branco.

• No meu exemplo, eu configurei C2para

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

  No seu exemplo, você deve definir B2como

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

  refletindo as duas colunas Sheet1 das quais a coluna Sheet2  Bpode estar extraindo:

  • Coluna  B(“Código Cust”), ou
  • Coluna  G(“Código do Produto”)

  Novamente, isso apenas faz o que as colunas auxiliares dizem para fazer. Adicionamos 1ao Zvalor map 0, 1, 2, e 3 to 1, 2, 3, e 4.  CHOOSEusa o primeiro argumento para indexar nos argumentos seguintes, então

  • Se Zfor 0, em branco,
  • Se Zfor 1, obtenha os dados principais,
  • Se Zfor 2, obtenha o título de Sheet1 Row 1e
  • Se Zfor 3, obtenha os dados não-chave.

• Definir Y3para =IF($Z2<3, $Y2, $Y2+1)e Z3para

  =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 Zvalor na linha anterior for 1ou 2(ou  0), defina esse Yvalor como igual ao valor da linha anterior. Isso ocorre porque cada linha na tabela do seu banco de dados (cada conjunto de valores exclusivos em Colunas A- Fna Planilha1) resulta em pelo menos três linhas na Planilha2. Caso contrário, aumente o Yvalor para endereçar a próxima linha na Planilha1.

  Se o Zvalor anterior for 0, terminamos e preenchemos com zeros. Se o Zvalor anterior for 1ou 2, passe para o próximo valor. Caso contrário, observe os dados principais da Planilha1. Se estiver em branco, suponha que estamos no final dos dados e definimos Zcomo  0. Se for igual à linha anterior, use 3para continuar o que estamos fazendo. Caso contrário, entraremos em um novo conjunto de valores exclusivos, então reinicie o ciclo com a 1.

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

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

Obviamente usei formatação condicional, com fórmula =$Z2=2, para formatar os subtítulos adequadamente.