Transform data from rows to columns

cuinbc

New Member
Joined
Oct 29, 2018
Messages
1
[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:
abcdefghklmn
123234456789
qwewerertrty

<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]
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,794
Is that what you want?

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

SOURCERESULT
Column120:23B:32A:50K:
====Starting details=====abcdefghklmn
20: abc123321456789
23B: defqwewerertrty
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
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,310
Office Version
  1. 365
Platform
  1. Windows
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)
Code:
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
ABCDE
1====Starting details=====20:23B:32A:50K:
220: abcabcdefghklmn
323B: def123321456789
432A: ghkqwewerertrty
550K: lmn
6=====Ending details=====
7====Starting details=====
820: 123
923B: 321
1032A: 456
1150K: 789
12=====Ending details=====
13====Starting details=====
1420: qwe
1523B: wer
1632A: ert
1750K: rty
18=====Ending details=====
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,109,395
Messages
5,528,487
Members
409,820
Latest member
gabrielrms

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top