How to split cells that have carriage return into their own row?

I

im2bz2p345

Board Regular
Joined
Mar 31, 2008
Messages
229
Hi all,

I have insurance data that is in this structure. For each type of insurance (column H and I), I want a new row with data that is the same besides these two cells.

A little hard to explain, but the below screenshot is what I'm after. Notice column H and I have carriage returns in the data. I'm simply wanting to split these out into their own rows, but not affect the other data (column A-G & J-N).


Excel 2016 (Windows) 64 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD="bgcolor: #FFFF00"]Header1[/TD]
[TD="bgcolor: #FFFF00"]Header2[/TD]
[TD="bgcolor: #FFFF00"]Header3[/TD]
[TD="bgcolor: #FFFF00"]Header4[/TD]
[TD="bgcolor: #FFFF00"]Header5[/TD]
[TD="bgcolor: #FFFF00"]Header6[/TD]
[TD="bgcolor: #FFFF00"]Header7[/TD]
[TD="bgcolor: #FFFF00"]Header8[/TD]
[TD="bgcolor: #FFFF00"]Header9[/TD]
[TD="bgcolor: #FFFF00"]Header10[/TD]
[TD="bgcolor: #FFFF00"]Header11[/TD]
[TD="bgcolor: #FFFF00"]Header12[/TD]
[TD="bgcolor: #FFFF00"]Header13[/TD]
[TD="bgcolor: #FFFF00"]Header14[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]10001
10002
10004
10005
10006
10003[/TD]
[TD]AETNA
AETNA MEDICARE ADVANTAGE
MEDICARE RX ESSENTIALS
MEDICARE RX PLUS
MEDICARE RX PREMIER
AETNA - GENERIC[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]10101
10102[/TD]
[TD]UNITED HEALTH CARE
UHC - GENERIC[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[TD]b[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]10301
10302[/TD]
[TD]HUMANA
HUMANA - GENERIC[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]Result shoud look like this:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD="bgcolor: #FFFF00"]Header1[/TD]
[TD="bgcolor: #FFFF00"]Header2[/TD]
[TD="bgcolor: #FFFF00"]Header3[/TD]
[TD="bgcolor: #FFFF00"]Header4[/TD]
[TD="bgcolor: #FFFF00"]Header5[/TD]
[TD="bgcolor: #FFFF00"]Header6[/TD]
[TD="bgcolor: #FFFF00"]Header7[/TD]
[TD="bgcolor: #FFFF00"]Header8[/TD]
[TD="bgcolor: #FFFF00"]Header9[/TD]
[TD="bgcolor: #FFFF00"]Header10[/TD]
[TD="bgcolor: #FFFF00"]Header11[/TD]
[TD="bgcolor: #FFFF00"]Header12[/TD]
[TD="bgcolor: #FFFF00"]Header13[/TD]
[TD="bgcolor: #FFFF00"]Header14[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]10001[/TD]
[TD]AETNA[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]
10002​
[/TD]
[TD]AETNA MEDICARE ADVANTAGE[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]10004[/TD]
[TD]MEDICARE RX ESSENTIALS[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

Is there any way to do this easily?

~ Im2bz2p345 :)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
How about
Code: 
Sub imb2bz()
   Dim i As Long, Rws As Long
   Dim SpH As Variant, SpI As Variant
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      SpH = Split(Cells(i, 8), vbLf)
      SpI = Split(Cells(i, 9), vbLf)
      Rws = UBound(SpH)
      If Rws >= 1 Then
         Rows(i + 1).Resize(Rws).Insert
         Cells(i, 1).Resize(Rws + 1, 14).Filldown
         Cells(i, 8).Resize(Rws + 1, 2).Value = Application.Transpose(Array(SpH, SpI))
      End If
   Next i
End Sub
 
Upvote 0
alansidman said:
Look at this link. Scroll down to see how to split the cells on a carriage return using Power Query.

https://excelgorilla.com/power-bi/power-query/split-column-by-carriage-return/

This will then put them in separate cells on the same line. You will need to unpivot that data to get it into rows.
Click to expand...

Can't get this to work.

Screenshot of what I get when I follow these instructions and load the Power Query data back into Excel: https://i.snag.gy/MzPDdO.jpg

You mentioned unpivot the data (I assume this is done in Power Query as well)?). I see a button to Unpivot by column and when I press that, it moves the Header 8 into a row, which isn't what I'm after: https://i.snag.gy/VUH1B8.jpg

Header 8 moved into the row field: https://i.snag.gy/x3M4fR.jpg

What am I doing wrong here?

~ Im2bz2p345 :)
 
Upvote 0
Post a file to either Google Drive, Box Net or Drop Box that is a realistic representative of your actual file. I will attempt to work with it in Power Query. Pictures are pretty much worthless as one cannot manipulate the data in a picture and to be honest, I don't have the time nor the desire to try an recreate your file.
 
Upvote 0
example file

as I can see there is no CR but LF :)

result should be like this (I hope :) )

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Header1[/td][td=bgcolor:#70AD47]Header2[/td][td=bgcolor:#70AD47]Header3[/td][td=bgcolor:#70AD47]Header4[/td][td=bgcolor:#70AD47]Header5[/td][td=bgcolor:#70AD47]Header6[/td][td=bgcolor:#70AD47]Header7[/td][td=bgcolor:#70AD47]Header8[/td][td=bgcolor:#70AD47]Header9[/td][td=bgcolor:#70AD47]Header10[/td][td=bgcolor:#70AD47]Header11[/td][td=bgcolor:#70AD47]Header12[/td][td=bgcolor:#70AD47]Header13[/td][td=bgcolor:#70AD47]Header14[/td][td=bgcolor:#70AD47]Index[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
10001​
[/td][td=bgcolor:#E2EFDA]AETNA[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]
10002​
[/td][td]AETNA MEDICARE ADVANTAGE[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
10004​
[/td][td=bgcolor:#E2EFDA]MEDICARE RX ESSENTIALS[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]
10005​
[/td][td]MEDICARE RX PLUS[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
10006​
[/td][td=bgcolor:#E2EFDA]MEDICARE RX PREMIER[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]
10003​
[/td][td]AETNA - GENERIC[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]a[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]
10101​
[/td][td=bgcolor:#E2EFDA]UNITED HEALTH CARE[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]b[/td][td=bgcolor:#E2EFDA]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td]b[/td][td]b[/td][td]b[/td][td]b[/td][td]b[/td][td]b[/td][td]
10102​
[/td][td]UHC - GENERIC[/td][td]b[/td][td]b[/td][td]b[/td][td]b[/td][td]b[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]
10301​
[/td][td=bgcolor:#E2EFDA]HUMANA[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]c[/td][td]c[/td][td]c[/td][td]c[/td][td]c[/td][td]c[/td][td]c[/td][td]
10302​
[/td][td]HUMANA - GENERIC[/td][td]c[/td][td]c[/td][td][/td][td][/td][td][/td][td]
10​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
M-code for post above

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in
    Source

// Table1 (2)
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Header9"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Header8", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Header8"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Header8", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

// Table1 (3)
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Header8"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Header9", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Header9"),
    #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 1, 1)
in
    #"Added Index"

// Merge1
let
    Source = Table.NestedJoin(#"Table1 (2)",{"Index"},#"Table1 (3)",{"Index"},"Table1 (3)",JoinKind.LeftOuter),
    #"Expanded Table1 (3)" = Table.ExpandTableColumn(Source, "Table1 (3)", {"Header9"}, {"Header9"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Table1 (3)",{"Header1", "Header2", "Header3", "Header4", "Header5", "Header6", "Header7", "Header8", "Header9", "Header10", "Header11", "Header12", "Header13", "Header14", "Index"})
in
    #"Reordered Columns"[/SIZE]

should be checked because of typos ;)

Edit:
I forgot remove Index from result table in previous post
Whole action is not optimized but this is cosmetic :diablo:
 
Last edited:
Upvote 0
sandy666 said:
example file

as I can see there is no CR but LF :)

result should be like this (I hope :) )
Click to expand...

OMG sandy666, thank you!!

The problem was that it was indeed a LF (Line Feed) and NOT a CR (Carriage Return). It might be because this data was pulled from a Unix command line database.

Everything else works amazing!! Thanks for posting your M-code. I honestly haven't used Power Query a lot, but can now see some of the power behind it. I was able to follow your code and perform the same steps in Power Query (i.e. started off by creating 2 tables, removed a column (9), split column by LF, add an index column at the end, load to connection only, ... repeat the same steps with the other table & this time removed the other column (8), add index column at the end.. then performed a merge of the two tables using left out and click on both indexed columns as the "primary key").

Very cool!

Thanks to all for the suggestions and help. Always fun learning new tricks.

'Till next time,

~ Im2bz2p345 :)
 
Last edited:
Upvote 0
You are welcome :)

Now you can use Thanks/Like button in the post which helped you

Have a nice day
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Selection of a Range from Sheet1 based on an input on Sheet2
3 4 5
Replies
42
Views
1K
mumps
mumps
K
  • Question Question
VBA Macro Transpose/Delete Rows and continue down spreadsheet with differing total number of rows
Replies
3
Views
346
johnnyL
J
T
  • Question Question
How to print filtered listbox data
Replies
4
Views
2K
Tayl4n
T
M
  • Question Question
Highlight Duplicates with Alternate Colors
Replies
8
Views
498
Legacy 143009
L
D
  • Question Question
Macro forcing values into specific cells
Replies
9
Views
581
Joe4
Joe4

Forum statistics

Threads
1,223,152
Messages
6,170,389
Members
452,324
Latest member
stuart1980

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top