Evite que columnas específicas se repitan en Excel

Question

Hay un informe que extraigo de nuestro sistema ERP que enumera información detallada del pedido. Enumerará el número de pedido, el código de cliente, el nombre del cliente, la fecha del pedido, el estado del pedido, el total del pedido, el código del producto, el nombre del producto y la cantidad solicitada, el precio unitario y el precio extendido. Si un pedido tiene varias líneas, la información del encabezado aparece varias veces.

Así es como se ven los datos sin procesar.

Estoy tratando de encontrar una manera en Excel para evitar que las líneas del encabezado se repitan para cada una de las líneas de detalle de la línea.

Preferiría que los datos se vieran así cuando termine. Básicamente, cada una de las líneas se repite debajo de sus respectivas filas de encabezado.

Otro formato aceptable podría ser el siguiente. Creo que esto puede ser más simple. Pude hacer esto con una tabla dinámica.

No estoy seguro de si esto requeriría VBA o no. Intenté resaltar las filas y usar eliminar duplicados, pero eso mueve todas las líneas juntas. Cualquier ayuda sería muy apreciada.

Answer 1

Sé que esto está mal visto, pero el problema parecía interesante, así que escribí el VBA.

---

Pruebe el código a continuación. Configuré las constantes para que funcionen en el ejemplo que diste, pero es posible que desees cambiarlas para tu aplicación 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

Aquí tienes un pequeño truco para lograr tu objetivo. Se puede aplicar a celdas de cualquier columna. Digamos que comenzamos con:

Y queremos evitar ver todo el excesoMiguel's, etc. Hacemos clic en la celdaA2y aplique formato condicional para que si el valor de la celda es el mismo que el de la celda que está encima, haga que el color de la fuente sea el mismo que el color del fondo de la celda:

Luego copiamos la celdaA2y PasteSpecialFormats en la columna. Esto "oculta" los valores repetidos:

Los datos reales se dejan intactos, ¡solo se cambia la visualización!

Answer 3

pongo

                       

en la Hoja1 y pude hacer que la Hoja2 se viera así:

Utiliza dos columnas auxiliares que, por supuesto, puedes mover hacia la derecha tanto como quieras (o necesites) y que puedes ocultar.

• Establezca A1(en la Hoja 2) =Sheet1!A1y arrastre hacia la derecha para cubrir las columnas que de otro modo se duplicarían en varias líneas. En su ejemplo, esto sería Columna  F. (En mi ejemplo, es Columna  C).
• Establecer Y2en 2y Z2en 1. El valor en la columna  Ydice qué fila de la Hoja1esteLa fila está extrayendo datos. La columna  Zes 1si se trata de una fila de encabezado (extrayendo datos de las columnas de la izquierda de la Hoja 1; es decir, campos clave), 2si se trata de una fila de subtítulo, 3si se trata de una fila de subdatos (extrayendo datos de las columnas de la derecha de la Hoja1), y 0si se trata de una fila en blanco (debajo de la última fila de datos).
• Ajustado A2a =IF($Z2=1, INDEX(Sheet1!A:A, $Y2), ""). Si corresponde, arrastre hacia la derecha para cubrir las columnas que se usan solo para datos clave. En su ejemplo, esto no es aplicable porque tiene datos no clave que comienzan en Columna  B. (En mi ejemplo, es a través de Columna  B). Esto implementa las definiciones de las columnas auxiliares: si Zes 1, extraiga los datos clave de la Hoja1; de lo contrario, en blanco.

• En mi ejemplo, puse C2en

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

  En su ejemplo, debe configurarlo B2en

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

  reflejando las dos columnas de la Hoja1 de las que la Columna de la Hoja2  Bpodría estar extrayendo:

  • Columna  B(“Código personalizado”), o
  • Columna  G(“Código de producto”)

  Nuevamente, esto simplemente hace lo que las columnas auxiliares le indican que haga. Agregamos 1al Zvalor para mapear 0, 1, 2, y 3 a 1, 2, 3y 4.  CHOOSEutiliza el primer argumento para indexar los siguientes argumentos, por lo que

  • Si Zestá 0en blanco,
  • Si Zes así 1, obtenga los datos clave,
  • Si Zes así 2, obtenga el título de la fila Hoja1 1y
  • Si Zes así 3, obtenga los datos no clave.

• Establecer Y3a =IF($Z2<3, $Y2, $Y2+1)y Z3a

  =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))))
  

  (todo en una línea). Estos dicen, si el Zvalor en la fila anterior es 1o 2(o  0), establezca este Yvalor en el mismo valor que el de la fila anterior. Esto se debe a que cada fila de la tabla de su base de datos (cada conjunto de valores únicos en Columnas A, Fen la Hoja1) da como resultado al menos tres filas en la Hoja2. De lo contrario, incremente el Yvalor para abordar la siguiente fila en la Hoja1.

  Si el Zvalor anterior es 0, terminamos y completamos con ceros. Si el valor anterior Zes 1o 2, avance al siguiente valor. De lo contrario, mire los datos clave de la Hoja1. Si está en blanco, supongamos que estamos al final de los datos y configurados Zen  0. Si es igual que la fila anterior, use 3para continuar con lo que estamos haciendo. De lo contrario, nos encontraremos con un nuevo conjunto de valores únicos, así que reinicie el ciclo con un archivo 1.

• Arrastre hacia abajo lo suficiente para obtener todos sus datos.

Si sus valores únicos no son únicos individualmente (por ejemplo, si tiene A4= A5pero B4≠ B5), expanda las pruebas en Columna  Z para probar tantas columnas como necesite (combinándolas con AND(…)).

Obviamente utilicé formato condicional, con una fórmula de =$Z2=2, para formatear los subtítulos adecuadamente.