As you haven't provided any specific information on where your data is in the various workbooks, or path names, I can only offer a generic approach - you will have to change addresses etc. to match your requirements.
This macro takes a list of filenames in Column A of the main workbook (your worksheet named 'list").
For each name it opens a workbook of the same name (followed by .xls - the macro adds the .xls).
On opening each workbook it filters data in column A of Worksheet "Sheet1", filtered for 'non-blanks'
It then copies the filtered cells
The copied cells are then transposed (column to row) and pasted alongside the original filename.
I suggest that you add the macro to a button on the worksheet containing the names.
Create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Get Data' or something suitable.
Right-click the button again and select View Code
In the code window that opens enter this:
Private Sub CommandButton1_Click()
Dim rngCell As Range
Dim rngStart As Range
Dim rngEnd As Range
Dim strPath As String
Dim strFilename As String
Dim strFilePath As String
Dim rngSourceEnd As Range
'set path name for all files - include closing "\"
strPath = "C:\temp\"
'set start of list of names in Column A - heading on row 1
Set rngStart = Range("A2")
'find end of list of names in column A
Set rngEnd = Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'loop through names in list
For Each rngCell In Range(rngStart, rngEnd)
strFilename = rngCell.Text & ".xls"
strFilePath = strPath & strFilename
'open the file
'filter on column A
.Columns("A:A").AutoFilter Field:=1, Criteria1:="<>"
'find end of data in column A
Set rngSourceEnd = .Range("A" & CStr(Application.Rows.Count)) _
'copy data in column A
.Range("A2:" & rngSourceEnd.Address) _
'paste data (transpose)
rngCell.Offset(0, 1).PasteSpecial xlPasteAll, Transpose:=True
'close file (dont save changes)
Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.
Click Save from the Visual Basic Menu.
Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).
As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'
Click the 'Get Data' button to run the macro
Note that all the named workbooks are closed after filter/copy but changes are not saved. This is 'by design' so that the workbooks do not open with a filter already in place.
Note also that re-running this macro will copy the changed/updated data - if there is less data than previous for any 'name' then the excess data will still be present on the relevant row.
If you use this macro, after changing it to match your data etc. please post it as a response and a data clear up can be proposed.