Forum Discussion

guywe's avatar
guywe
Copper Contributor
Feb 28, 2022

Re-ordering spreadsheet

Hi, I'm new to the forum and hope this question makes sense. I have a spreadsheet with financial information that I use to prepare my tax return. The rows are different securities. The columns are di...
excel
Excel on Mac
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Feb 28, 2022

guywe Excel for the Mac, unfortunately, does not support the feature described by Martin_Weiss yet. But if you are an MS365 subscriber or have Excel 2021 you could use a LAMBDA function to unpivot the data for you. It's demonstrated in K3 in the attached file. I picked it up from PeterBartholomew1 no too long ago and tweaked it a little bit. Perhaps you can use it on your own data and create the reports you need from this tabular (unpivotted) data.

 

 

guywe's avatar
guywe
Copper Contributor
Feb 28, 2022
Hi Riny, thanks. While the output of your spreadsheet is precisely what I'm seeking to achieve, Lamda functions are beyond my current capabilities! I will start off trying to use the Power Query functionality which is indeed available to mean my Mac as a MS365 subscriber. Appreciate your help - thank you!
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 28, 2022

    guywe PQ is NOT available on the Mac to the extent that you need it. You can connect to a workbook or a TXT/CSV file and import them. That's it. No more PQ functionality, unless you dive into quite cumbersome VBA solutions. Nothing you would want to get involved with, I believe. And you don't really need to understand the LAMBDA. It works like a regular function. The only difference is that the "function" exists only in the workbook as a named formula.

     

    =UNPIVOT(data_range, column_header, row_header)

Resources