0

I have 10 Excel files(WorkBooks) with same header but different data. I want to search RLL in the Name column of all the files and need to count of all the 10 records after the filter.

This is a big task as I have to open each and every file and search RLL and then count and note it in a notepad/new excel sheet and doing it for remaining 9 is really frustrating.

So Is there any easy method inside Excel itself to do this task easily?

2 Answers 2

3

Assume u have 10 files D:\file1.xlsx to D:\file10.xlsx, and the column interested is A:A.

  1. Open file1.xlsx.
  2. create a new file, do:

 

in cell A1 put `=COUNTIF(INDIRECT("[file"&C1&".xlsx]Sheet1!$A:$A"),"RLL")`
in cell C1 put `=ROW()`

drag both downwards until row 10.

Then see the results.. only count in A1 should be available, and others should be should be #REF

To get the other value, just open the other file2-file10 at once the result will be there in A2:A10. Save it. and close all excel at once.

Idea : Use countif() to count. and play around with indirect() AND the its filename/list to 'point' to data source.

Please share if it works/understandable/not.

1
  • Dear @phidot Thanks for this. I tried this but =COUNTIF(INDIRECT("[file1.xlsx]Sheet1!$A:$A"),"RLL") this result is showing 0 data but file1 has197 data for RLL. I have opened file1, checked Sheet1 and A column only has data. Am i missing something here? please help me Aug 9, 2021 at 4:23
2

I like @p.phidot's approach, but it relies on the file names to be consistent. Another approach is to use Power Query to combine all files in the same folder regardless of file name. It should work well, since you said you have the same columns in each file.

Here's an example with three files in a folder. Each has a different name but similar data, all in Column A, with varying occurrences of "RLL". enter image description here

Here's what's in one of the files. enter image description here

In a new file, go to the Data tab on the ribbon, and choose Get Data --> From File --> From Folder. Then select your folder. enter image description here

On the Power Query popup, select Combine --> Combine and Load. enter image description here

Select the columns you want to search and click "OK". enter image description here

Then you can use a COUNTIF (or whatever suits you) to count the occurrences of "RLL". enter image description here

This is a quick and easy way to get what you need and demonstrate the concept, but play with the other options too. Power Query is great for gathering data from lots of similar spreadsheets from a folder. Any time you add more spreadsheets, you just have to hit "Refresh" and it'll update.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .