VBA Code to paste rows values as transpose to columns

[Monty9]

Hello,

I have this excel where I have multiple questions and 4 options in a column as below;

Question

Option 1

Option 2

Option 3

Option 4

I am looking for the code which can convert these Options from rows to transpose to columns against each question. Please note there are multiple questions and each question have always 4 options with a blank row at the end of each question/option set. Example is as below;

What is SAP S/4HANA EAM, and what is its purpose?​

A) It is a supply chain management tool.​

B) It is a project management tool.​

C) It is an enterprise asset management tool.​

D) It is a customer relationship management tool.​

Answer: C​

What is the difference between SAP EAM and SAP S/4HANA EAM?​

A) SAP EAM is an outdated version of SAP S/4HANA EAM.​

B) SAP S/4HANA EAM is a cloud-based application.​

C) SAP EAM is a standalone application, while SAP S/4HANA EAM is integrated with other modules.​

D) There is no difference between the two.​

Answer: C​

 

[steve the fish]

A formula can do that:

=IF(ISNUMBER(SEARCH("~?",A1)),INDEX(A1:A5,SEQUENCE(1,4,2,1)),"")

 

[Herakles]

Assuming that your data starts in A2 with a header in A1 and that you want the transposed data
to start on row 2 in any sheet:

=TRANSPOSE(OFFSET(ToTransposeData!$A$1,(((ROW()-1)*7)-7)+1,0,5,1))

where 'ToTransposeData' is the name of the worksheet containing the questions.

Copy this formula down for as many questions as you have.

If you want to include the answer then change the 5 to a 6.