Pulling cell data from multiple sheets

I receive a spreadsheet on a monthly basis reporting the page & number of hits our Intranet site receives. I receive a second report containing filenames & number of downloads. Each file comes in csv format and I moved them into a consolidated workbook today.

I need to provide a summary of each page visited/file downloaded during each month (e.g. - Aug, Sep, Oct, etc.), as well as the number of hits/downloads for each month. One issue is that not every page receives a hit each month, so there may be blanks (or 0's) for any given month. Another issue I am having problems with is extracting the page name or file name from the path listed on each sheet.

The path/file name generally follows this format:
insite/enterprise_risk_management/business%20recovery/incident%20management/cbrp_incident_management. What I want/need to do is create a sorted column containing distinct text extracted from multiple sheets for the text that falls after the last '/' in each path/file name on the separate sheets.

Once I get that down, I already have the column for each month, but I want to pull the count that matching the source of the path/file name, which as I stated before, may not exist for any given month.

The spreadsheet I am working with is attached, and I have given up with my current approach. Any help would be GREATLY appreciated.

Hello Stkbm,

I have noticed in the attached file that the last “/” always comes after the world “ Recovery”, if this is the case then in D11 put this code

= SUBSTITUTE(A11,"Recovery","|",1) and drag it down as far as required

then highlight the range in column D copy—Paste special—values,

then

Data—text to columns—Delimited—Next and put | in the white box next to others--Next—Finish
Best regards

Malnahar,
Thanks! I was able to accomplish what I needed to with the solution you offered. Eventually, I want the people sending me the report to provide the information to me in the way I formatted it, but first-things-first.

Thanks again!