I have excel file with a lot of rows, one of the column has many values separated by commas.

I want to break the 3rd column into rows, which I achieved easily. But I also want to copy column one and two. So it should look like this:

What I did was first I delimited the column and then copied rest of the columns with the newly created column manually. There are thousands of records so it is taking very long time.

Any help will be really appreciated. Thank you!

CodePudding user response：

With Microsoft-365 latest release, below formula should work.

=DROP(REDUCE(0,REDUCE(0,C2:C3,LAMBDA(a,x,VSTACK(a,CONCAT(CHOOSEROWS(A2:B3,ROW(x)-1)&"|")&TEXTSPLIT(x,,",")))),LAMBDA(p,q,VSTACK(p,TEXTSPLIT(q,"|")))),2)

CodePudding user response：

This would be rather quick and easy with PowerQuery:

• Select your data (or a single cell inside your data), A1:C3 in this example;
• Import this data into PowerQuery AKA Get&Transform (including headers);
• Select column 'Serial Number';
• Under "Home" click, "Split Column" and choose "By Delimiter";
• Use the comma as delimiter, and under advanced tick "Rows";
• Hit "OK" and close PQ. Your results are there.

M-code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model", type text}, {"Vendor", type text}, {"Serial Number", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Serial Number", Splitter.SplitTextByDelimiter("#(#)(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Serial Number"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Serial Number", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Serial Number"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Serial Number", Int64.Type}})
in
    #"Changed Type1"

If a formula is a must, try:

Formula in E1:

=LET(x,A2:C3,REDUCE(A1:C1,ROW(x)-1,LAMBDA(a,b,VSTACK(a,LET(y,INDEX(x,b,{1,2}),IFERROR(HSTACK(y,TEXTSPLIT(INDEX(x,b,3),,", ")),y))))))