How to Transpose Results Horizontally

[andyka]

Is it possible to transpose the data horizontally as shown in table on right. i tried various method but has been unsuccessfull. i am looking for formula version. Please help.

Stage Gates Report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1							Results I want to see
2
3	Activity ID	Start	Finish			STAGE GATE 1		STAGE GATE 2		STAGE GATE 3		STAGE GATE 4		STAGE GATE 5
4	P1	16/03/23	26/02/27		P1	16/03/23	16/03/23	03/08/23	03/08/23
5	STAGE GATE 1	16/03/23	16/03/23		P2			21/07/23	21/07/23
6	STAGE GATE 2	03/08/23	03/08/23		P6	13/09/23	13/09/23	27/06/24	27/06/24
7	STAGE GATE 3	20/03/25	20/03/25		P7	17/02/23	17/02/23	27/11/23	27/11/23
8	STAGE GATE 4	29/09/26	29/09/26		P8	23/02/24	23/02/24	25/11/24	25/11/24
9	STAGE GATE 5	26/02/27	26/02/27		P5	18/10/23	18/10/23	01/08/24	01/08/24
10	P2	21/07/23	22/10/26		P9
11	STAGE GATE 2	21/07/23	21/07/23		P10			24/10/23	24/10/23
12	STAGE GATE 3	13/12/24	13/12/24
13	STAGE GATE 4	01/06/26	01/06/26
14	STAGE GATE 5	22/10/26	22/10/26
15	P6	13/09/23	11/11/27
16	STAGE GATE 1	13/09/23	13/09/23
17	STAGE GATE 2	27/06/24	27/06/24
18	STAGE GATE 3	26/11/25	26/11/25
19	STAGE GATE 4	21/06/27	21/06/27
20	STAGE GATE 5	11/11/27	11/11/27
21	P7	17/02/23	24/08/27
22	STAGE GATE 1	17/02/23	17/02/23
23	STAGE GATE 2	27/11/23	27/11/23
24	STAGE GATE 3	07/05/25	07/05/25
25	STAGE GATE 4	31/03/27	31/03/27
26	STAGE GATE 5	24/08/27	24/08/27
27	P8	23/02/24	14/02/28
28	STAGE GATE 1	23/02/24	23/02/24
29	STAGE GATE 2	25/11/24	25/11/24
30	STAGE GATE 3	20/05/26	20/05/26
31	STAGE GATE 4	15/09/27	15/09/27
32	STAGE GATE 5	14/02/28	14/02/28
33	P5	18/10/23	25/11/27
34	STAGE GATE 1	18/10/23	18/10/23
35	STAGE GATE 2	01/08/24	01/08/24
36	STAGE GATE 3	19/03/26	19/03/26
37	STAGE GATE 4	05/07/27	05/07/27
38	STAGE GATE 5	25/11/27	25/11/27
39	P9	15/09/23	27/03/25
40	STAGE GATE 3	15/09/23	15/09/23
41	STAGE GATE 4	28/10/24	28/10/24
42	STAGE GATE 5	27/03/25	27/03/25
43	P10	24/10/23	20/07/26
44	STAGE GATE 2	24/10/23	24/10/23
45	STAGE GATE 3	30/10/24	30/10/24
46	STAGE GATE 4	20/02/26	20/02/26
47	STAGE GATE 5	20/07/26	20/07/26
Sheet1

Cell Formulas
Range	Formula
F4	F4	=IFERROR(INDEX($B$4:$B$1000,MATCH($E4,$A$4:$A$1000,0)+MATCH(F$3,$F$3,0)-1),"")

 

[andyka]

Are there any experts who may be able to offer a solution to my problem?

 

[Herakles]

Are you able to have the P1, P2 etc in a seperate column, the first column?

 

[andyka]

I ideally, no because this is how i get my RAW data extracted from another software. and its easy just to update my dates in column B and C from RAW DATA file.

 

[andyka]

Are you able to have the P1, P2 etc in a seperate column, the first column?

Any Solutions Please!

 

[Herakles]

Can we see how your raw data is strutured?

 

[andyka]

Can we see how your raw data is strutured?

exactly the same way as it shown in my post the only difference is i have over 1200 rows with additional activities and its in CSV format

 

[andyka]

Can we see how your raw data is strutured?

Any Solution Yet Herakles. I looked everywhere for solution but could not find it. it seems to be really tricky. Your help would be highly appreciated.

 

[Herakles]

Lets hope that this will work for you.

It assumes that you have nothing to the right of the results and that your data is on Sheet1.

Put this into a standard code module.

Test it on a copy of your data.

If you have not got a row set up for a P number or a column set up for a Stage Gate number it will warn you and then
abort the procedure.

Public Sub subTranspose()
' https://www.mrexcel.com/board/threads/how-to-transpose-results-horizontally.1234004/#post-6042705
Dim rngData As Range
Dim arr() As Variant
Dim i As Integer
Dim intRow As Integer
Dim strP As String
Dim rngFound As Range
Dim rngRow As Range
Dim rngColumn As Range
Dim Ws As Worksheet

On Error GoTo Err_Handler

    ActiveWorkbook.Save

    Set Ws = Worksheets("Sheet1")
    
    Set rngData = Ws.Range("A3:C" & Range("A1").End(xlDown).Row)
        
    arr = rngData
    
    Ws.Activate
    
    Ws.Range("F4:AZ10000").Value = ""
    
    intRow = 4
    
    For i = LBound(arr) To UBound(arr)
    
        If Left(arr(i, 1), 1) = "P" Then
            strP = arr(i, 1)
            Set rngRow = Ws.Range("E4:E500").Find(strP, LookIn:=xlValues)
            If rngRow Is Nothing Then
                MsgBox "Cannot find a row for " & strP, vbCritical, "Warning!"
            End If
            intRow = intRow + 1
        Else
            Set rngColumn = Ws.Range("E3").Resize(1, 50).Find(arr(i, 1), LookIn:=xlValues)
            If rngColumn Is Nothing Then
                MsgBox "Cannot find a column for " & arr(i, 1), vbCritical, "Warning!" & " " & Ws.Range("E3").Resize(1, 50).Address
            End If
            Ws.Cells(intRow, rngColumn.Column).Resize(1, 2).Value = Array(arr(i, 2), arr(i, 3))
        End If
        
    Next i
    
    MsgBox "The data has been transposed.", vbInformation, "Confirmation"
    
Exit_Handler:
    
    Exit Sub
    
Err_Handler:
    
   MsgBox Err.Number & " " & Err.Description
   
   Resume Exit_Handler
    
End Sub

 

[andyka]

Lets hope that this will work for you.

It assumes that you have nothing to the right of the results and that your data is on Sheet1.

Put this into a standard code module.

Test it on a copy of your data.

If you have not got a row set up for a P number or a column set up for a Stage Gate number it will warn you and then
abort the procedure.

Public Sub subTranspose()
' https://www.mrexcel.com/board/threads/how-to-transpose-results-horizontally.1234004/#post-6042705
Dim rngData As Range
Dim arr() As Variant
Dim i As Integer
Dim intRow As Integer
Dim strP As String
Dim rngFound As Range
Dim rngRow As Range
Dim rngColumn As Range
Dim Ws As Worksheet

On Error GoTo Err_Handler

    ActiveWorkbook.Save

    Set Ws = Worksheets("Sheet1")
   
    Set rngData = Ws.Range("A3:C" & Range("A1").End(xlDown).Row)
       
    arr = rngData
   
    Ws.Activate
   
    Ws.Range("F4:AZ10000").Value = ""
   
    intRow = 4
   
    For i = LBound(arr) To UBound(arr)
   
        If Left(arr(i, 1), 1) = "P" Then
            strP = arr(i, 1)
            Set rngRow = Ws.Range("E4:E500").Find(strP, LookIn:=xlValues)
            If rngRow Is Nothing Then
                MsgBox "Cannot find a row for " & strP, vbCritical, "Warning!"
            End If
            intRow = intRow + 1
        Else
            Set rngColumn = Ws.Range("E3").Resize(1, 50).Find(arr(i, 1), LookIn:=xlValues)
            If rngColumn Is Nothing Then
                MsgBox "Cannot find a column for " & arr(i, 1), vbCritical, "Warning!" & " " & Ws.Range("E3").Resize(1, 50).Address
            End If
            Ws.Cells(intRow, rngColumn.Column).Resize(1, 2).Value = Array(arr(i, 2), arr(i, 3))
        End If
       
    Next i
   
    MsgBox "The data has been transposed.", vbInformation, "Confirmation"
   
Exit_Handler:
   
    Exit Sub
   
Err_Handler:
   
   MsgBox Err.Number & " " & Err.Description
  
   Resume Exit_Handler
   
End Sub

Hi. It did not work. I got error "91 Object variable or With block variable not set".
I have inserted Activity id in column d such as "Project1" "Project2" etc