Consolidation… but not really

[adamlscott]

So this is what I’m trying to figure out how to do using either a macro or a pivot table (or anything else that will do the job).

I am using Excel to do some billing for our company (I know there is software designed to do this but we are a non-profit and can’t afford it, ha ha ha) and am trying to consolidate several workbooks into one. I use the term consolidate loosely because the consolidation feature in Excel is clearly not the function that I am looking for.

Here is the example of how the data looks:

WORKBOOK 1
Client Name Job Code Total Minutes
John X1 35
Sally X3 14
Mitch X2 24
Lisa X2 6
Jill X2 23

WORKBOOK 2
Client Name Job Code Total Minutes
Mitch X5 7
Mitch X4 80
Sally X2 7
Jill X1 4
John X3 45

WORKBOOK 3
Client Name Job Code Total Minutes
Lisa X2 78
Sally X4 4
John X1 65
Sally X5 37

And what I want to get out of these multiple workbooks is a summary for a particular client. Example:

WORKBOOK 4
Client X1 X2 X3 X4 X5
Mitch 24 80 7

Or

WORKBOOK 4
Client Name Job Code Total Minutes
Mitch X2 24
Mitch X5 7
Mitch X4 80

I’d even settle for

WORKBOOK 4
Client Name Job Code Total Minutes
John X1 35
Sally X3 14
Mitch X2 24
Lisa X2 6
Jill X2 23
Mitch X5 7
Mitch X4 80
Sally X2 7
Jill X1 4
John X3 45
Lisa X2 78
Sally X4 4
John X1 65
Sally X5 37

So yeah… any help would be grateful.

[antoka05]

In the attached file I added a macro (Macro1) in the sheet 'Sheet4'.

I hope it's what you need.

Regards,
Antonio