Hi All
I have a workbook where each tab represents a country. Within that, it shows the same functions and items per client.
I need to summarise the data across all sheets to extract key pieces of information without having to reformat the sheets. I inherited the workbook so don't want to re-invent the wheel.
The way it is formatted at the moment it's difficult to pivot on due to the way it has been structured. I have looked at Get & Transform tutorials but again, can't see how I can relate and condense all info the way I need it.
Here's an example of how it is set out -starting from column B. Column A I haven't included but there's a merged cell to catagorize the function. All the below items fall under a header called 'General Information':
<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
I need to summarise by:
Region
Country
Client
then by item as needed
Any advice on structure or what method to consolidate will be greatly appreciated.
Many thanks
I have a workbook where each tab represents a country. Within that, it shows the same functions and items per client.
I need to summarise the data across all sheets to extract key pieces of information without having to reformat the sheets. I inherited the workbook so don't want to re-invent the wheel.
The way it is formatted at the moment it's difficult to pivot on due to the way it has been structured. I have looked at Get & Transform tutorials but again, can't see how I can relate and condense all info the way I need it.
Here's an example of how it is set out -starting from column B. Column A I haven't included but there's a merged cell to catagorize the function. All the below items fall under a header called 'General Information':
ITEM | CLIENT A | CLIENT B |
Region | APAC | APAC |
Phase | 1 | 1 |
Incumbent Partner | Company A | Company A |
Partner Contact BM | ||
Contact E-Mail | ||
Client Contact | ||
Contact E-Mail | ||
Account mgr | ||
Contact E-Mail | ||
Receiving Partner | ||
Partner BM | N/A | N/A |
Contact E-Mail | ||
Annual $ Spend | $50,000 | $100,000 |
Annual Air Transactions | 500 | 900 |
Annual Hotel Transactions | 2 | 2 |
Annual Car Transactions | 0 | 0 |
Annual Rail Transactions | 9 | 9 |
Total Transactions | 511 | 911 |
Other Countries Serviced | ||
Target Transition Date |
<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
I need to summarise by:
Region
Country
Client
then by item as needed
Any advice on structure or what method to consolidate will be greatly appreciated.
Many thanks
Last edited: