Consolidando dados de colunas para linhas

Question

Eu tenho uma planilha:

Company 1 | ID1
Company 1 | ID2
Company 1 | ID3
Company 2 | ID4
Company 2 | ID5
Company 2 | ID6
Company 3 | ID7
Company 3 | ID8
Company 3 | ID9

Eu preciso combinar cada empresa em uma linha, com todos os seus IDs nas colunas à direita. Pode haver um número diferente de IDs por empresa.

O resultado final seria:

Company 1 | ID1 | ID2 | ID3
Company 2 | ID4 | ID5 | ID6
Company 3 | ID7 | ID8 | ID9

Como eu poderia conseguir isso? De preferência com VBA.

    

Answer 1

Esta solução de fórmula única pressupõe que cada empresa tenha o mesmo número de linhas:

InsiraaseguintefórmulaemD2ectrl-enter/copy-paste/fill-down&direitanorestantedatabela:

=""&INDEX($A:$B,ROW($D$2)+3*INT(ROW()-ROW($D$2))+MAX(0,COLUMN()-COLUMN($D$2)-1),2-(COLUMN()=COLUMN($D$2)))

Observe que essa fórmula funcionará para qualquer número (idêntico) de linhas de cada empresa e ajustará corretamente se linhas ou colunas forem adicionadas / removidas.

No entanto, a fórmula será interrompida se a tabela em que ela está for copiada / recortada e colada em outro lugar. O $D$2 s precisa ser alterado para corresponder à célula de dados superior esquerda da nova localização da tabela para que ela funcione corretamente novamente.

Se houver um número variável de linhas para cada empresa, a única fórmula necessária é:

=IFERROR(IF(COLUMN()=COLUMN($D$2),""&INDEX($A:$A,MATCH(D1,$A:$A,0)+COUNTIF($A:$A,D1)),INDEX(INDEX($B:$B,MATCH($D2,$A:$A,0)):INDEX($B:$B,MATCH($D2,$A:$A,0)+COUNTIF($A:$A,$D2)-1),COLUMN()-COLUMN($D$2))),"")

    

Answer 2

Aqui está uma alternativa do VBA:

'============================================================================================
' Module     : <any standard module>
' Version    : 0.1.0
' Part       : 1 of 1
' References : N/A
' Source     : https://superuser.com/a/1335738/763880
'============================================================================================
Option Explicit

Public Sub UnPivot()
       Dim ¡ As Long

  Const s_SourceColumns As String = "A:B"
  Const s_TargetColumn As String = "D"
  Const b_HasHeader As Boolean = True

  Dim lngSourceRows As Long
  lngSourceRows = ActiveSheet.Columns(s_SourceColumns).Cells(Rows.Count, 1).End(xlUp).Row + CLng(b_HasHeader)
  Dim varSource As Variant
  varSource = ActiveSheet.Columns(s_SourceColumns).Resize(RowSize:=lngSourceRows + 1).Offset(RowOffset:=-CLng(b_HasHeader)).Value2
  Dim idxNewCompany As Long: idxNewCompany = LBound(varSource, 1)
  Dim strNewCompany As String: strNewCompany = varSource(idxNewCompany, 1)
  Dim varUnPivotedData() As Variant
  ReDim varUnPivotedData(1 To lngSourceRows)
  varUnPivotedData(1) = strNewCompany
  Dim celNextTargetStart As Range
  Set celNextTargetStart = ActiveSheet.Columns(s_TargetColumn).Resize(RowSize:=1).Offset(RowOffset:=-CLng(b_HasHeader))
  Application.ScreenUpdating = False
  For ¡ = LBound(varSource, 1) To UBound(varSource, 1) - 1
    varUnPivotedData(¡ - idxNewCompany + 2) = varSource(¡, 2)
    If varSource(¡ + 1, 1) <> strNewCompany Then
      ReDim Preserve varUnPivotedData(1 To ¡ - idxNewCompany + 2)
      celNextTargetStart.Resize(ColumnSize:=UBound(varUnPivotedData)).Value2 = varUnPivotedData
      Set celNextTargetStart = celNextTargetStart.Offset(RowOffset:=1)
      idxNewCompany = ¡ + 1
      strNewCompany = varSource(idxNewCompany, 1)
      ReDim varUnPivotedData(1 To lngSourceRows)
      varUnPivotedData(1) = strNewCompany
    End If
  Next ¡
  Application.ScreenUpdating = True

End Sub

Basta alterar as constantes na parte superior do código para se adequarem à sua situação.

    

Answer 3

Comofunciona:

• SelecioneA2:A11enomeieointervaloCOMPANY.
• SelecioneB2:B11enomeieosIDsdeintervalo.

• EscrevaessafórmuladematriznacélulaC2paragerarumalistaexclusivadeempresas:

  {=INDEX($A$2:$A$11,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$11),0))}

N.B.ConcluaafórmulaacimadaescritacomCtrl+Shift+Enter&preenchê-lo.

• EscrevaessafórmuladematriznacélulaD2,terminecomCtrl+Shift+Enterepreenchaedesça.

  {=IFERROR(INDEX(IDs,SMALL(IF(Company=$C2,ROW(IDs)-MIN(ROW(IDs))+1),COLUMNS($C$2:C2))),"")}
  

Nota,

• Esta fórmula funcionará sem problemas, caso haja um número variável de IDs para cada empresa.

• Você obtém a lista de IDs em Colunas, conforme mostrado na captura de tela.