How to Transpose Results Horizontally

A

andyka

Board Regular
Joined
Sep 20, 2021
Messages
52
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNO
1Results I want to see
2
3Activity IDStartFinishSTAGE GATE 1STAGE GATE 2STAGE GATE 3STAGE GATE 4STAGE GATE 5
4P116/03/2326/02/27P116/03/2316/03/2303/08/2303/08/23
5STAGE GATE 116/03/2316/03/23P221/07/2321/07/23
6STAGE GATE 203/08/2303/08/23P613/09/2313/09/2327/06/2427/06/24
7STAGE GATE 320/03/2520/03/25P717/02/2317/02/2327/11/2327/11/23
8STAGE GATE 429/09/2629/09/26P823/02/2423/02/2425/11/2425/11/24
9STAGE GATE 526/02/2726/02/27P518/10/2318/10/2301/08/2401/08/24
10P221/07/2322/10/26P9
11STAGE GATE 221/07/2321/07/23P1024/10/2324/10/23
12STAGE GATE 313/12/2413/12/24
13STAGE GATE 401/06/2601/06/26
14STAGE GATE 522/10/2622/10/26
15P613/09/2311/11/27
16STAGE GATE 113/09/2313/09/23
17STAGE GATE 227/06/2427/06/24
18STAGE GATE 326/11/2526/11/25
19STAGE GATE 421/06/2721/06/27
20STAGE GATE 511/11/2711/11/27
21P717/02/2324/08/27
22STAGE GATE 117/02/2317/02/23
23STAGE GATE 227/11/2327/11/23
24STAGE GATE 307/05/2507/05/25
25STAGE GATE 431/03/2731/03/27
26STAGE GATE 524/08/2724/08/27
27P823/02/2414/02/28
28STAGE GATE 123/02/2423/02/24
29STAGE GATE 225/11/2425/11/24
30STAGE GATE 320/05/2620/05/26
31STAGE GATE 415/09/2715/09/27
32STAGE GATE 514/02/2814/02/28
33P518/10/2325/11/27
34STAGE GATE 118/10/2318/10/23
35STAGE GATE 201/08/2401/08/24
36STAGE GATE 319/03/2619/03/26
37STAGE GATE 405/07/2705/07/27
38STAGE GATE 525/11/2725/11/27
39P915/09/2327/03/25
40STAGE GATE 315/09/2315/09/23
41STAGE GATE 428/10/2428/10/24
42STAGE GATE 527/03/2527/03/25
43P1024/10/2320/07/26
44STAGE GATE 224/10/2324/10/23
45STAGE GATE 330/10/2430/10/24
46STAGE GATE 420/02/2620/02/26
47STAGE GATE 520/07/2620/07/26
Sheet1
Cell Formulas
RangeFormula
F4F4=IFERROR(INDEX($B$4:$B$1000,MATCH($E4,$A$4:$A$1000,0)+MATCH(F$3,$F$3,0)-1),"")
 
Sort by date Sort by votes
Is it not possible with formula?
andyka said:
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
Click to expand...
Herakles said:
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.

VBA Code: 
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
Click to expand...
Is it not possible with Formula
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Now that you have the Project number you can use SUMIFS using the date columns as the sum ranges, the
ActivityID column as criteria range 1 and the Project ID column as the criteria range 2.

Dates are just numbers and there will be only one date for each combination of Project ID and Stage Gate number.
 
Upvote 0
Try the below using Helper column :

Book4
ABCDEFGHIJKLMNOP
1Results I want to see
2
3HelperActivity IDStartFinishSTAGE GATE 1STAGE GATE 2STAGE GATE 3STAGE GATE 4STAGE GATE 5
4P1P116/03/202326/02/2027P116/03/202316/03/202303/08/202303/08/202320/03/202520/03/202529/09/202629/09/202626/02/202726/02/2027
5P1 STAGE GATE 1STAGE GATE 116/03/202316/03/2023P2 21/07/202321/07/202313/12/202413/12/202401/06/202601/06/202622/10/202622/10/2026
6P1 STAGE GATE 2STAGE GATE 203/08/202303/08/2023P613/09/202313/09/202327/06/202427/06/202426/11/202526/11/202521/06/202721/06/202711/11/202711/11/2027
7P1 STAGE GATE 3STAGE GATE 320/03/202520/03/2025P717/02/202317/02/202327/11/202327/11/202307/05/202507/05/202531/03/202731/03/202724/08/202724/08/2027
8P1 STAGE GATE 4STAGE GATE 429/09/202629/09/2026P823/02/202423/02/202425/11/202425/11/202420/05/202620/05/202615/09/202715/09/202714/02/202814/02/2028
9P1 STAGE GATE 5STAGE GATE 526/02/202726/02/2027P518/10/202318/10/202301/08/202401/08/202419/03/202619/03/202605/07/202705/07/202725/11/202725/11/2027
10P2P221/07/202322/10/2026P9  15/09/202315/09/202328/10/202428/10/202427/03/202527/03/2025
11P2 STAGE GATE 2STAGE GATE 221/07/202321/07/2023P10 24/10/202324/10/202330/10/202430/10/202420/02/202620/02/202620/07/202620/07/2026
12P2 STAGE GATE 3STAGE GATE 313/12/202413/12/2024
13P2 STAGE GATE 4STAGE GATE 401/06/202601/06/2026
14P2 STAGE GATE 5STAGE GATE 522/10/202622/10/2026
15P6P613/09/202311/11/2027
16P6 STAGE GATE 1STAGE GATE 113/09/202313/09/2023
17P6 STAGE GATE 2STAGE GATE 227/06/202427/06/2024
18P6 STAGE GATE 3STAGE GATE 326/11/202526/11/2025
19P6 STAGE GATE 4STAGE GATE 421/06/202721/06/2027
20P6 STAGE GATE 5STAGE GATE 511/11/202711/11/2027
21P7P717/02/202324/08/2027
22P7 STAGE GATE 1STAGE GATE 117/02/202317/02/2023
23P7 STAGE GATE 2STAGE GATE 227/11/202327/11/2023
24P7 STAGE GATE 3STAGE GATE 307/05/202507/05/2025
25P7 STAGE GATE 4STAGE GATE 431/03/202731/03/2027
26P7 STAGE GATE 5STAGE GATE 524/08/202724/08/2027
27P8P823/02/202414/02/2028
28P8 STAGE GATE 1STAGE GATE 123/02/202423/02/2024
29P8 STAGE GATE 2STAGE GATE 225/11/202425/11/2024
30P8 STAGE GATE 3STAGE GATE 320/05/202620/05/2026
31P8 STAGE GATE 4STAGE GATE 415/09/202715/09/2027
32P8 STAGE GATE 5STAGE GATE 514/02/202814/02/2028
33P5P518/10/202325/11/2027
34P5 STAGE GATE 1STAGE GATE 118/10/202318/10/2023
35P5 STAGE GATE 2STAGE GATE 201/08/202401/08/2024
36P5 STAGE GATE 3STAGE GATE 319/03/202619/03/2026
37P5 STAGE GATE 4STAGE GATE 405/07/202705/07/2027
38P5 STAGE GATE 5STAGE GATE 525/11/202725/11/2027
39P9P915/09/202327/03/2025
40P9 STAGE GATE 3STAGE GATE 315/09/202315/09/2023
41P9 STAGE GATE 4STAGE GATE 428/10/202428/10/2024
42P9 STAGE GATE 5STAGE GATE 527/03/202527/03/2025
43P10P1024/10/202320/07/2026
44P10 STAGE GATE 2STAGE GATE 224/10/202324/10/2023
45P10 STAGE GATE 3STAGE GATE 330/10/202430/10/2024
46P10 STAGE GATE 4STAGE GATE 420/02/202620/02/2026
47P10 STAGE GATE 5STAGE GATE 520/07/202620/07/2026
Sheet1
Cell Formulas
RangeFormula
G4:H4,K4:P11,I11:J11,I10,I4:J9,G6:H9,G5,G10:G11G4=IFERROR(VLOOKUP($F4&" "&G$3,$A$4:$D$47,{3,4},FALSE),"")
A4A4=B4
A5:A9A5=IF(LEN(B5)>4,$A$4&" "&B5,B5)
A10,A15,A21,A27,A33,A39,A43A10=IF(LEN(B10)>4,A9&" "&B10,B10)
A11:A14A11=IF(LEN(B11)>4,$A$10&" "&B11,B11)
A16:A20A16=IF(LEN(B16)>4,$A$15&" "&B16,B16)
A22:A26A22=IF(LEN(B22)>4,$A$21&" "&B22,B22)
A28:A32A28=IF(LEN(B28)>4,$A$27&" "&B28,B28)
A34:A38A34=IF(LEN(B34)>4,$A$33&" "&B34,B34)
A40:A42A40=IF(LEN(B40)>4,$A$39&" "&B40,B40)
A44:A47A44=IF(LEN(B44)>4,$A$43&" "&B44,B44)
Dynamic array formulas.
 
Upvote 0
Solution
Sanjeev1976 said:
Try the below using Helper column :

Book4
ABCDEFGHIJKLMNOP
1Results I want to see
2
3HelperActivity IDStartFinishSTAGE GATE 1STAGE GATE 2STAGE GATE 3STAGE GATE 4STAGE GATE 5
4P1P116/03/202326/02/2027P116/03/202316/03/202303/08/202303/08/202320/03/202520/03/202529/09/202629/09/202626/02/202726/02/2027
5P1 STAGE GATE 1STAGE GATE 116/03/202316/03/2023P2 21/07/202321/07/202313/12/202413/12/202401/06/202601/06/202622/10/202622/10/2026
6P1 STAGE GATE 2STAGE GATE 203/08/202303/08/2023P613/09/202313/09/202327/06/202427/06/202426/11/202526/11/202521/06/202721/06/202711/11/202711/11/2027
7P1 STAGE GATE 3STAGE GATE 320/03/202520/03/2025P717/02/202317/02/202327/11/202327/11/202307/05/202507/05/202531/03/202731/03/202724/08/202724/08/2027
8P1 STAGE GATE 4STAGE GATE 429/09/202629/09/2026P823/02/202423/02/202425/11/202425/11/202420/05/202620/05/202615/09/202715/09/202714/02/202814/02/2028
9P1 STAGE GATE 5STAGE GATE 526/02/202726/02/2027P518/10/202318/10/202301/08/202401/08/202419/03/202619/03/202605/07/202705/07/202725/11/202725/11/2027
10P2P221/07/202322/10/2026P9  15/09/202315/09/202328/10/202428/10/202427/03/202527/03/2025
11P2 STAGE GATE 2STAGE GATE 221/07/202321/07/2023P10 24/10/202324/10/202330/10/202430/10/202420/02/202620/02/202620/07/202620/07/2026
12P2 STAGE GATE 3STAGE GATE 313/12/202413/12/2024
13P2 STAGE GATE 4STAGE GATE 401/06/202601/06/2026
14P2 STAGE GATE 5STAGE GATE 522/10/202622/10/2026
15P6P613/09/202311/11/2027
16P6 STAGE GATE 1STAGE GATE 113/09/202313/09/2023
17P6 STAGE GATE 2STAGE GATE 227/06/202427/06/2024
18P6 STAGE GATE 3STAGE GATE 326/11/202526/11/2025
19P6 STAGE GATE 4STAGE GATE 421/06/202721/06/2027
20P6 STAGE GATE 5STAGE GATE 511/11/202711/11/2027
21P7P717/02/202324/08/2027
22P7 STAGE GATE 1STAGE GATE 117/02/202317/02/2023
23P7 STAGE GATE 2STAGE GATE 227/11/202327/11/2023
24P7 STAGE GATE 3STAGE GATE 307/05/202507/05/2025
25P7 STAGE GATE 4STAGE GATE 431/03/202731/03/2027
26P7 STAGE GATE 5STAGE GATE 524/08/202724/08/2027
27P8P823/02/202414/02/2028
28P8 STAGE GATE 1STAGE GATE 123/02/202423/02/2024
29P8 STAGE GATE 2STAGE GATE 225/11/202425/11/2024
30P8 STAGE GATE 3STAGE GATE 320/05/202620/05/2026
31P8 STAGE GATE 4STAGE GATE 415/09/202715/09/2027
32P8 STAGE GATE 5STAGE GATE 514/02/202814/02/2028
33P5P518/10/202325/11/2027
34P5 STAGE GATE 1STAGE GATE 118/10/202318/10/2023
35P5 STAGE GATE 2STAGE GATE 201/08/202401/08/2024
36P5 STAGE GATE 3STAGE GATE 319/03/202619/03/2026
37P5 STAGE GATE 4STAGE GATE 405/07/202705/07/2027
38P5 STAGE GATE 5STAGE GATE 525/11/202725/11/2027
39P9P915/09/202327/03/2025
40P9 STAGE GATE 3STAGE GATE 315/09/202315/09/2023
41P9 STAGE GATE 4STAGE GATE 428/10/202428/10/2024
42P9 STAGE GATE 5STAGE GATE 527/03/202527/03/2025
43P10P1024/10/202320/07/2026
44P10 STAGE GATE 2STAGE GATE 224/10/202324/10/2023
45P10 STAGE GATE 3STAGE GATE 330/10/202430/10/2024
46P10 STAGE GATE 4STAGE GATE 420/02/202620/02/2026
47P10 STAGE GATE 5STAGE GATE 520/07/202620/07/2026
Sheet1
Cell Formulas
RangeFormula
G4:H4,K4:P11,I11:J11,I10,I4:J9,G6:H9,G5,G10:G11G4=IFERROR(VLOOKUP($F4&" "&G$3,$A$4:$D$47,{3,4},FALSE),"")
A4A4=B4
A5:A9A5=IF(LEN(B5)>4,$A$4&" "&B5,B5)
A10,A15,A21,A27,A33,A39,A43A10=IF(LEN(B10)>4,A9&" "&B10,B10)
A11:A14A11=IF(LEN(B11)>4,$A$10&" "&B11,B11)
A16:A20A16=IF(LEN(B16)>4,$A$15&" "&B16,B16)
A22:A26A22=IF(LEN(B22)>4,$A$21&" "&B22,B22)
A28:A32A28=IF(LEN(B28)>4,$A$27&" "&B28,B28)
A34:A38A34=IF(LEN(B34)>4,$A$33&" "&B34,B34)
A40:A42A40=IF(LEN(B40)>4,$A$39&" "&B40,B40)
A44:A47A44=IF(LEN(B44)>4,$A$43&" "&B44,B44)
Dynamic array formulas.
Click to expand...
You are a STAR Sanjeev1976. ITS BEEN SO LONG SINCE i HAVE BEEN LOOKING FOR THE SOLUTION. I am following you from now on. Thax a million for your help.
 
Upvote 0
You are welcome and happy to know that you found the solution
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
107
Fluff
Fluff
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
122
etaf
etaf
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
379
kevin9999
K
J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
436
felixstraube
F
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
324
awoohaw
awoohaw

Forum statistics

Threads
1,215,444
Messages
6,124,892
Members
449,194
Latest member
JayEggleton

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top