Forum Discussion

nailuenlue's avatar
nailuenlue
Copper Contributor
Feb 04, 2021

Dynamic transpose based on additional rows

Hi 🙂 

 

Due to the great support I got here, I would like to use your knowhow on the following problem..I would like to add additional columns based on the row entries in another column. The Screenshot should explain the problem.

 

excel
Formulas and Functions

3 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 04, 2021

    nailuenlue 

    You can make your headers dynamic by using

     

    =TRANSPOSE(SORT(UNIQUE(Table3[Role])))

     

    in D1

     

    Then, in D2 enter

     

    =SORT(FILTER(Table3[Position]:Table3[Position],Table3[Role]:Table3[Role]=D1))

     

     and copy/paste towards the right. Do NOT drag the formula to the right.

     

    Please find a mock-up attached.

    • nailuenlue's avatar
      nailuenlue
      Copper Contributor
      Feb 04, 2021

      Riny_van_Eekelen 

      Thanks a lot for the great example 🙂 Is there a way to make the D2-Part dynamic as well (without the need to copy paste the formula)? As the left list grows, the columns on the right are added and the references (positions) are listed below (the numbers in your example).

Resources