Macro to Organise some data, Transpose and grouping.

[bushidowarrior]

Hello all.

I have the table located below, but I have to organsise the data in to the format of Fig 2.

Fig 1. the data the is carried over is Column D, G and H



From this (Fig 1)

All the info below come from the above table, as you can see the data is grouped the number is column A.

So, group 1 has only 2 questions whereas group 3 has 3 questions.

I would like it so it can support up to 20 questions on 1 row. The above image should support at least 100 rows.



To this (Fig 2)

I think I have explained it well, but I am not sure.

Thank you all very much.

 

[Fluff]

How about

Sub RearrangeData()
   Dim Ary As Variant, Oary As Variant
   Dim r As Long, i As Long, c As Long
   
   With Sheets("Sheet1")
      Ary = .Range("D2", .Range("D" & Rows.Count).End(xlUp).Offset(, 4)).value2
   End With
   ReDim Oary(1 To UBound(Ary), 1 To 40)
   
   c = 1:   r = r + 1
   Oary(r, 1) = Ary(1, 1): Oary(r, 2) = Ary(1, 5): Oary(r, 3) = Ary(1, 4)
   For i = 2 To UBound(Ary)
      If Ary(i, 1) = Oary(r, 1) Then
         c = c + 2
         Oary(r, c + 1) = Ary(i, 5): Oary(r, c + 2) = Ary(i, 4)
      Else
         r = r + 1: c = 1
         Oary(r, c) = Ary(i, 1): Oary(r, c + 1) = Ary(i, 5): Oary(r, c + 2) = Ary(i, 4)
      End If
   Next i
   Sheets("sheet2").Range("A2").Resize(r, 40).Value = Oary
End Sub

 

[bushidowarrior]

Hello,

Fantastic... This is great!!!!!!!!!!!!!!!!!!!!!

Wow...Just a quick thing please... Can it carry over the points column.

I would really appreciate that!

 

[Fluff]

It already does. If I start with

Excel 2013/2016
	D	E	F	G	H
2	1			1	{1}{1}
3	1			1	{2}{2}
4	2			1	{3}{3}
5	2			1	{4}{4}
6	3			1	{5}{5}
7	3			1	{6}{6}
8	3			1	{7}{7}
9	4			1	{8}{8}
Sheet1

I end up with

Excel 2013/2016
	A	B	C	D	E	F	G
2	1	{1}{1}	1	{2}{2}	1
3	2	{3}{3}	1	{4}{4}	1
4	3	{5}{5}	1	{6}{6}	1	{7}{7}	1
5	4	{8}{8}	1
Sheet2

 

[bushidowarrior]

Indeed, you are correct. My mistake.

THANK YOU VERY MUCH!!!!!!!!!!!!!!!!!

It already does. If I start with

Excel 2013/2016

	D	E	F	G	H
1			1	{1}{1}
1			1	{2}{2}
2			1	{3}{3}
2			1	{4}{4}
3			1	{5}{5}
3			1	{6}{6}
3			1	{7}{7}
4			1	{8}{8}

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

</tbody>

Sheet1​

I end up with

Excel 2013/2016

	A	B	C	D	E	F	G
{1}{1}	{2}{2}
{3}{3}	{4}{4}
{5}{5}	{6}{6}	{7}{7}
{8}{8}

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

 

[Fluff]

You're welcome