Transpose of a table with conditions

[Jayapresad]

Dear Friends,

I am having a table with pack nos, items & weight.
I need to regorganise the table based on the pack no. in a row of maximum 5 items.
The file is attched.
Awaiting for your solutions.
Thanks
Jayapresad

 

[AliGW]

Which version of Excel are you using?

 

[Jayapresad]

Dear AliGW,

Thanks for message and I am using Version 2016.

JP

 

[AliGW]

Cross-posted: https://www.excelforum.com/office-365/1318428-transpose-of-a-table-with-conditions.html

 

[AliGW]

I used PowerQuery.

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pack No:", Int64.Type}, {"Item", type text}, {"KG", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"KG", type text}}, "en-GB"),{"Item", "KG"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Item,KG"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Pack No:"}, {{"Count", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Item,KG", "Index"}, {"Item,KG", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Count",{"Pack No:", "Index", "Item,KG"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Item,KG", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Item,KG.1", "Item,KG.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item,KG.1", type text}, {"Item,KG.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Item,KG.1", "Item"}, {"Item,KG.2", "KG"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Pack No:", "Index"}, "Attribute", "Value"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Pack No:", "Attribute", "Index", "Value"}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns1", {{"Index", type text}}, "en-GB"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

 

[Jayapresad]

Thanks Ali for the solution with M code.

Could you please help me the solution with formulas.
JP

 

[vletm]

Jayapresad
You had a long time between Your previous activity.
You have noted about cross-posting.
You should refresh Your memory by reading Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Jayapresad]

Dear VLETM,

Yes, I understood the rules and cross psoting is not allowed. this was pointed out by ALI and accepted.
I am a new user and I was not aware about this and will not repeat hence forth.
If you can give a solution for my prblem with formulas, it will be fine.

Thanks
JP

 

[AliGW]

You haven't understood the rules, evidently. Cross-posting IS allowed, but you must provide links if you do it.

 

[vletm]

Jayapresad
New - Joined 2012 ... eight years ago.
Did You refresh Your memory?

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[Jayapresad]

Jayapresad
New - Joined 2012 ... eight years ago.
Did You refresh Your memory?

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Dear vletm,

Yes I understood that cross posting is not a good practice. If required, the cross posting can be done with mention of cross posting in the message and also give the the link of posting from other forum. Also post the solution if getting from the other forum in this forum.

Thanks

Jayapresad