0

So I have a table like this and I want to split it into separate transactions for each item with the same IDs. Essentially we would have 3 rows with Order ID 1 but one with Pen, one with Pencil and one with Ruler. Similar for the rest.

Order ID Category Amount
1 Pen,Pencil,Ruler 1,2,3
2 Ruler,Eraser 3,2
3 Compass,Pen,Eraser 4,1,2
4 Pen,Pencil 1,2

This is what I want the table to look like (I will only do the first few rows)

Order ID Category Amount
1 Pen 1
1 Pencil 2
1 Ruler 3

I have managed to do this one way through first using text to columns with a delimiter to split the category and the amounts bit. Then from here I copy and pasted with transpose and shifted the data around to get my desired table.

I was wondering if there is an easier, quicker and better way of doing this perhaps since this wouldn't be ideal for large datasets.

Thanks.

1
  • Which Excel version are you using? If you have Excel O365, there are text handling functions like TEXTSPLIT that make this easier to do in a formula.
    – Max
    Sep 23 at 3:56

1 Answer 1

0

You can accomplish this using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range or from within sheet
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.
  • Read the comments and explore the Applied Steps to understand the algorithm
let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table24"]}[Content],

//set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", Int64.Type}, {"Category", type text}, {"Amount", type text}}),

//Add custom column where the contents of the Category and Amount column are combined into a single list
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({Text.Split([Category],","), Text.Split([Amount],",")})),

//Remove the original category and amount columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Category", "Amount"}),

//Expand the list column into rows (each one will have a list corresponding to a single pair of Category/Amount
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),

//Create a two-element comma-separated text string from each list
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", 
        each Text.Combine(List.Transform(_, Text.From), ","), type text}),

//Split the text string, by the comma, into two rows
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", 
        Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Category", "Amount"}),

//set the data types
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}, {"Amount", Int64.Type}})
in
    #"Changed Type1"

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.