¿Cómo transponer celdas en una columna basándose en valores únicos en otra columna?

Supongamos que tienes un rango de datos que contiene dos columnas, y ahora deseas transponer las celdas en una columna a filas horizontales basándote en los valores únicos de otra columna para obtener el siguiente resultado. ¿Tienes alguna buena idea para resolver este problema en Excel?

Transponer celdas en una columna basándose en valores únicos con fórmulas

Transponer celdas en una columna basándose en valores únicos con código VBA

Transponer celdas en una columna basándose en valores únicos con Kutools para Excel

Transponer celdas en una columna basándose en valores únicos con fórmulas

Con las siguientes fórmulas matriciales, puedes extraer los valores únicos y transponer sus datos correspondientes en filas horizontales; por favor, sigue estos pasos:

1. Ingresa esta fórmula matricial: =INDICE($A$2:$A$16; COINCIDIR(0; CONTAR.SI($D$1:$D1; $A$2:$A$16); 0)) en una celda en blanco, D2, por ejemplo, y presiona simultáneamente las teclas Mayús + Ctrl + Entrar para obtener el resultado correcto, ver captura de pantalla:

Nota: En la fórmula anterior, A2:A16 es la columna de la cual quieres enumerar los valores únicos, y D1 es la celda encima de esta celda de fórmula.

2. Luego arrastra el controlador de relleno hacia abajo en las celdas para extraer todos los valores únicos, ver captura de pantalla:

3. Y luego sigue ingresando esta fórmula en la celda E2: =SI.ERROR(INDICE($B$2:$B$16; COINCIDIR(0; CONTAR.SI($D2:D2;$B$2:$B$16)+SI($A$2:$A$16<>$D2; 1; 0); 0)); 0), y recuerda presionar las teclas Mayús + Ctrl + Entrar para obtener el resultado, ver captura de pantalla:

Nota: En la fórmula anterior: B2:B16 es la columna de datos que deseas transponer, A2:A16 es la columna en la que deseas transponer los valores, y D2 contiene el valor único que has extraído en el Paso 1.

4. Luego arrastra el controlador de relleno hacia la derecha de las celdas donde deseas enumerar los datos transpuestos hasta que se muestre 0, ver captura de pantalla:

5. Y luego sigue arrastrando el controlador de relleno hacia abajo en el rango de celdas para obtener los datos transpuestos como se muestra en la siguiente captura de pantalla:

Transponer celdas en una columna basándose en valores únicos con código VBA

Quizás las fórmulas sean complejas para que las entiendas, aquí puedes ejecutar el siguiente código VBA para obtener el resultado deseado que necesitas.

1. Mantén presionadas las teclas ALT + F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones.

2. Haz clic en Insertar > Módulo, y pega el siguiente código en la Ventana del Módulo.

Código VBA: Transponer celdas en una columna basándose en valores únicos en otra columna:

Sub transposeunique()
'updateby Extendoffice
    Dim xLRow As Long
    Dim i As Long
    Dim xCrit As String
    Dim xCol  As New Collection
    Dim xRg As Range
    Dim xOutRg As Range
    Dim xTxt As String
    Dim xCount As Long
    Dim xVRg As Range
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
    If (xRg.Columns.Count <> 2) Or _
       (xRg.Areas.Count > 1) Then
        MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
        Exit Sub
    End If
    Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
    If xOutRg Is Nothing Then Exit Sub
    Set xOutRg = xOutRg.Range(1)
    xLRow = xRg.Rows.Count
    For i = 2 To xLRow
        xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
    Next
    Application.ScreenUpdating = False
    For i = 1 To xCol.Count
        xCrit = xCol.Item(i)
        xOutRg.Offset(i, 0) = xCrit
        xRg.AutoFilter Field:=1, Criteria1:=xCrit
        Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
        If xVRg.Count > xCount Then xCount = xVRg.Count
        xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
        xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next
    xOutRg = xRg.Cells(1, 1)
    xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
    xRg.Rows(1).Copy
    xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
    xRg.AutoFilter
    Application.ScreenUpdating = True
End Sub

3. Luego presiona la tecla F5 para ejecutar este código, y aparecerá un cuadro de aviso para recordarte que selecciones el rango de datos que deseas usar, ver captura de pantalla:

4. Y luego haz clic en el botón Aceptar, aparecerá otro cuadro de aviso para recordarte que selecciones una celda para colocar el resultado, ver captura de pantalla:

6. Haz clic en el botón Aceptar, y los datos en la columna B se han transpuesto basándose en los valores únicos en la columna A, ver captura de pantalla:

Transponer celdas en una columna basándose en valores únicos con Kutools para Excel

Si tienes Kutools para Excel, combinando las utilidades Combinar Filas Avanzado y Dividir Celdas, puedes completar rápidamente esta tarea sin ninguna fórmula o código.

Después de instalar Kutools para Excel, por favor, sigue los siguientes pasos:

1. Selecciona el rango de datos que deseas usar. (Si deseas conservar los datos originales, copia y pega los datos en otra ubicación primero.)

2. Luego haz clic en Kutools > Combinar y Dividir > Combinar Filas Avanzado, ver captura de pantalla:

3. En el cuadro de diálogo Combinar Filas Basadas en Columna, por favor realiza las siguientes operaciones:

(1.) Haz clic en el nombre de la columna en la que deseas transponer los datos, y selecciona Clave Principal;

(2.) Haz clic en otra columna que deseas transponer, y haz clic en Combinar y luego elige un separador para separar los datos combinados, como espacio, coma, punto y coma.

4. Luego haz clic en el botón Aceptar, los datos en la columna B se han combinado en una sola celda basada en la columna A, ver captura de pantalla:

5. Y luego selecciona las celdas combinadas, y haz clic en Kutools > Combinar y Dividir > Dividir Celdas, ver captura de pantalla:

6. En el cuadro de diálogo Dividir Celdas, selecciona Dividir Datos bajo la opción Tipo, y luego elige el separador que separa tus datos combinados, ver captura de pantalla:

7. Luego haz clic en el botón Aceptar, y selecciona una celda para colocar el resultado dividido en el cuadro de diálogo emergente, ver captura de pantalla:

8. Haz clic en Aceptar, y obtendrás el resultado que necesitas. Ver captura de pantalla:

¡Descarga y prueba gratis Kutools para Excel ahora!

Demostración: Transponer celdas en una columna basándose en valores únicos con Kutools para Excel