Taking 9 columns and making them vertical with other data

[jackson1990]

Hey everyone,

I was working on a project in excel where I have 9 columns of dates. They each column represent a milestone in a project (it's kind of a scheduler). I want to make a pivot table that makes it so I can see all the milestone dates. Currently this is impossible as they are in 9 columns, and in a pivot table that means they can't all be shown in an organized manner. So what I wanted to do is make all of the dates vertical in one column, but I also need to pull in the data from other columns to match it. So here is a simple example (did less milestones) of how it is currently and how I want it to go, if it is even possible to automatically? As I have a large set of data.

Currently:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Project Name
[/TD]
[TD]Milestone1[/TD]
[TD]Milestone2[/TD]
[TD]Milestone3[/TD]
[TD]Milestone4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Test1[/TD]
[TD]7/3/2018[/TD]
[TD]7/20/2018[/TD]
[TD]7/28/2018[/TD]
[TD]8/4/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test2[/TD]
[TD]1/1/2018[/TD]
[TD]1/6/2018[/TD]
[TD]1/20/2018[/TD]
[TD]1/25/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test3[/TD]
[TD]4/12/2018[/TD]
[TD]4/18/2018[/TD]
[TD]4/23/2018[/TD]
[TD]4/25/2018[/TD]
[/TR]
</tbody>[/TABLE]

How I want it to transform
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Project Name[/TD]
[TD]Milestone #[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Test1[/TD]
[TD]1
[/TD]
[TD]7/3/2018
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Test1[/TD]
[TD]2[/TD]
[TD]7/20/2018[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Test1[/TD]
[TD]3[/TD]
[TD]7/28/2018[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Test1[/TD]
[TD]4[/TD]
[TD]8/4/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test2[/TD]
[TD]1[/TD]
[TD]1/1/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test2[/TD]
[TD]2[/TD]
[TD]1/6/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test2[/TD]
[TD]3[/TD]
[TD]1/20/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test2[/TD]
[TD]4[/TD]
[TD]1/25/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test3[/TD]
[TD]1[/TD]
[TD]4/12/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test3[/TD]
[TD]2[/TD]
[TD]4/18/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test3[/TD]
[TD]3[/TD]
[TD]4/23/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test3[/TD]
[TD]4[/TD]
[TD]4/25/2018[/TD]
[/TR]
</tbody>[/TABLE]

Is this even possible?

Thanks for your help everyone!

 

[sheetspread]

The reverse pivot table will do this:

https://www.youtube.com/watch?v=xmqTN0X-AgY

 

[jackson1990]

Brilliant! Exactly what I was looking for. Thank you so much!

 

[jackson1990]

One further question, is there a way to keep that "extracted,drilled down" data connected to the source? I ask because if a user changes the original I would like it to show on the new pivot table as well. Currently, it seems like that data is static and not connected to the source.

 

[sandy666]

You can try PowerQuery (2010/2013 add-in, 2016 built-in).
It will give you refreshable table (Ctrl+Alt+F5) to see new data if any will be changed in source table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Project Name", type text}, {"Milestone1", type date}, {"Milestone2", type date}, {"Milestone3", type date}, {"Milestone4", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Project Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Date"}, {"Attribute", "Milestone #"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Milestone","",Replacer.ReplaceText,{"Milestone #"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Milestone #", Int64.Type}})
in
    #"Changed Type1"

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Project Name[/td][td=bgcolor:#70AD47]Milestone #[/td][td=bgcolor:#70AD47]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Test1[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

03/07/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]Test1[/td][td]

2​

[/td][td]

20/07/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Test1[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

28/07/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]Test1[/td][td]

4​

[/td][td]

04/08/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]Test2[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

01/01/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]Test2[/td][td]

2​

[/td][td]

06/01/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]Test2[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

20/01/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2​

[/td][td]Test2[/td][td]

4​

[/td][td]

25/01/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]Test3[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

12/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3​

[/td][td]Test3[/td][td]

2​

[/td][td]

18/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]Test3[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

23/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3​

[/td][td]Test3[/td][td]

4​

[/td][td]

25/04/2018​

[/td][/tr]
[/table]

 

[jackson1990]

Hmmm that's a good thought! The only issue is my company is split between 2016, and 2013/2010, and I'm not sure the user base is tech savvy enough to install power query. Great thought though! It might not be possible otherwise though.

 

[sandy666]

With 2016 you can do that then convert to Excel Table and propagate it to others as normal Excel table.
But ok, I don't know all issues there

 

[jackson1990]

True...let me give it a shot to see if that works and I'll report back after to let you know if that works.

 

[sandy666]

Sure, but one thing - if you convert to Excel Table you will lost Refresh for new data.
So choose

You can send the file without converting and people without powerQuery will see the Excel table and will not be able to refresh but people with PowerQuery will be able to refresh

 

[sheetspread]

it seems like that data is static and not connected to the source.

Yes, and the only remedies I know are through VBA (including the macro recorder which requires little to no coding knowledge), Power Query, or creating a new table each time the data is changed. You can keep or delete the first table.