Get Data from other excell files

[ezeB1805]

I have 12 excel files that contain a column with dates. The dates occur multiple times. I need to count the number of occurances of each date on all 12 files. Is there a way to do this with a formula or array?

Example,

[TABLE="class: grid, width: 80, align: center"]
<tbody>[TR]
[TD]Date column on file 1[/TD]
[/TR]
[TR]
[TD]6-1-2015[/TD]
[/TR]
[TR]
[TD]6-1-2015[/TD]
[/TR]
[TR]
[TD]6-3-2015[/TD]
[/TR]
[TR]
[TD]6-5-2015[/TD]
[/TR]
[TR]
[TD]6-5-2016[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: grid, width: 80, align: center"]
<tbody>[TR]
[TD]Date column on file 2[/TD]
[/TR]
[TR]
[TD]6-1-2015[/TD]
[/TR]
[TR]
[TD]6-2-2015[/TD]
[/TR]
[TR]
[TD]6-3-2015[/TD]
[/TR]
[TR]
[TD]6-3-2015[/TD]
[/TR]
[TR]
[TD]6-5-2016
[/TD]
[/TR]
</tbody>[/TABLE]

Desired result on a new file:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]File 1[/TD]
[TD="align: center"]File 2[/TD]
[/TR]
[TR]
[TD="align: center"]6-1-2015[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6-2-2015[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6-3-2015[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]6-4-2015[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6-5-2015[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Chrisdontm]

What I would do is create a tab in my workbook that will correspond with each file...( 12-tabs )

Link them, (each particular worksheet), to be Imported as a Table into their respective sheets...

( Set it to automatically update as you open your workbook...) & ( Hide all of the Tabs, but don't lock them )

Then do a COUNTIF formula to count the number of times a date is in the column on their sheet...

If you would like details on all of this, let me know... ( Also, I use Excel 2013 )

 

[ezeB1805]

What I would do is create a tab in my workbook that will correspond with each file...( 12-tabs )

Link them, (each particular worksheet), to be Imported as a Table into their respective sheets...

( Set it to automatically update as you open your workbook...) & ( Hide all of the Tabs, but don't lock them )

Then do a COUNTIF formula to count the number of times a date is in the column on their sheet...

If you would like details on all of this, let me know... ( Also, I use Excel 2013 )

Hi Chrisdontm,

I actually found a ridiculously easy method. I opened both the source and target workbook and used COUNTIF on the target workbook. Then highlighted the desired column on the source workbook and excel (2013) did the rest. This is what the formula looked like.


=COUNTIF('[FILENAME.xlsx]WORKSHEETNAME'!$H$2:$H$4256,[@Date])