Transpose rows for a single column based on equal value in other columns

[Raghav Chamadiya]

Hi, so I am trying to transpose the values of a single column into 3 seperate columns if all the other columns values are same. This is what my initial data looks like:

I want the data of column A split into column headings, right now it has 3 unique values: Batch1, Batch2, Batch3. And I want column E values to be inside those columns as rows with all other columns same.
For example if I apply filter on the actual data to make columns C same, it looks like this:

And in the final result, I want it like this:

Similarly for the other column C values as well. I was thinking of writing a VBA for this with a loop, but I am not sure how to do it. Any help will be really appreciated.
This is the google drive link to the file with public access: Excel Link

 

[arunsjain]

Hi,

Try Power Query, Convert your excel data in Table (CTRL + T) and apply following transformation in Power query. Refer to outcome snapshot. It is a very quick transformation. With table in future if you add more data in table then refresh the output table.

I hope it will help.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Batch Reference", type text}, {"Timepoint", type text}, {"Condition", type text}, {"T Reported Name", type text}, {"R Formatted Entry", type number}, {"R Reported Name", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Batch Reference"]), "Batch Reference", "R Formatted Entry", List.Sum)
in
    #"Pivoted Column"