combining time recorded for multiple tabs

[david gabra]

Hello
I have a problem due to the size of my file
I have more that 1m rows on different tabs.
I want to copy all to one tab - not possible and I do not know access.
I need the 'magical' skills of your vba scripts I believe. (I am not sure if formulae in excel is enough)
Let me explain.
I have 15 tabs with the time that was recorded by
different employees during a 2 year period.
I want to know by employee the total that they recorded per month form Oct-15 to June 16.

So I need a new tab that will have the employee names and columns for each month and total that they recorded to work, vacation, and holidays. - I need this to be automated since I have so many tabs and thousands of rows. - excel can't can't cope if I combine to one tab- and neither can I :-(.

I really need your help, otherwise I am totally stuck.

I have over 15 tabs - but for the example I have provided I have put 4 tabs to make it simple.

I need to be able to use your solution for 15 tabs and 5,000 employees.

I have attached excel, with the expected results for your automation.

I hope I explained clearly enough.

I have added the an example of how the new sheet should look.

Thank you in advance to who ever decides to take on this challenge.

 

[david gabra]

Hi
I am sorry I forgot to add that I need the summary by month to, and this is only for the hours field. I have added a updated file.
thank you so much.
David.

 

[dan_l]

If it's a one off thing, I highly recommend doing this with powerquery.

 

[david gabra]

I need to do this many time.
I have no idea about power query.
I need to be able to add other months, since I have only a small sample of months for the example.
Please can you send me an example in excel.
thank you for the time and effort.

 

[AlanSidman]

This is a fairly simple task with MS Access. Import all your tabs to one table in Access. Then run a summary Query to get the totals. To get a total by month, you will need to add a field to the table for each record indicating the date. I've provided a sample db to show you in the attached. To do this in Access took about five minutes and it was all manual.

Alternatively, If you are using Excel 2016, then use the Get and Transform Tab on the Data Ribbon. You can run queries to do exactly what you need to do.

https://support.office.com/en-us/ar...cel-2016-a8310388-2a12-438c-9d29-c6d29cb8df6a

 

[vletm]

david gabra
Something like this ...

Go to 'Summary'-sheet
and
press [Do Summary]-button.

Ps. 'Summary'-sheet should be 'Summary' not 'Summary.'!

 

[AlanSidman]

@vletm
When you ran your code, How long did it take to complete. I cancelled out after 6 minutes. With the amount of data the OP has indicated, I wonder how long this code will take to run. Interesting code, but with Access and no code, the information was available in seconds. Just my 2 cents and no disrespect for your code intended.

Alan

 

[vletm]

AlanSidman ... hmm ... I just checked and ...
it took ... oh! ... totally ... ONE SECOND !

As Chandoo.org - become awesome in Excel.

If I would try to do this with Access,
it would take few days and a lot of money!

Of course, there could be some 'sensitive' points ... like
1) if Your PivotTable do not have word 'Grand Total'.
2) Your Excel-version is not in that list or
if You're using 2016version and
its 'xlPivotTableVersion16' isn't as I have marked in my code!
> Did You check those points?

Would You check again,
how long time it really would take with Access?
From ... this moment ...
including importing tables and so on #5 Reply ...
until You would get same kind of results as OP wanted.
Really in seconds?
Clock is ticking...

 

[vletm]

david gabra, AlanSidman
I modified that code,
if use Excel2016 (?)-version then it uses the highest xlPivotTableversion and
there is now a check that, it moves max as many row data as original data!
No matter which language version You use ('Grand Total'-check).

... and still it takes 1 sec or less.

 

[AlanSidman]

david gabra, AlanSidman
I modified that code,
if use Excel2016 (?)-version then it uses the highest xlPivotTableversion and
there is now a check that, it moves max as many row data as original data!
No matter which language version You use ('Grand Total'-check).

... and still it takes 1 sec or less.

@vletm
Just ran your file and it is very fast. Did not bother to go back and run diagnostics as you suggested. Very nice and efficient.

BTW. It did take less than five minutes for me to import the four files and then build a query and run it in Access. Not saying it is a better solution, only that it was an alternative solution. I do recognize that Access has a steep learning curve and is not available to all. We all have our preferred ways of developing solutions to our problems.

 

[vletm]

AlanSidman - it's good to know, that it works with You too.
Maybe that 'xlPivotTableVersion' was incorrect; depends Your Excel-version
or You didn't pass that 'Grand Total'; language?
Less than 5 minutes, okay, with Access!
'My Access' isn't ready ... because ... no Access!
That's why it takes days ... and that's why Excel!
Things can do many ways

 

[david gabra]

It seems to work great. I am trying it now with thousands of files