Return values from columns using a row with (duplicated) primary key to different rows

Question

I need a way to return data from rows that are defined by duplicating key, and return the data in these rows in different columns in a separate sheet with unique key.

I am working with a large file with 20,000+ data with multiple sheets and need to produce the below outcome for one of the data:

Sample data:

Is there any ways that Excel can do this for me? I am not familiar with VBA if that's the way but I can still try with more assistance.

Methods tried:

1. =IFS

2. Power Query (merge and append)

3. index and match

Thank you so much for your ideas! This is taking me a lot of time and I don't even know how to ask google to get to a solution...

Answer 1

In powerquery, using M

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Book#", "Book title"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Book#", "Book title", "Attribute"}, {{"data", each 
    let #"Added Index" = Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)
    in Table.CombineColumns(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")
    , type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Value", "Merged"}, {"Value", "Merged"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded data",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns1"[Merged]), "Merged", "Value")
in #"Pivoted Column"