100 consolidating
03-May-2020 18:36
If you are looking for a quick way to match and merge data from two worksheets, then you can either employ the Excel VLOOKUP function or embrace the Merge Tables Wizard.
The latter is a visual user-friendly tool that lets you compare two Excel spreadsheets by a common column(s) and pull matching data from the lookup table.
In this situation, when producing a consolidated report, it is important to only account for the percentage of ownership you own.
Management Reporter uses the Rollup % in the Reporting Tree Definition, and setting this up is outlined in this blog post (the last in a series of blogs on consolidations).
In the following example, at the consolidated level for Sales, you can see only 80% shows.
If you own less than 1% of a company, select “Allow rollup less than 1%” in the Additional Options tab of the Report Settings form, and then the rollup % in the reporting tree will be treated as less than 1%.
At that, it's rather complex and requires a long learning curve.
As you see in the screenshot below, the three worksheets to be consolidated have a similar data structure, but different numbers of rows and columns: To consolidate the data in a single worksheet, perform the following steps: As you see, the Excel Consolidate feature is very helpful to pull together data from several worksheets. In particular, it works for numeric values only and it always summarizes those numbers in one way or another (sum, count, average, etc.) If you want to merge sheets in Excel by copying their data, the consolidation option is not the way to go.
For example, if the setting is selected, then .8 would .8%, not 80%.
