Consolidation

Resolved · Medium Priority · Version 2010

Mon 13 Nov 2017, 12:37 Delegate Mark said...

Mark has attended:
Excel Intermediate course

Consolidation

Good afternoon.

I need to consolidate our monthly payroll downloads. The information includes both text, dates and numbers. I can consolidate the numbers ie salary, tax and deductions etc but I cannot seem to get the text data or dates to work ie department names and costs centres / dob. My idea is to consolidate the 12 payrolls for our accounting year and use a pivot table to manipulate the information but I cannot do this without the departments and cost centre names. Can you explain how I can resolve this.

Await your news.

Mark

Mon 13 Nov 2017, 13:46 Trainer Marius said...

RE: Consolidation

Hi Mark,

Thank you for your question. The Consolidate tool is very convenient for numbers but its shortfall is that it can't consolidate other data types. You could create the other labels manually and then use Consolidate just selecting the numbers to create a consolidation.

Alternatively, you could build 3D formulas to consolidate the figures. You may have to use Sumif to add numbers according to specific labels.

You're welcome to send some sample data to forum@stl-training.co.uk so that we can have a better idea of your tables.

Kind regards
Marius Barnard
STL

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Apply currency format quickly in Excel

To quickly apply the currency format to cell in your spreadsheet, select (highlight) the cells you wish to apply currency format to, then use Ctrl + Shift + $

This will apply a pounds symbol even though the $ key is pressed.

View all Excel hints and tips