Create a Horizontal format of Table from a Vertical oriented data table based on condition

Question

Hi All,

Based on the input file attached, can anyone help me to understand what would be the best approach to convert that entire dataset into a proper table format similar to the Output file attached.

The catch is, whether it is possible or not to demarcate/create a divider using Reporting tools in such a way that every tables are separated based on Partner names and financials. Having said that, the volume of the data can be dynamic, for ex. there can be more line items for "Ordinary business income (loss)" "Net rental real estate income (loss)" "Other net rental income (loss)" for Part 3 data.

Any help on this will be very appreciated.

Thanks,

db89

Output.xlsx

Input_File .xlsx

MeganBowers · Accepted Answer

Hi @db89,

This is possible using reporting tools, just a bit tedious to get it in the exact format you requested. I created a workflow to get you started that you can build upon (attached). The key parts of this solution are:

* cross tabbing data to get it in the right format (dealing with the multi-row data differently in the bottom half of the workflow--there's probably a more efficient way to do this, this is just what occurred to me)
* building tables for each piece with a header
* using the layout tool to position tables horizontally or vertically next to each other
* using a render tool to output, which will be in separate sections by "partner name" since that is always grouped on in the table tools

Workflow:

Output:

hopefully this is enough to get you started!