How to convert table from one form to other form?

[SarahDetroja]

Hello Fellow Experts,

Please see attached image. I have a table in the form of "Source" format. I want to convert that table in the form of "Output" format. What is the best way to do that?

Many thanks in advance for your kind help.

Regards
Sarah.

 

[SarahDetroja]

Hi Peter,

Everything works fine. Just one small thing. I can do the following manually as you suggested in above Macro
.Rows(0).Value = Array("First Name", "Last Name", .....) 'Add other headers here

However, sometimes there could be 20 or 30.

Can we do something about it?

---------------------------- SOURCE----------------------
So we have 10 columns with the following text.

What is your favorite color?
Which European country you like?
Which city do you like?
Which is your food?
Which is your favorite pet?
Which is your favorite bird?
Which are your favourite sports?
Which is your favourite subject?
Which is your favourite day?
Which is your favourite festival?

---------------------------- OUTPUT----------------------
"What is your favorite color?", "Which European country do you like?", ....."Which is your favourite festival?"

(So concatenating 10 columns)
So I can use it in the following
.Rows(0).Value = Array("First Name", "Last Name", .....) 'Add other headers here

Is there a formula or can be included in the above Macro? I have tried a few things e.g. textjoin but it's not available in the 2013 version.

Note: No. of columns can be changed from 10 to 30 OR 10 to 3.


Thank you once again.

 

[Peter_SSs]

Can we do something about it?

Give this version a try.

Sub Rearrange_v3()
  Dim a As Variant, b As Variant, FCols As Variant
  Dim i As Long, j As Long, k As Long, UBFC As Long
  
  Const NumCols As Long = 6               'Total number of data columns
  Const FixedCols As String = "1 2 5 6"   'Columns A, B, E F (Non Question/Answer columns)
  Const QnCol As Long = 3                 'Column C (Question column)
  Const AnswerCol As Long = 4             'Column D (Answer column)
  Const NumQns As Long = 10               'Number of questions
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, NumCols).Value
  FCols = Split(FixedCols)
  UBFC = UBound(FCols)
  ReDim b(1 To UBound(a) / NumQns, 0 To UBFC + 1 + NumQns)
  For i = 2 To UBound(a) Step NumQns
    k = k + 1
    For j = 0 To UBFC
      b(k, j) = a(i, FCols(j))
    Next j
    For j = UBFC + 1 To UBFC + NumQns
      b(k, j) = a(i + j - UBFC - 1, AnswerCol)
    Next j
  Next i
  With Range("Z1")                        '<- Z1 is top-left cell of results area
    .Offset(1).Resize(k, UBound(b, 2)).Value = b
    For j = 0 To UBFC
      .Offset(0, j).Value = a(1, FCols(j))
    Next j
    For i = 1 To NumQns
      .Offset(0, UBFC + i).Value = a(1 + i, QnCol)
    Next i
    .CurrentRegion.Columns.AutoFit
  End With
End Sub

 

[SarahDetroja]

Give this version a try.

Sub Rearrange_v3()
  Dim a As Variant, b As Variant, FCols As Variant
  Dim i As Long, j As Long, k As Long, UBFC As Long
 
  Const NumCols As Long = 6               'Total number of data columns
  Const FixedCols As String = "1 2 5 6"   'Columns A, B, E F (Non Question/Answer columns)
  Const QnCol As Long = 3                 'Column C (Question column)
  Const AnswerCol As Long = 4             'Column D (Answer column)
  Const NumQns As Long = 10               'Number of questions
 
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, NumCols).Value
  FCols = Split(FixedCols)
  UBFC = UBound(FCols)
  ReDim b(1 To UBound(a) / NumQns, 0 To UBFC + 1 + NumQns)
  For i = 2 To UBound(a) Step NumQns
    k = k + 1
    For j = 0 To UBFC
      b(k, j) = a(i, FCols(j))
    Next j
    For j = UBFC + 1 To UBFC + NumQns
      b(k, j) = a(i + j - UBFC - 1, AnswerCol)
    Next j
  Next i
  With Range("Z1")                        '<- Z1 is top-left cell of results area
    .Offset(1).Resize(k, UBound(b, 2)).Value = b
    For j = 0 To UBFC
      .Offset(0, j).Value = a(1, FCols(j))
    Next j
    For i = 1 To NumQns
      .Offset(0, UBFC + i).Value = a(1 + i, QnCol)
    Next i
    .CurrentRegion.Columns.AutoFit
  End With
End Sub

Hi Peter,

Stunning! Simply superb. We know how the software works but people who don't know the intricacy would just see it as Magic!

Again many thanks for your wonderful work!

Stay blessed!

Regards
Sarah

 

[Peter_SSs]

Glad it worked for you. Thanks for the follow-up.