How can I filter by term digits
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! |
1 Attachment(s)
Quote:
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 |
Quote:
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. |
Quote:
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. |
Quote:
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! |
All times are GMT +1. The time now is 02:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com