Transform data from rows to columns

[cuinbc]

[FONT=&quot]Hi all,

My raw data looks like this:[/FONT]
[FONT=&quot]The data was copied and pasted from a notepad, so each line is on a different row.

[/FONT]

====Starting details=====
20: abc
23B: def
32A: ghk
50K: lmn
=====Ending details=====
====Starting details=====
20: 123
23B: 321
32A: 456
50K: 789
=====Ending details=====
====Starting details=====
20: qwe
23B: wer
32A: ert
50K: rty
=====Ending details=====

<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>

[FONT=&quot]
[/FONT]

• Please note that in reality, the details that follow the (20:, 23B:, 32A: 50K are all different lengths, but the details always follow 20:/23B:/32A:/50K:
  
• Text to column won't work because the data is scattered on different rows.
  
• I have access to power query

[FONT=&quot]I need to achieve the below:[/FONT]

20:	23B:	32A:	50K:
abc	def	ghk	lmn
123	234	456	789
qwe	wer	ert	rty

<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</tbody>

[FONT=&quot]- That is, basically, extract text following each common of "20:", "23B:" "32A" & "50K", and organize them to columns.[/FONT]

• First of all, should I figure out a way to combine rows between the common texts "====Starting details=====" & "=====Ending details====="? It would become something like this:

20: abc 23B: 321 32A: 456 50K: 789

<thead style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;">
</thead><tbody style="margin: 0px; padding: 0px; border: 0px; font: inherit; vertical-align: baseline;"></tbody>

[FONT=&quot]2) Once step 1 is solved, I can figure out a way to extract text between 20:/23B:/32A:/50K:, and place it on different columns?[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Please let me know if this is feasible? What formulas/tools on power query should I utilize?[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Open to any ideas.[/FONT]

 

[sandy666]

Is that what you want?

done with PowerQuery (Get&Transform)
btw. this is not vba

SOURCE		RESULT
Column1		20:	23B:	32A:	50K:
====Starting details=====		abc	def	ghk	lmn
20: abc		123	321	456	789
23B: def		qwe	wer	ert	rty
32A: ghk
50K: lmn
=====Ending details=====
====Starting details=====
20: 123
23B: 321
32A: 456
50K: 789
=====Ending details=====
====Starting details=====
20: qwe
23B: wer
32A: ert
50K: rty
=====Ending details=====

M-Code

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "=====Ending details=====" and [Column1] <> "====Starting details=====")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Column1.1"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Column1.2")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Count"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"[/SIZE]

 

[Peter_SSs]

I know you mentioned Power Query and you have a suggestion for that, but in case it is of interest to you, following are suggestions for vba and formulas to do the job assuming the data is uniform like your samples.

1) VBA
(Assuming data in col A starting at row 1, & results into columns B:E)

Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 4)
  For i = 1 To UBound(a)
    If a(i, 1) Like "====S*" Then
      k = k + 1
      For j = 1 To 4
        b(k, j) = Split(a(i + j, 1), ": ", 1)(1)
      Next j
      i = i + 5
    End If
  Next i
  Range("B2:E2").Resize(k).Value = b
  Range("B1:E1").Value = Array("'20:", "23B:", "32A:", "50K:")
End Sub

2) Formula, copied across and down.

Excel Workbook
	A	B	C	D	E
1	====Starting details=====	20:	23B:	32A:	50K:
2	20: abc	abc	def	ghk	lmn
3	23B: def	123	321	456	789
4	32A: ghk	qwe	wer	ert	rty
5	50K: lmn
6	=====Ending details=====
7	====Starting details=====
8	20: 123
9	23B: 321
10	32A: 456
11	50K: 789
12	=====Ending details=====
13	====Starting details=====
14	20: qwe
15	23B: wer
16	32A: ert
17	50K: rty
18	=====Ending details=====
Sheet1