Convert data from columns to rows with M code

It would help if you attached a sample Excel workbook, so we can see exactly how your data is arranged.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon for attachments, as it doesn't work in this forum.

Hope this helps.

Pete

Attaching sample

You can put this formula in D3 of the Input Data sheet:

=IF(B3="","",IF(COUNTIF(B$3:B3,B3)=1,MAX(D$2:D2)+1,MAX(D$2:D2)&"_"&COUNTIF(B$3:B3,B3)))

and then copy down to the bottom of your data (or beyond, if you wish). In the other sheet you can use these formulae:

B3: =IFERROR(INDEX('Input Data'!B:B,MATCH(ROWS($1:1),'Input Data'!$D:$D,0)),"")

C3: =IF(B3="","","|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1),'Input Data'!$D:$D,0))&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_2",'Input Data'!$D:$D,0)),"")&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_3",'Input Data'!$D:$D,0)),"")&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_4",'Input Data'!$D:$D,0)),"")&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_5",'Input Data'!$D:$D,0)),""))

That might look a bit daunting, but it is more straightforward if I show it like this:

=IF(B3="","","|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1),'Input Data'!$D:$D,0))
&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_2",'Input Data'!$D:$D,0)),"")
&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_3",'Input Data'!$D:$D,0)),"")
&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_4",'Input Data'!$D:$D,0)),"")
&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_5",'Input Data'!$D:$D,0)),""))

This can accommodate up to 5 items joined together with the | character separating them, but it is quite easy to add more terms if required. If you had a later version of Excel, you could use the TEXTJOIN function to simplify this.

Copy both formulae down as far as you need them.

Hope this helps.

Pete

As a starter :

PHP Code: 

Sub Demo1()
    Dim V, R&
        V = [B2].CurrentRegion.Value2
    With CreateObject("Scripting.Dictionary")
        For R = 2 To UBound(V):  .Item(V(R, 1)) = .Item(V(R, 1)) & "|" & V(R, 2):  Next
        [B3:C3].Resize(.Count).Value2 = Application.Transpose(Array(.Keys, .Items))
        If .Count + 1 < UBound(V) Then Cells(3 + .Count, 2).Resize(UBound(V) - 1 - .Count, 2).Clear
       .RemoveAll
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

Marc L,
I cannot get this macro to run. I get a Type Mismatch error on this line:
For R = 2 To UBound(V): .Item(V(R, 1)) = .Item(V(R, 1)) & "|" & V(R, 2): Next

In the original thread, a Power Query was used and I was hoping for that approach: convert data from vertical to horizontal based on column a

In your original post, you asked to do this with a formula. I gave you a solution which actually uses 3 formulae - did you try it?

Pete

I cannot get this macro to run. I get a Type Mismatch error on this line:
For R = 2 To UBound(V): .Item(V(R, 1)) = .Item(V(R, 1)) & "|" & V(R, 2): Next

Originally Posted by JewelDarby

I get a Type Mismatch error

As no issue with your attachment on my side …

Alan,
This is oh so close. The sample file you returned did look right.

But when I paste my full table in, the delimiters don't seem to appear, just "test types" in various columns.
I did view your "how to use Mcode" so I could try again, but can't seem to make it work.

However, with what you supplied, maybe I can make it work if I noodle it.
Thank you SO MUCH for what you provided!

Let me know if you need the full table to work with.
Thank you again,
Jewel

VBA for your attachement.

Sub test()
    Dim a, i As Long, n As Long
    With Sheets("input data")
        a = .[b2].CurrentRegion.Value
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 2 To UBound(a, 1)
                If Not .exists(a(i, 1)) Then
                    .Item(a(i, 1)) = .Count + 2
                    a(.Count + 1, 1) = a(i, 1)
                    a(.Count + 1, 2) = "|" & a(i, 2)
                Else
                    a(.Item(a(i, 1)), 2) = a(.Item(a(i, 1)), 2) & "|" & a(i, 2)
                End If
            Next
            i = .Count + 1
        End With
        .[e2].Resize(i, UBound(a, 2)).Value = a
    End With
End Sub

This errors out here for me:

Subscript out of range
a = .[b2].CurrentRegion.Value

with the table selected or not

Impossible...

That worked perfectly.
I will review the code and try to learn.
Thank you SO MUCH.