Forum Discussion

Mikedem007's avatar
Mikedem007
Copper Contributor
Apr 21, 2024

Tranpose Data

Please can anyone assist me on how I can easily transpose this data on the left A-C so it looks like the one on the right E-G, the idea is to have a simple order sheet for the client to add the quantity they want then transpose the data to look like (Column E-G)
Thank you!

  • Mikedem007 With Excel for MS365 or Excel for the web:

     

    =LET(
        names, B3:C3, codes, A4:A8, data, B4:C8,
        is_data, NOT(ISBLANK(data)),
        HSTACK(
            TOCOL(IFS(is_data, names), 2, TRUE),
            TOCOL(IFS(is_data, codes), 2, TRUE),
            TOCOL(data, 1, TRUE)
        )
    )

     

    Unpivot: Remove Blanks

  • djclements's avatar
    djclements
    Bronze Contributor

    Mikedem007 With Excel for MS365 or Excel for the web:

     

    =LET(
        names, B3:C3, codes, A4:A8, data, B4:C8,
        is_data, NOT(ISBLANK(data)),
        HSTACK(
            TOCOL(IFS(is_data, names), 2, TRUE),
            TOCOL(IFS(is_data, codes), 2, TRUE),
            TOCOL(data, 1, TRUE)
        )
    )

     

    Unpivot: Remove Blanks

  • Mikedem007 

    An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

     

    The data layout in the attached file is for illustration. You can place the green result table anywhere in the same worksheet or in another worksheet.

    • Mikedem007's avatar
      Mikedem007
      Copper Contributor

      OliverScheurich Dear Oliver

      Brilliant!! thank you I downloaded the attached example and it works perfect.

      Really appreciate your help

Resources