Re-ordering spreadsheet

[guywe]

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 different types of income (eg interest, dividends, capital gains, etc.). For each security, I enter appropriate data in the relevant columns. I'd like to create a view - or a report - that would show each security, with related income types and data underneath the security name. In essence, I'm trying to convert the column data into horizontal rows under the security name. Any ideas how I could accomplish this?

[#2]

Hi @guywe 

according to your description, this sounds like a perfect task for Power Query. You would need to load your table into Power Query and there is a transformation type called "unpivot columns".

In your example, you would select the column with the securities and then upply the transformation "Transform | Unpivot Columns | Unpivot other columns"

This will convert all the other columns into rows.

[#3]

@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.

[#4]

@Riny_van_Eekelen 

Hi Riny

If you are getting involved with Lambda functions, would you like to see some utilities I have experimented with?  One reads a data item from an arbitrary representation of a multidimensional array, using the Lambda function

   = DATAλ(iᵣ)

which takes as its argument an array of indices, iᵣ (in hindsight, I probably should have used separate parameters for each dimension but that would mean additional steps in the calculations).

To be able to read a value back from any layout of data is useful, but I have also written functions that generate row and column arrays of such indices to allow the data to be repivoted (or aggregated by addition) at will.  One option is to create a pivot-table normalised form of the users data.

I have made a start with putting the Lambda functions up on GitHub as Gists but the process is completely alien to me (I have received some advice but I don't think my advisor realises the level of profound ignorance I can bring to the table).

Multidimensional Array Representation (github.com)

I have also attached the workbook that demo's the functions, though I would not claim to have got to the point of the workbook being self-evident.

@Bill Jelen @HansVogelaar @SergeiBaklan @cuong 

[#5]

@PeterBartholomew1 Will study with great interest!!

[#6]

Thanks @Martin_Weiss . I think this approach should work, although still need to navigate my way through this! (I believe Power Query functionality is indeed available to me as a MS365 subscriber). Appreciate the guidance - thanks again!

[#7]

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!

[#8]

@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)