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

I truly appreciate your help and time, but that is so far over my head that I wouldn't know where to start.

Now that I've played with this a bit more, I know this is basic and I'm probably posting in the wrong formum. But I can't get things to work, and I don't know why not.


I have a spreadsheet with the file numbers. Btw, these are loan numbers rather than file or spreadsheets. The point is that I have my data, and I can apply the formula to obtain the last two digits of each. So now I have something like this....

Loan # Term Digits
7977634 34
16556300 00
7847344 44
8361829 29
7976682 82
8250334 34
8201514 14
8191585 85
8220618 18

Now I need to assign an employee based on the term digits. The if statement that I'm using is not working, and I can't even get a simple one to run. I'm trying this =IF(C2<50,Sheryl,IF(C2<99,Lisa)). And the results keeps coming up "false", so there is probably something very basic that I'm missing and I haven't a clue what. All I want is for it to tall me that if the term digit is between 00 and 49, the employee is Sheryl. If it's between 50 and 99, it's Lisa.

I think you should use this formula: =IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.

You can also use (if A2 is where the Loan # is):

=IF(Right(A2,2)*1<50,"Sheryl","Lisa")

The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's.

I need a formula just like this, however I need it for more people, for example:
Check digits 0-25 = Monica
Check digits 26-51 = Tom
Check digits 52-76 = Greg
Check digits 77-100 = Lucy

Is this possible, and can anyone please help? Thanks in advance!