Turn Columns into Rows, with a catch

[jasonwebber]

I'm trying to turn sales funnel stages into a "path", showing each stage of the sales funnel in a single row. Currently the date is in two columns (old stage and new stage) and the path is shown in the old stage as a single column, multiple rows. The catch is that the final stage is in the 'new stage' column. What I need is to get each stage in it's own cell in rows, not columns, and include the final stage as well. Attached is a spreadsheet that shows the exported data on the left and the desired data on the right. Hopefully someone can help.

[FlameRetired]

Hi, jasonwebber

Welcome to the forum.

I don't understand the description, and cannot relate it to the workbook. Could you re-upload with desired results hand-typed in?

Thanks.

[jasonwebber]

Dave, the results are typed in, it's the J-N columns that should be the output. Columns C-H is the raw data that needs to be transformed, and the bolded cells are what I'd like to get put into a single row. Hope that helps, and thanks in advance for your advice.

[jaslake]

Hi Jason

Welcome to the Forum!!!

A VBA solution...try this Code in the attached...CTRL +x will fire the Code.

Option Explicit

Sub Test_Stages()
   Dim ws           As Worksheet
   Dim FR           As Long
   Dim LR           As Long
   Dim i            As Long
   Dim j            As Long

   Set ws = Sheets("Sheet1")

   Application.ScreenUpdating = False
   With ws
      FR = 5
      j = 5
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).Row
      For i = FR To LR + 1
         .Cells(FR - 1, FR + j).Value = j - 4 & "-Stage"
         j = j + 1
      Next i

      .Range(.Cells(FR, "F"), .Cells(LR, "F")).Copy
      .Cells(FR, "J").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                                   False, Transpose:=True
      .Cells(FR, "N").Value = .Cells(LR, "G").Value
   End With
   Application.CutCopyMode = False
   Application.ScreenUpdating = True

End Sub

[FlameRetired]

I am just guessing that the live data does not follow a pattern "Stage #" where it follows position and numeric patterns as in the upload. Is that correct?

Edit In the meantime this works with the data and layout provided. Try this formula in J5 filled / copied across to N5

=IFERROR(INDEX($F$5:$F$8,MATCH(SUBSTITUTE(TRIM(MID(SUBSTITUTE(J$4&"/"&J$4,"-",REPT(" ",256)),256,256)),"/"," "),$F$5:$F$8,0)),INDEX($G$5:$G$8,MATCH(SUBSTITUTE(TRIM(MID(SUBSTITUTE(J$4&"/"&J$4,"-",REPT(" ",256)),256,256)),"/"," "),$G$5:$G$8,0)))