VBA Transpose and Join Help

[NeoSez]

Data is being pulled from SHEET1 and transposed and joined on SHEET2.
Problem: Sheet1's column "E" is supposed to be at the top header row on SHEET2, but it is not working.

Sub ReArrange()

  Dim a As Variant, b As Variant, cr As Variant

  Dim d As Object

  Dim i As Long, lr As Long

 

  Set d = CreateObject("Scripting.Dictionary")

  d.CompareMode = 1

  With Sheets("Sheet1")

    lr = .Range("B" & Rows.Count).End(xlUp).Row

    a = Application.Index(.Cells, Evaluate("row(2:" & lr & ")"), Array(1, 5, 3, 2))

 

  End With

  ReDim b(1 To UBound(a), 1 To UBound(a))

  For i = 1 To UBound(a)

    If Not d.exists(a(i, 3)) Then d(a(i, 3)) = d.Count + 1 & " 1"

    cr = Split(d(a(i, 3)))

    b(cr(1), cr(0)) = Join(Application.Index(a, i, Array(1, 2, 3, 4)), vbLf)

    d(a(i, 3)) = cr(0) & " " & cr(1) + 1

 

  Next i

  With Sheets("Sheet2")

    With .Range("A2").Resize(UBound(a), d.Count)

      .WrapText = True

      .Value = b

      .Rows(0).Value = d.Keys

    End With

  End With

End Sub

 

[pike]

Hi,
you can move it to the top in the index column array

b(cr(1), cr(0)) = Join(Application.Index(a, i, Array(2, 1, 3, 4)), vbLf)

 

[NeoSez]

Hi,
you can move it to the top in the index column array

b(cr(1), cr(0)) = Join(Application.Index(a, i, Array(2, 1, 3, 4)), vbLf)

Thanks for your suggestion. I had tried that already and it didn't make a difference.
Maybe I should provide more context. The header row example is like blue, green, red. Anything, that is under the blue category gets listed under blue, etcetera. All the listing underneath the header row is listing it downwards in the column, rather than across the columns under the correct header.

 

[NeoSez]

Thanks for your suggestion. I had tried that already and it didn't make a difference.
Maybe I should provide more context. The header row example is like blue, green, red. Anything, that is under the blue category gets listed under blue, etcetera. All the listing underneath the header row is listing it downwards in the column, rather than across the columns under the correct header.

I resolved the issue now.

 

[pike]

Hi ,
Just tested some dummy data and it moved column e (sheet1) to column 2 in array a
Then to the top of transposed array b to sheet2 ??
better upload images of before and after worksheets as i don't understand what you are trying to accomplish.

 

[Peter_SSs]

I resolved the issue now.

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.