Match and sort file names according to ids and name

Question

I need your help in VBA solution and I'm using 2003, hope you can help.

I have a sheet called "ProfileList" that contains a list of student IDs, names and file names.

In this sheet, I have a feature called 'List Files' to list all the file names contained in the main folder where the folder path is obtained from the user input. So far, I manage to list all the file names from the folders (including subfolders).

However, I couldn't manage to sort the file names according to the ID and name of a student. So, is there anyway I can achieve this? In addition to that, the folder may not contain all the student files, thus I would like to know if there is any missing file in the folder (by leaving the row blank and highlighted in grey).

I've attached an image for you to refer. The image shows how it should look like.

Answer

As you loop through your list of files, you will need to match them to the entry already there

I don't know how you are getting your file names, so I will just assume they are in an array called flist

here is a code snippit that represents looping through the list of file names then for each name looping through the cells of student data trying to identify a match. Incorporate something like that in your code.

Dim r as Range, i as long, s as String , s1 as String

with worksheets("ProfileList")

 set r = .Range("B12",.Range("B12").End(xldown))

End with

for i = lbound(fList) to Ubound(flist)
   s = flist(i)
   for each cell in r
     s1 = "(" & cell.Value & ") " & cell.offset(0,1) & ".xls"    
     if lcase(s) = lcase(s1) then  ' lcase makes the compare case insensitive
       cell.offset(0,2).Value =  s
        exit for
     end if
  Next
Next

Hope that provides you the information you need.