Reformatting or consolidating multiple worksheets for summary


Active Member
Oct 16, 2011
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':

Incumbent PartnerCompany ACompany A
Partner Contact BM
Contact E-Mail
Client Contact
Contact E-Mail
Account mgr
Contact E-Mail
Receiving Partner
Partner BMN/AN/A
Contact E-Mail
Annual $ Spend$50,000$100,000
Annual Air Transactions500900
Annual Hotel Transactions22
Annual Car Transactions00
Annual Rail Transactions99
Total Transactions511911
Other Countries Serviced
Target Transition Date

<colgroup><col><col span="2"></colgroup><tbody>

I need to summarise by:
then by item as needed

Any advice on structure or what method to consolidate will be greatly appreciated.

Many thanks
Last edited:

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...