Combine multiple files to one table

**koen.at.oversteek** · 12-21-2011, 06:37 AM

Dear Sirs,

I work for a public school. We have 60+ employees who can print and copy on two different machines. I need to get a yearly summary (with views per month) of the usage per employee.

Each month I get a xls dumps of each machine. On it is per employee a total number of prints, scans and copies. An employee can make use of either machine, so per month I have two xls files with per employee a different number. These numbers need to be SUM()-ed. The listed employees can vary however: due to pregnacies an employee may leave, while another may take their place.

example

copier 1, month 1:
Employee Copies Prints
x 1 2
y 0 0

copier 2, month 1:
Employee Copies Prints
x 0 2
y 1 0

copier 1, month 2:
Employee Copies Prints
x 4 2
y 0 3
z 0 0

copier 2, month 2:
Employee Copies Prints
x 0 8
y 1 0
z 1 1

etc

total number of prints/scans/copies this year:
Employee month1 month2 etc
x 5 14
y 1 4
z 0 2

How can this be done? I can put the monthly-files into tabs (into one file). I can import them into access and try doing it with SQL.

Thanks in advance for any help!

kind regards, Koen

**Fotis1991** · 12-21-2011, 06:56 AM

Ηι Koen and welcome to the forum

I think that you can do this, by SUMIF or SUMPRODUCT, but....

Be sure, that the best way to describe your problem, is to attach a sample workbook.

**koen.at.oversteek** · 12-21-2011, 08:07 AM

Hi Fotis1991,

I made a quick example: 2009-2010.xlsx

The multiple files have been put into one file, using multiple tabs. This file is for copier 1; assume a likewise file (in this case, the same) for copier 2. Tab 4 "2010 total" is what the outcome should be.

The tab names (or as files?) are always different, and the number of tabs per year could be any number from 1 to 12.

The actual totals in this example in tab "2010 total" are actually of only copier 1.

thanks, Koen

**Fotis1991** · 12-21-2011, 08:43 AM

Hi

I made an example for you using SUMPRODUCT function.

=SUMPRODUCT((('2010-04-29'!$A$2:$A$15=A2)*('2010-04-29'!$B$2:$B$15))+('2010-05-17'!$A$2:$A$15=A2)*('2010-05-17'!$B$2:$B$15)+('2010-07-02'!$A$2:$A$15=A2)*('2010-07-02'!$B$2:$B$15))

This gives you the result that you need.

Pls take a look to the attachment.

Hope to helps you

**jpr73** · 12-21-2011, 10:06 AM

Hi,
See attached a solution with pivot table
Best regards

**koen.at.oversteek** · 12-23-2011, 02:31 AM

Hi jpr73 and Fotis1991,

Thanks for your replies! I haven't got time at this moment to look into your solutions (were closing up for Christmas and it's chaos) but I'll do so asap. I'll be in touch!

kind regards, Koen

**koen.at.oversteek** · 01-19-2012, 06:30 AM

Hi jpr73 and Fotis1991,

It's been a while since I asked my question and finaly I'm able to take it up again. Again, thank you both very much for your time and your solutions. They are both very helpful. The solution of jpr73 actualy is exactly what I wished for! I've tried altering a few numbers in the tables (tabs), but the PT tab didn't refresh/update to the actual state. How can I achieve this? Right-click "refresh" (or renew in English?) seemed to delete the whole PT to replace it with a new defination. (For which I coulnd't find any of the fields used in the solution.)

Does anyone know how to refresh an existing pivot table, or, how to defined anew?

kind regards, Koen