Como converter dados de coluna em linhas, agrupadas por identificador

Question

Eu tenho dados como mostrado abaixo em duas colunas e preciso organizá-los horizontalmente em uma linha para cada indivíduo.

Baker   17                              
Baker   37                              
Baker   28                              
Baker   49                              
Baker   27                              
Baker   44                              
Baker   47                              
Baker   32                              
Baker   29                              
Boley   13                              
Boley   46                              
Boley   10                              
Boley   35                              
Boley   32                              
Boley   49                              
Boley   18                              
Boley   47                              
Boley   22                              

Baker   17  37  28  49  27  44  47  32  29
Boley   13  46  10  35  32  49  18  47  22

Como posso fazer isso?

    

Answer 1

1. Faça uma lista de todas as entradas exclusivas da coluna A . Neste exemplo, seria uma questão de digitar dois nomes. Com um conjunto de dados maior, você pode copiar essa coluna para o local de destino e usar a ferramenta Remover Duplicatas para reduzir a lista.

2. À direita do primeiro nome em sua nova lista (Mina começa em F1 , então eu tenho o seguinte em G1 ), insira a seguinte fórmula como uma fórmula de matriz pressionando Ctrl + Shift + Enter :

   =IFERROR(INDEX($B$1:$B$18,SMALL(IF($A$1:$A$18=$F1,ROW($B$1:$B$18),""),COLUMN()-COLUMN($F1))),"")
   

Para referência, A1:A18 é sua lista original de Nomes, B1:B18 é o número correspondente e F1 é o nome "Baker" em sua nova tabela. Preencha isso para a direita, tanto quanto você gosta. Em seguida, preencha para completar sua tabela.

NOTA: Se os dados originais NÃO começarem na Linha 1 , use a seguinte fórmula, em que A1 é a célula superior esquerda dos dados originais:

=IFERROR(INDEX($B$1:$B$18,SMALL(IF($A$1:$A$18=$F1,ROW($B$1:$B$18)-ROW($A$1)+1,""),COLUMN()-COLUMN($F1))),"")

    

Answer 2

Se houver apenas alguns nomes, você pode fazer o seguinte no teclado:

1. Classifique a lista, se ainda não estiver ordenada.
2. Selecione os números em relação ao primeiro nome.
3. Mover para a coluna C da primeira linha do nome
4. Selecione Editar, Colar especial, Transpor valores, OK
5. Excluir tudo, exceto a primeira linha do nome
6. Repita os passos 2 a 5 para todos os outros nomes
7. Excluir coluna B.

Se você tiver muitos nomes, precisará de uma solução VBA:

Option Explicit
Sub TransposeColB()

  Dim ColCrntNext As Integer       ' The next cell on the current row
  Dim ColNextLast As Integer       ' The last cell on the next row
  Dim Offset As Integer            ' Offset from first number on row to last
  Dim RowCrnt As Integer           ' Current row

  With Sheets("Sheet1")     ' !!!! Replace "Sheet1" with name of your sheet !!!!

    ' Sort entire sheet in case a partial tranpose has occurred.
    .Cells.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    RowCrnt = 1
    ' Identify first blank cell on row.  This ensures nothing is overwritten.
    ColCrntNext = .Cells(RowCrnt, Columns.Count).End(xlToLeft).Column + 1
    Do While True
      ' Check name on next row
      Select Case .Cells(RowCrnt + 1, "a").Value
        Case ""
          ' The next row has no name.  The transpose is complete.
          Exit Do
        Case .Cells(RowCrnt, "a").Value
          ' The next row has the same name as the current row.  Move its
          ' numbers to the current row.
          ' Find last used column on the next row
          ColNextLast = .Cells(RowCrnt + 1, _
                                          Columns.Count).End(xlToLeft).Column
          Offset = ColNextLast - 2        ' Offset from first number to last.
                                          ' Normally zero.
          ' Move numbers from next row to current
          .Range(.Cells(RowCrnt, ColCrntNext), _
                 .Cells(RowCrnt, ColCrntNext + Offset)).Value = _
          .Range(.Cells(RowCrnt + 1, 2), _
                 .Cells(RowCrnt + 1, 2 + Offset)).Value
          .Rows(RowCrnt + 1).EntireRow.Delete     ' Delete next row
          ColCrntNext = ColCrntNext + Offset + 1  ' Advance to first blank cell
        Case Else
          ' The next row is for a new name
          RowCrnt = RowCrnt + 1
          ' Identify first blank cell on row. This ensures
          ' nothing is overwritten.
          ColCrntNext = .Cells(RowCrnt, _
                                   Columns.Count).End(xlToLeft).Column + 1
      End Select
    Loop
  End With

End Sub