I need to run a countif across 520 separate workbooks that each have ~80,000 lines of data. I want to count each workbook individually in a master datasheet. Any tips on how to do this?
I need to run a countif across 520 separate workbooks that each have ~80,000 lines of data. I want to count each workbook individually in a master datasheet. Any tips on how to do this?
There is not much to say, you have to open every single folder, count the lines and write the value back to the master.
If every workbook is in one folder a macro like this could loop through every file in that folder and preform some action on it. Does every workbook contain only one sheet or are there several sheets in the workbook?
A macro like this will open every excel file in a folder:
But you must give more specific information, upload a small sample file and show what data should be extracted to master file if you wish for specific help.Please Login or Register to view this content.
Alf
Not exactly the answer for you,
but maybe you want to have a look the code below :
The code assumed :Please Login or Register to view this content.
- the 500 workbooks to be CountIf are in drive D, folder test ---> D:\test
- the range of the criteria to be CountIf from those 500 workbook is always the same, which is cell A2 to cell A500000
- the sheet name of that range is always the same, which is Sheet1
- the criteria to be CountIf is "blablabla"
- the active sheet when run the macro is completely blank
The result will be listed in the active sheet start from cell B2 telling something like this on each row :
workbook1.xlsx has blablabla 10 times
workbook2.xlsx has blablabla 6 times
workbook3.xlsx has blablabla 23 times
and so on.
Last edited by karmapala; 09-20-2021 at 07:00 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks