Quote:
Originally Posted by Dyan
I could really use some help with an excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.
So for example, I have the following list of files:
1002856101
22781721
1044863815
1008799064
1044779765
1006511115
1007641804
0729939256
5303486020
8364709
0014094759
0019921519
8172717
Employee A works all files ending 00 - 49, and employee B works all files ending 50 - 99. Is there a formula that I can run that would identify which files belong to which employee?
Btw, most of our file numbers are 10 digits long. But that's not always the case. I don't know if that makes a difference, but I wanted to mention it.
Thanks!
|
Attached is the spreadsheet fetches all the files from the particular folder.
After fetching the file names into Column C, using right, left, choose and logical functions
Created and edited by Bala Sesharao
Option Explicit
Sub GetFileNames()
Dim x_coordinate As Long
Dim x_directory, x_Filename$, InitialFoldr$
InitialFoldr$ = "G:\" '<<< Startup folder to begin searching from
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count < 0 Then
x_directory = .SelectedItems(1) & "\"
x_Filename$ = Dir(x_directory, 7)
Do While x_Filename$ < ""
ActiveCell.Offset(x_coordinate) = x_Filename$
x_coordinate = x_coordinate + 1
x_Filename$ = Dir
Loop
End If
End With
End Sub