Array + Transpose + Table

[Excel_pal]

Excel Champs,

Looking for creative solves for the below problem. I have a project sheet that looks something likes below in a table. I need a button function that can transfer in a separate sheet all the values in that row for that project transposed in a column.

Master file (Sheet 1)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project[/TD]
[TD]Owner[/TD]
[TD]Status[/TD]
[TD]Risk[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]Jack[/TD]
[TD]On Track[/TD]
[TD]None[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]Mary[/TD]
[TD]RIsk[/TD]
[TD]Short on funding[/TD]
[TD]Joe, Mary, Mira[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD]Vick[/TD]
[TD]Need Help[/TD]
[TD]Need more resources[/TD]
[TD]Kayle, Mike[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project Name[/TD]
[TD]Button/Function (Create Summary)[/TD]
[/TR]
</tbody>[/TABLE]


Example: If Project is selected in sheet 2 cell A1, the outcome below should be in a separate sheet:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD]Project A[/TD]
[/TR]
[TR]
[TD]Owner[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]Status[/TD]
[TD]On Track[/TD]
[/TR]
[TR]
[TD]Risk[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]Team[/TD]
[TD]None[/TD]
[/TR]
</tbody>[/TABLE]

Any help on this, would greatly appreciate it.
Thank you.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project Name[/TD]
[TD]Button/Function (Create Summary)

[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[alansidman]

another alternative is to use Power Query. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Attribute[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Value[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Project[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Owner[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Jack[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Status[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]On Track[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Risk[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]None[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Team[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]None[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Project[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Owner[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Mary[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]9[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Status[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]RIsk[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Risk[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Short on funding[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]11[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Team[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Joe, Mary, Mira[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]12[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Project[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]13[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Owner[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Vick[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]14[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Status[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Need Help[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]15[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Risk[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Need more resources[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]16[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Team[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Kayle, Mike[/td]
[/tr]
[/table]

 

[Excel_pal]

Here's a dropbox link to a mockup table version with a guide to how it is made for reference for your sheet.

Note that you should make a copy of your original file and see if you like the end result first before applying to your real file.


https://www.dropbox.com/s/6zm758ke1ahqb8n/array_transpose_table_Excel_pal_table_version.xlsx?dl=0

Thank you. It worked!

 

[Excel_pal]

Thanks. You made it much easier and it worked.

 

[alansidman]

Thanks. You made it much easier and it worked.

Who is this meant for? Did you try the Power Query Solution?