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

Raghav Chamadiya

New Member
Joined
May 31, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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:
1611738224917.png

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:
1611738224454.png


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


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
 

Attachments

  • 1611737356898.png
    1611737356898.png
    232.7 KB · Views: 7

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

arunsjain

Board Regular
Joined
Apr 29, 2016
Messages
102
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.

Power Query:
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"
 

Attachments

  • Outocme.JPG
    Outocme.JPG
    56.4 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,127,715
Messages
5,626,457
Members
416,186
Latest member
shamm28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top