Tranposing many date field columns to single row entries

[lewjan]

Hello,
Thank you in advance for assisting; much appreciated!!

I have a spreadsheet with multiple columns of environment plus date field and trying to find a way to transpose each into row.
Example:
Project A has 28 columns, each column pertains to Env + if planned or actual + start or end date
I was hoping to transpose each 28 columns into 28 rows per project.
I have over 200 projects each with 28 env-date columns.

Current:

Project

LabPlannedStart

LabActualStart

LabPlannedComplete

LabActualComplete

DevPlannedStart

DevActualStart

DevPlannedComplete

DevActualComplete

QAPlannedStart

QAActualStart

QAPlannedComplete

QAActualComplete

IntPlannedStart

IntActualStart

IntPlannedComplete

IntActualComplete

Apples

8/1/2021​

7/30/2023​

9/30/2021​

7/30/2023​

########​

7/30/2023​

Oranges

1/16/2022​

2/27/2022​

Pears

10/3/2021​

12/30/2021​

Melons

Cherries

11/27/2022​

2/12/2023​

1/8/2023​

Transposed:

Project	Env-Type	Date
Apples	LabPlannedStart
Apples	LabActualStart
Apples	LabPlannedComplete
Apples	LabActualComplete
Apples	DevPlannedStart	8/1/2021​
Apples	DevActualStart
Apples	DevPlannedComplete	7/30/2023​
Apples	DevActualComplete
Apples	QAPlannedStart	9/30/2021​
Apples	QAActualStart
Apples	QAPlannedComplete	7/30/2023​
Apples	QAActualComplete
Apples	IntPlannedStart	8/31/2021​
Apples	IntActualStart
Apples	IntPlannedComplete	7/30/2023​
Apples	IntActualComplete
Apples	PreProdPlannedStart
Apples	PreProdActualStart
Apples	PreProdPlannedComplete
Apples	PreProdActualComplete
Apples	ProdPlannedStart	12/31/2021​
Apples	ProdActualStart
Apples	ProdPlannedComplete	7/30/2023​
Apples	ProdActualComplete
Apples	OptPlannedStart
Apples	OptActualStart
Apples	OptPlannedComplete
Apples	OptActualComplete
Oranges	LabPlannedStart
Oranges	LabActualStart
Oranges	LabPlannedComplete
Oranges	LabActualComplete
Oranges	DevPlannedStart
Oranges	DevActualStart
Oranges	DevPlannedComplete	1/16/2022​
Oranges	DevActualComplete
Oranges	QAPlannedStart
Oranges	QAActualStart
Oranges	QAPlannedComplete
Oranges	QAActualComplete
Oranges	IntPlannedStart
Oranges	IntActualStart
Oranges	IntPlannedComplete	2/27/2022​
Oranges	IntActualComplete
Oranges	PreProdPlannedStart
Oranges	PreProdActualStart
Oranges	PreProdPlannedComplete
Oranges	PreProdActualComplete
Oranges	ProdPlannedStart
Oranges	ProdActualStart
Oranges	ProdPlannedComplete	4/17/2022​
Oranges	ProdActualComplete
Oranges	OptPlannedStart
Oranges	OptActualStart
Oranges	OptPlannedComplete	5/15/2022​
Oranges	OptActualComplete

 

[DanteAmor]

Try this. Data in sheet1, results in "Transposed" sheet

Sub Transposed_Data()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Sheets("Sheet1").Range("A1").CurrentRegion
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
  
  For i = 2 To UBound(a, 1)
    For j = 2 To UBound(a, 2)
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(1, j)
      b(k, 3) = a(i, j)
    Next j
  Next i
  Sheets("Transposed").Range("A1").Resize(k, 3).Value = b
End Sub

 

[JGordon11]

in Power Query starting with a table named Table1:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Header1toEnd = List.LastN(Table.ColumnNames(Source),Table.ColumnCount(Source)-1),
    ReplaceNulls = Table.ReplaceValue(Source,null,"@#$%^&*&^%$#@",Replacer.ReplaceValue,Header1toEnd),
    UnpivotColumns = Table.UnpivotOtherColumns(ReplaceNulls, {"Project"}, "Env-Type", "Date"),
    RestoreNulls = Table.ReplaceValue(UnpivotColumns,"@#$%^&*&^%$#@",null,Replacer.ReplaceValue,{"Date"})

in
    RestoreNulls

 

[lewjan]

Try this. Data in sheet1, results in "Transposed" sheet

Sub Transposed_Data()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Sheets("Sheet1").Range("A1").CurrentRegion
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
 
  For i = 2 To UBound(a, 1)
    For j = 2 To UBound(a, 2)
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(1, j)
      b(k, 3) = a(i, j)
    Next j
  Next i
  Sheets("Transposed").Range("A1").Resize(k, 3).Value = b
End Sub

 

[lewjan]

Try this. Data in sheet1, results in "Transposed" sheet

Sub Transposed_Data()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Sheets("Sheet1").Range("A1").CurrentRegion
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
 
  For i = 2 To UBound(a, 1)
    For j = 2 To UBound(a, 2)
      k = k + 1
      b(k, 1) = a(i, 1)
      b(k, 2) = a(1, j)
      b(k, 3) = a(i, j)
    Next j
  Next i
  Sheets("Transposed").Range("A1").Resize(k, 3).Value = b
End Sub

Thank you so very much DanteAmor!! it works!! thank you, thank you, thank you!

 

[lewjan]

in Power Query starting with a table named Table1:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Header1toEnd = List.LastN(Table.ColumnNames(Source),Table.ColumnCount(Source)-1),
    ReplaceNulls = Table.ReplaceValue(Source,null,"@#$%^&*&^%$#@",Replacer.ReplaceValue,Header1toEnd),
    UnpivotColumns = Table.UnpivotOtherColumns(ReplaceNulls, {"Project"}, "Env-Type", "Date"),
    RestoreNulls = Table.ReplaceValue(UnpivotColumns,"@#$%^&*&^%$#@",null,Replacer.ReplaceValue,{"Date"})

in
    RestoreNulls

T

in Power Query starting with a table named Table1:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Header1toEnd = List.LastN(Table.ColumnNames(Source),Table.ColumnCount(Source)-1),
    ReplaceNulls = Table.ReplaceValue(Source,null,"@#$%^&*&^%$#@",Replacer.ReplaceValue,Header1toEnd),
    UnpivotColumns = Table.UnpivotOtherColumns(ReplaceNulls, {"Project"}, "Env-Type", "Date"),
    RestoreNulls = Table.ReplaceValue(UnpivotColumns,"@#$%^&*&^%$#@",null,Replacer.ReplaceValue,{"Date"})

in
    RestoreNulls

Thank you JGordon11!!

 

[DanteAmor]

Glad we could help & thanks for the feedback