Transpose or Transfer from horizontal data to vertical

Sirs/Ma'ams,

I have wide range of data arrange horizontally like;


Date Amount Date Amount Date Amount Date Amount Date Amount Date Amount Date Amount Date Amount


The above is consist of 16 columns, where in every date has a corresponding amount.. My question is, would it be possible to transpose or transfer it vertically (with only 2 columns "Date and Amount")

Please see attached for easy reference.

Thank you in advance

Do you still have Office 2019 or something newer?

Good - please update your forum profile NOW.

Try this:

=LET(d,TOCOL(TRANSPOSE(FILTER(B4:Q5,B3:Q3="Date")),3),a,TOCOL(TRANSPOSE(FILTER(B4:Q5,B3:Q3="Amount")),3),SORT(CHOOSE({1,2},d,a),,1,0))

This uses the new TOCOL function, for which at the moment you need to be a beta channel subscriber, so you may need to change your channel.

In Excel, go to File | Account and then adjust your settings as indicated in the screenshot below. You'll then need to force Office Updates to bring through the new functions.

Glad to have helped - thanks for the rep.