Transpose multiple columns to multiple rows with Stack

[Lina2019]

Dear Experts,

first of all, thank you to accept me and allow to me to post in this great forum

MY issue is about converting data to be a useful row data
everything is explained in the attached picture

I'm very normal Excel user

thank you for any help.

[Pete_UK]

Welcome to the forum.

It would help if you attached a sample Excel workbook, rather than a picture of one.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum,

Hope this helps.

Pete

[Lina2019]

Done

Excel file is attached now

[Pete_UK]

I copied your headings from M9:T9 across into W9:AD9 so that you can compare the results directly with the table you have presented. Then I used this formula in W10:

=INDEX(A:A,INT((ROWS($1:1)-1)/5)+3)&""

This can be copied across into X10:AB10, and then those formulae can be copied down as far as you need them. Then I used these formulae in the cells stated:

AC10: =INDEX($G$2:$K$2,MOD(ROWS($1:1)-1,5)+1)

AD10: =INDEX($G:$K,INT((ROWS($1:1)-1)/5)+3,MOD(ROWS($1:1)-1,5)+1)&""

Then you can copy those two formulae down, as required.

Hope this helps.

Pete

[BellyGas]

And remember to mention Pete in the bibliography and your graduation speech.

[Lina2019]

Million of thanks to you Pete … it's a magic

it's working wonderful

[Lina2019]

How to do that Belly ?

[Pete_UK]

Glad to help, Lina.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete