A button to transpose data into a destination table

Hi, I have a table filled with data that I need to be pasted into a final blank table but the destination table is larger than the original table. Also the data needs to be sorted by one column and t...

excel

Macros and VBA

SergeiBaklan

Oct 21, 2025

In general that could be formula if locations of Source and Destination are predefined

=LET(
   source,  TRIMRANGE($A$4:$C$10000),
   IDs,     SORT( TRIMRANGE($E$4:$E$10000) ),
   headers, CHOOSECOLS($A$3:$C$3, {1,3,2} ),
   VSTACK(
       headers,
       HSTACK(
          IDs,
          XLOOKUP(IDs, CHOOSECOLS(source,1), CHOOSECOLS(source,3), ""),
          XLOOKUP(IDs, CHOOSECOLS(source,1), CHOOSECOLS(source,2), "")
      )
  )
)

A button to transpose data into a destination table.xlsx11 KB

OliverScheurich

Gold Contributor

Oct 21, 2025

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 screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.