Copy multi data (range) from a set of excel files inside a folde

[Closed]
Report
-
Hello,

It's my first post in this forum after a lot of visits that i`ve done in the past and really helped me a lot.
Well the situation is this: i have a set of around 50 files (more less) and there are all in the same format (i have created them). There are answers from a research that i did (with names and values 0,1 or 2). Like the following:
B5 = Question 1
B6 = Question 2
B7 = Question 3
etc
to Question 30
from D4 to N4 horizontaly there are names like (Thomson, James, Howard...etc ) where are corresponded values like 0 or 1 or 2 per name and question. The only difference between these 50 files is that not always each cell is fullfiled from (d4 to N4 with names) . Sometimes are less than 11 names and left it blank the coressponded cell but the format is the same for all files.
What i want to do is little complicated. I want to take each response from FILE1,FILE2...to FILE50 and copy the values into another file or sheet. In the new file i want tobe organized the answers in lists per answer and per question.

For example if someone responded
D5=0 Thomson responded 0,
E5=0 James responded 0
F5=2 Howard responded 2 in File1,

and in File2
D5=0 Brandley responded 0,
E5=1 Clarck responded 1
F5=2 Nickolson responded 2

I would like to create a list like the following anywhere in anew sheet or file:
Question1
(answered 0 )

Thomson
James
Brandley

Question1
(Answered 1)
Clarck

Question1
(Answered 2 )
Howard
Nickolson
from names that responded 0 from all 50 files for question1, another list for all who answered 1 from all files for question1 and another for all who answered 2 for question1 in all 50 files.
These lists should be created for every question from Question1 to Question30.
I hope i explain well what i would like to do and i hope even more and i would appreciate any suggestion and advice from anyone for this complicated work.