Reformatting or consolidating multiple worksheets for summary

Melimob

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


ITEMCLIENT ACLIENT B
RegionAPACAPAC
Phase11
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>
</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:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,122,952
Messages
5,599,033
Members
414,275
Latest member
Pungie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top