Transpose column by date( Excel or VBA)

[vradhak7]

Hello Experts,
I have attached the file showing how my input looks like and how I would require my output. I could have more number of similar rows for different cities in column B and more date range and I would like to transpose it by date. Is there an efficient way to do this ? Your expertise would be very useful.1772717727

[Bob Phillips]

Here is a Power query solution. You will need to convert your data to a table first.


let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Headers.Demote = Table.DemoteHeaders(Source),
Date1.Extract = Table.FillDown(
Table.AddColumn(Headers.Demote,
"Date.1",
each if Date.From([Column4]) < Date.FromText("01/01/2000") then null else Date.From([Column4])
),
{"Date.1"}
),
Date2.Extract = Table.FillDown(
Table.AddColumn(Date1.Extract,
"Date.2",
each if Date.From([Column5]) < Date.FromText("01/01/2000") then null else Date.From([Column5])
),
{"Date.2"}
),
Date3.Extract = Table.FillDown(
Table.AddColumn(Date2.Extract,
"Date.3",
each if Date.From([Column6]) < Date.FromText("01/01/2000") then null else Date.From([Column6])
),
{"Date.3"}
),
Date4.Extract = Table.FillDown(
Table.AddColumn(Date3.Extract,
"Date.4",
each if Date.From([Column7]) < Date.FromText("01/01/2000") then null else Date.From([Column7])
),
{"Date.4"}
),
Headers.Promote = Table.PromoteHeaders(Date4.Extract),
DateValues.Transpose = Table.UnpivotOtherColumns(Headers.Promote, {"Region", "Station Name", "Category"}, "Attribute", "Value"),
Gumpf.Remove = Table.SelectRows(DateValues.Transpose,
each ([Attribute] = "27/11/2016" or
[Attribute] = "28/11/2016" or
[Attribute] = "29/11/2016" or
[Attribute] = "30/11/2016")
),
DateCategory.Sort = Table.Sort(Gumpf.Remove,{{"Attribute", Order.Ascending}, {"Category", Order.Ascending}}),
Final.Layout = Table.ReorderColumns(
Table.RenameColumns(DateCategory.Sort,
{{"Attribute", "Date"}}
),
{"Date", "Region", "Station Name", "Category", "Value"}
)
in
Final.Layout