Transpose a table

Dear all,
currently I'm trying to transpose data from columns to rows. My problem is as follow: After every 40 rows in the original data sheet a new column has to be build:
Original structure of the table:

Group Question Answer

Group 1 Question 1 Answer 1
Group 1 Question 2 Answer 2
Group 1 Question 3 Answer 3
Group 1 Question 4 Answer 4
Group 1 ...
Group 1 Question 40 Answer 40
Group 2 Question 1 Answer 1
Group 2 Question 2 Answer 2
Group 2 Question 3 Answer 3
Group 2 Question 4 Answer 4
Group 2 ...
Group 2 Question 40 Answer 40

The structure I prefer and taht needs to be build just consists of one row for each of the groups.

Group 1 Answer 1 Answer 2 .... Answer 40
Group 2 Answer 2 Answer2 .... Answer 40

I would like to know, if it is possible to build a macro that creates a new row in the new data sheet after 40 rows in the original data sheet. Otherwise I have to transpose the table for each group which is impossoible since there are thousands of those groups.

Thanks a lot in advance!

I'm sorry. The data is confidental but the layout is like the one above. Did that become clear or do you have any questions about that.
Unfortunately, I don't really know how to solve the problem with formulas. Do you have a specific formula in mind?
Thanks a lot in advance!!

Thank you all a lot! You solved the problem. Sorry for thanking you so late. I finally applied your formula, Romperstomper. Until now, I didn't understand how it works, but since it does, everthing is fine Except for my curiosity of course... So thanks a lot again!