Excel: как преобразовать список имя-значение повторяющейся структуры записи в таблицу / кросс-таблицу?

"текст в столбцы" и сводная таблица не являются решениями!

источник данных

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

в

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

первая часть хороша, когда разделитель также указан в дате и может быть достигнут через:

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

Который красиво разделяется только на первый разделитель

Google досадно для "список в таблицу" или "список в кросс-таблицу" возвращает результаты с точностью до наоборот

РЕДАКТИРОВАТЬ: в каждой записи содержится около 200 полей, значения всегда отображаются в одном и том же порядке, но некоторые из них присутствуют не всегда, что означает, что их нельзя решить напрямую с помощью команды OFFSET, которая в противном случае работала бы

Источник

1 ответ

Решение

Предположим, что ваши исходные данные находятся в столбце A Листа 1:

Сначала поместите заголовки столбцов в Sheet2

Затем запустите этот короткий макрос:

Sub DataReOrganizer()
   Dim s1 As Worksheet, s2 As Worksheet
   Dim N As Long, i As Long, K As Long, v As String
   Set s1 = 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

произвести это в Sheet2:

Chr(39) используется для сохранения вашего формата даты и предотвращения его изменения во времени в Excel.

Источник
Другие вопросы по тегам