excel: como converter a lista de valores nominais da estrutura de registros repetidos em tabela / crosstab?

Question

'texto para colunas' e tabela dinâmica não são as soluções!

dados de origem

myval: value1
mydate: 11:11:2001
myname: bob diamond
mynum: 5648
endmarker
myval: value2
mydate: 10:10:2008
myname: jimmy knapp
mynum: 6661
endmarker

em

myval            mydate           myname            mynum
value 1          11:11:2001       bob diamond       5648
value 2          10:10:2008       jimmy knapp       6661

a primeira parte está bem onde o delimitador também aparece na data e pode ser obtido via:

 =IFERROR(LEFT(A1,FIND(":",A1,1)-1),"")     
 =IFERROR(RIGHT(A1,LEN(A1)-FIND(":",A1,1)),"")

Que se divide bem apenas no primeiro delimitador

google irritantemente por "lista para tabela" ou "lista para crosstab" retorna resultados exatamente para o oposto

EDIT : existem cerca de 200 campos em cada registro, os valores sempre aparecem na mesma ordem, mas alguns deles nem sempre estão presentes, o que significa que não pode ser resolvido diretamente com algo como o comando OFFSET que de outra forma teria funcionado

    

Answer 1

Vamos supor que seus dados de origem estão na coluna A de Folha1 :

PrimeirocoloqueoscabeçalhosdascolunasemFolha2

Emseguida,executeestamacrocurta:

SubDataReOrganizer()Dims1AsWorksheet,s2AsWorksheetDimNAsLong,iAsLong,KAsLong,vAsStringSets1=Sheets("Sheet1")
   Set s2 = Sheets("Sheet2")
   N = s1.Cells(Rows.Count, "A").End(xlUp).Row
   K = 2

   For i = 1 To N
      v = s1.Cells(i, "A").Text
      If v = "endmarker" Then
         K = K + 1
      Else
         ary = Split(v, ": ")
         MsgBox ary(1)
         If ary(0) = "myval" Then s2.Cells(K, 1) = ary(1)
         If ary(0) = "mydate" Then s2.Cells(K, 2) = Chr(39) & ary(1)
         If ary(0) = "myname" Then s2.Cells(K, 3) = ary(1)
         If ary(0) = "mynum" Then s2.Cells(K, 4) = ary(1)
      End If
   Next i
End Sub

para produzir isso em Folha2 :

O Chr (39) é usado para preservar seu formato de data e impedir que o Excel o altere em um momento.