Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo transponer celdas en una columna en base a valores únicos en otra columna?

Supongamos que tiene un rango de datos que contiene dos columnas, ahora desea transportar celdas en una columna a filas horizontales basadas en valores únicos en otra columna para obtener el siguiente resultado. ¿Tiene alguna buena idea para resolver este problema en Excel?

doc transpone valores únicos 1

Transponer celdas en una columna en base a valores únicos con fórmulas

Transponer celdas en una columna en base a valores únicos con código VBA

Transponer celdas en una columna en base a valores únicos con Kutools para Excel



Con las siguientes fórmulas de matriz, puede extraer los valores únicos y transponer sus datos correspondientes en filas horizontales, haga lo siguiente:

1. Ingrese esta fórmula de matriz: = INDICE ($ A $ 2: $ A $ 16, PARTIDO (0, CONTABLE ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) en una celda en blanco, D2, por ejemplo, y presione Shift + Ctrl + Enter claves juntas para obtener el resultado correcto, vea la captura de pantalla:

doc transpone valores únicos 2

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

2. A continuación, arrastre el controlador de relleno hacia las celdas para extraer todos los valores únicos, consulte la captura de pantalla:

doc transpone valores únicos 3

3. Y luego continúe ingresando esta fórmula en la celda E2: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0), y recuerda presionar Shift + Ctrl + Enter claves para obtener el resultado, ver captura de pantalla:

doc transpone valores únicos 4

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

4. A continuación, arrastre el controlador de relleno a la derecha de las celdas en las que desea mostrar los datos transpuestos hasta que aparezca 0, vea la captura de pantalla:

doc transpone valores únicos 5

5. Y luego continúe arrastrando el controlador de relleno hasta el rango de celdas para obtener los datos transpuestos como se muestra en la siguiente captura de pantalla:

doc transpone valores únicos 6


Puede ser que las fórmulas sean complejas para usted, aquí puede ejecutar el siguiente código VBA para obtener el resultado deseado que necesita.

1. Mantenga presionado el ALT + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.

2. Hacer clic recuadro > Móduloy pegue el siguiente código en Módulo Ventana.

Código de VBA: Transponga celdas en una columna en función de valores únicos en otra columna:

Sub transposeunique()
'updateby Extendoffice 20151207
    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. Entonces presione F5 tecla para ejecutar este código, y aparecerá un cuadro emergente para recordarle que seleccione el rango de datos que desea usar, vea la captura de pantalla:

doc transpone valores únicos 7

4. Y luego haz clic OK botón, aparecerá otro cuadro de aviso para recordarle que seleccione una celda para poner el resultado, vea la captura de pantalla:

doc transpone valores únicos 8

6. Hacer clic OK botón, y los datos en la columna B se han transpuesto en base a valores únicos en la columna A, ver captura de pantalla:

doc transpone valores únicos 9


Si usted tiene Kutools for Excel, combinando el Filas de combinación avanzadas y Células partidas utilidades, puede terminar rápidamente esta tarea sin ninguna fórmula o código.

Kutools for Excel : con más de 300 útiles complementos de Excel, de prueba sin límite en días 60.

Después de instalar Kutools para Excel, haga lo siguiente:

1. Seleccione el rango de datos que desea usar. (Si desea conservar los datos originales, primero copie y pegue los datos en otra ubicación).

2. Luego haga clic Kutools > Contenido > Filas de combinación avanzadas, mira la captura de pantalla:

doc transpone valores únicos 10

3. En la Combine Filas basadas en columnas cuadro de diálogo, realice las siguientes operaciones:

(1.) Haga clic en el nombre de columna sobre el que desea transponer los datos y seleccione Clave primaria;

(2.) Haga clic en otra columna que desee transponer, y haga clic Combinar luego elija un separador para separar los datos combinados, como espacio, coma, punto y coma.

doc transpone valores únicos 11

4. Luego haga clic Ok botón, los datos en la columna B se combinaron en una celda según la columna A, ver captura de pantalla:

doc transpone valores únicos 12

5. Y luego seleccione las celdas combinadas, y haga clic Kutools > Texto > Células partidas, mira la captura de pantalla:

doc transpone valores únicos 13

6. En la Células partidas cuadro de diálogo, seleccione Dividir en columnas bajo la Tipo opción, y luego elija el separador que separa sus datos combinados, vea la captura de pantalla:

doc transpone valores únicos 14 14

7. Luego haga clic Ok y seleccione una celda para poner el resultado dividido en el cuadro de diálogo emergente, vea la captura de pantalla:

doc transpone valores únicos 15

8. Hacer clic OK, y obtendrás el resultado que necesites. Ver captura de pantalla:

doc transpone valores únicos 16

Descargue y pruebe de forma gratuita Kutools for Excel Now!


Kutools for Excel: con más de 200 útiles complementos de Excel, de prueba sin límite en días 60. Descargar y prueba gratis ahora!


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Más de potentes funciones de 300. Funciona con Office 2007-2019 y 365. Soporta todos los idiomas. Fácil despliegue en compañía. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Eduardo Sequeira · 1 months ago
    I have a data set which has multiple IDs in column A, and has connected data in column B. I used the above formula and altered it a bit so that I am transposing the cells in the column B into a row based on the unique ID tied to it in column A. The formula used to identify the unique IDs is: =INDEX($A$2:$A$13409, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$13409), 0)). The formula used to do the transposing is: =IFERROR(INDEX($B$2:$B$13409, MATCH(0, IF($A$2:$A$13409<>$D2, 1, 0)+COUNTIF($D2:D2,$B$2:$B$13409), 0)), "N/A"). Both given in the article, only slightly altered.

    The issue is my data set in column B has duplicates, sometimes appearing one after another, and I need all of the values in the column to be presented in the rows.

    The image attached is what I would like the table to show (this is a small sample size, the true dataset has over 13,000 entries). What is happening now is when a repeat value is encountered, it will not count it.
    i.e. Row 9 for ID 11980 now only shows 0 -31.79 -0.19 -0.74 N/A N/A .... when what I need it to show instead is 0 0 -31.79 -0.19 -0.74 0 0 N/A N/A ....

    Is there a way to work around this issue and resolve it?

    Thank you in advance!
  • To post as a guest, your comment is unpublished.
    arielle · 2 months ago
    I have a data set in Columns A (Unique ID) - E. Each row has data based on the ID#, there are multiple rows for each ID# but I want one row per ID# with all of the other data in columns (it would be 5 columns long minimum and 25 maximum depending on how many each unique ID has). I found a code but it only works for two columns. I had to concatenate the four columns (not including ID) then delimit after running the macro (lot of work). For 15,000 rows of data this is extra time consuming. Is there an endless column macro that would work? Thanks in advance everyone for your help!
    ID CODE ST CODE# DATE
  • To post as a guest, your comment is unpublished.
    martha Bright · 2 months ago
    The macro did not work. It just copied the contents in cell A1.
  • To post as a guest, your comment is unpublished.
    Vinod · 2 months ago
    =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) worked for me to transpose the unique values of A column into a new column BUT...is there a way to get the all the values in B column to be transposed as given below:

    Product Order Date Product Order Order Order Order Order Order Order
    KTE 100 3/3/2019 KTE 100 100 100 200 100 150 100
    KTO 150 3/3/2019 KTO 150 100 200 100 150 200
    KTE 100 3/4/2019 BOT 150 100 200 150 100 200
    KTO 100 3/4/2019 COD 200 150 100 150
    KTO 200 3/5/2019
    KTE 100 3/5/2019
    BOT 150 3/5/2019
    BOT 100 3/6/2019
    KTO 100 3/6/2019
    KTE 200 3/6/2019
    BOT 200 3/7/2019
    COD 200 3/7/2019
    KTE 100 3/7/2019
    KTO 150 3/7/2019
    BOT 150 3/8/2019
    KTE 150 3/8/2019
    COD 150 3/8/2019
    BOT 100 3/9/2019
    BOT 200 3/10/2019
    COD 100 3/10/2019
    KTO 200 3/10/2019
    COD 150 3/11/2019
    KTE 100 3/11/2019
  • To post as a guest, your comment is unpublished.
    Sean Vizcarrondo · 6 months ago
    So I am working for a company. We have columns for info such as Last name, first name, rank, section, phone number, address. Is there a way I can use a similar formula to transpose the entire row of info to a column by names?
  • To post as a guest, your comment is unpublished.
    kumar · 6 months ago
    Hi can we add each row and give the output in one column, with the above functionality.
  • To post as a guest, your comment is unpublished.
    Raj · 6 months ago
    Need to get the same out put but for predefined columns to be selected would be ($A,$B) and need the output column Position on $D$1.
    If any one have idea's that would be a great help!!!!
  • To post as a guest, your comment is unpublished.
    Kate · 10 months ago
    =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) worked for me to transpose the unique values of a column into a new column BUT...is there a way to ad in a sort function so that the new column created is transposed in ascending order?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Prial · 11 months ago
    Same as Dave, I need to do the exactly opposite of this. Table 2 to transpose to Table 1. Input Table 2, Output Table 1.
  • To post as a guest, your comment is unpublished.
    Dave Babler · 1 years ago
    I need to do exactly the opposite of this. I have many many columns associated with a row id and I want to paste them into two columns
    for example I have
    rowid, value, value1, value2, value3, value4, value..225
    100, Dolphin, 255, 9--, sarah, jameson, ....
    179, Router, flood, jason, 89, nose



    I want it to look like this
    100, Dolphin
    100, 255
    100, 9--
    100, sarah
    100, jaemeson
    179, Router
    179, flood
    179, jason
    179, 89
    179, nose
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hello, Dave,
      To solve your problem, please use the below VBA code: (Note: When you select the data range that you want to transpose, please exclude the header row.)

      Sub TransposeUnique_2()
      Dim xLRow, xLCount As Long
      Dim xRg As Range
      Dim xOutRg As Range
      Dim xObjRRg 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:", "Kutools for Excel", xTxt, , , , , 8)
      Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
      If xRg Is Nothing Then Exit Sub
      If (xRg.Rows.count < 2) Or _
      (xRg.Areas.count > 1) Then
      MsgBox "Invalid selection", , "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
      Application.ScreenUpdating = False
      xLCount = xRg.Columns.count
      For xLRow = 1 To xRg.Rows.count
      Set xObjRRg = Range(xRg.Cells(xLRow, 2), xRg.Cells(xLRow, xLCount)).SpecialCells(xlCellTypeConstants)
      xObjRRg.Copy
      xOutRg.Offset(, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
      Application.CutCopyMode = False
      Range(Cells(xOutRg.Row, xOutRg.Column), Cells(xOutRg.Row + xObjRRg.count - 1, xOutRg.Column)).Value = xRg.Cells(xLRow, 1).Value
      Set xOutRg = xOutRg.Offset(RowOffset:=xObjRRg.count)
      Next
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        Anna · 10 months ago
        Thank you, it works perfectly, you saved me 2 days! :)
  • To post as a guest, your comment is unpublished.
    Gino · 1 years ago
    With the formula below:

    =IFERROR(INDEX($B$2:$B$45, MATCH(0, COUNTIF($D2:D2,$B$2:$B$45)+IF($A$2:$A$10<>$D2, 1, 0), 0)), 0)

    How can I transpose the data using approximate matches? Say, I want to extract all the values from Column B that match the first 9 characters / digits from Column A? Column B has 11 characters while A only 9. thank you!
  • To post as a guest, your comment is unpublished.
    Guest · 1 years ago
    i want to transpose duplicate values too (all values - unique + duplicate) and not just unique values. Can you give the formula for that too.
  • To post as a guest, your comment is unpublished.
    Aidan · 1 years ago
    Is there a way of doing this in reverse? I.e. data in rows of varying length and so sorting it into two columns? See attached.
  • To post as a guest, your comment is unpublished.
    Didin Mathew · 1 years ago
    How to do the transpose if B column doesn't have unique values but still need those values
    KTE 100
    KTE 100
    Assuming that they are two different transaction
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi,Didin,

      Can you give your problem more clearly or detailed?
      You can insert an example screenshot for your problem.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Sanjeev Chidambaram · 1 years ago
    I just want to do the opposite. Like i have the end result already, and i want to achieve the first step.
    • To post as a guest, your comment is unpublished.
      Chris · 1 years ago
      I am looking for the same thing
      • To post as a guest, your comment is unpublished.
        Juan Carlos · 1 years ago
        Did you find any solution for the opposite scenario? Thanks!
        • To post as a guest, your comment is unpublished.
          Prial · 11 months ago
          I want to do the opposite as well. Any solution you got gents?
          • To post as a guest, your comment is unpublished.
            skyyang · 11 months ago
            Hello, guys,
            To get the opposite result based on the example of this article, you can apply the following VBA code: (Note:When selecting the data range that you want to transpose, please exclude the header row)

            Sub TransposeUnique_2()
            Dim xLRow, xLCount As Long
            Dim xRg As Range
            Dim xOutRg As Range
            Dim xObjRRg 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:", "Kutools for Excel", xTxt, , , , , 8)
            Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
            If xRg Is Nothing Then Exit Sub
            If (xRg.Rows.count < 2) Or _
            (xRg.Areas.count > 1) Then
            MsgBox "Invalid selection", , "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
            Application.ScreenUpdating = False
            xLCount = xRg.Columns.count
            For xLRow = 1 To xRg.Rows.count
            Set xObjRRg = Range(xRg.Cells(xLRow, 2), xRg.Cells(xLRow, xLCount)).SpecialCells(xlCellTypeConstants)
            xObjRRg.Copy
            xOutRg.Offset(, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            Application.CutCopyMode = False
            Range(Cells(xOutRg.Row, xOutRg.Column), Cells(xOutRg.Row + xObjRRg.count - 1, xOutRg.Column)).Value = xRg.Cells(xLRow, 1).Value
            Set xOutRg = xOutRg.Offset(RowOffset:=xObjRRg.count)
            Next
            Application.ScreenUpdating = True
            End Sub
  • To post as a guest, your comment is unpublished.
    Pradeep · 2 years ago
    First step itself fails
    =INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) gives Value Not Available error
  • To post as a guest, your comment is unpublished.
    Piyush · 2 years ago
    This was fantastic.
    I had an excel with around 2000 unique values in row A and couldn't have managed this exercise without your help.

    Many many thanks.
  • To post as a guest, your comment is unpublished.
    Tim · 2 years ago
    How would I go in the opposite direction? From multiple columns into a single column? Thanks in advance!

    Tim