Greetings,
I have a list of subject I.D.s in Column L. Each subject I.D. as an individual score ranging from 1-5. I am attempting to use the Find and Select feature to locate specific I.D.s in column L, and when it does, to place the corresponding score in the adjacent column (M) in order to avoid inputting the scores manually. I have 41,000 rows of data to go through so I'm trying to be as efficient as possible.
Any suggestions?
jwalker
Last edited by jwalker52; 10-24-2011 at 11:42 PM.
Take a look at the VLOOKUP function.Replace the ID with the reference to the cell you want to evaluate. Put a 2 column table of ID's and scores somewhere in your book, and name it ScoreTable. IDs go in the first column, corresponding scores go in the second column.=VLOOKUP(L2,ScoreTable,2,FALSE)
Have I understood what you're trying to do correctly?
Last edited by tlafferty; 10-21-2011 at 06:57 PM. Reason: corrected typo... :)
If your question has been satisfactorily addressed, please consider marking it solved. Click here to see how.
Also, you might want to add to the user's reputation by clicking the scales icon - it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
jwalker52,
Where is the "corresponding" score coming from or how is it assigned/determined? Do you have a table that looks like:
SubID....Score
sID-1.....4
sID-2.....3
sID-3.....5
sID-4.....1
sID-5.....3
.
.
.
?
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Hello tigeravator,
Yes. I have a separate Excel worksheet that simple lists the I.D.s (of teachers in this case), each with a corresponding value. I've tried using the Find & Select button, select REPLACE from the drop down menu, onto the dialogue box. I typed in the I.D. number in the FIND box. Then typed in the corresponding value (1-5) in the REPLACE box. although, I don't want to replace the I.D. with the value. I want the value to appear in the next colomn (which is currently blank) next to all the respective I.Ds. For example, I.D. number 111396 has a corresponding value of [1]. The I.D. might be listed 81 times in all the data. For each time it is listed in Colume L, I want [1] to appear in the next cell over in Column M.
Muddled!
Hi Tom.
I think you may be on to the problem. I have a separate Excel worksheet that simply lists the I.D.s (of teachers in this case), each with a corresponding value. I've tried using the Find & Select button, select REPLACE from the drop down menu, onto the dialogue box where I type in the I.D. number in the FIND box. Then typed in the corresponding value (1-5) in the REPLACE box. I know this is not the correct approach as I do not want to replace the I.D. with the value. I want the value to appear in the next colomn (which is currently blank) next to all the respective I.Ds. For example, I.D. number 111396 has a corresponding value of [1]. The I.D. might be listed 81 times throughout the data base. For each time it is listed in Colume L, I want [1] to appear in the next cell over in Column M.
I hope this helps.
jwalker
VLOOKUP will definitely do this for you. If you attach a sample workbook, I'll show you. Click Go Advanced, then click the paperclip icon and browse to upload your file.
If your question has been satisfactorily addressed, please consider marking it solved. Click here to see how.
Also, you might want to add to the user's reputation by clicking the scales icon - it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
jwalker52,
It is highly recommended to upload the file for clarity purpose. What I perceive from your writing is : you want to add number adjacent to each IDS. I have no idea how different IDS you have in your 41k+ data. If they are small then:
copy column L into M, then select column M and run find replace dialogue.
ELSE
add IFs into this code to get output
Sub rePlace() Set l = Range("l1:l41000").Cells Set m = Range("m1:m41000").Cells For i = 1 To 41000 If l(i).Value = 111396 Then m(i).Value = "What ever you want to set" ElseIf l(i).Value = 111397 Then m(i).Value = "Same above" End If Next i End Sub
A brilliant simple solution. Thankyou. I'll give it a go.
Please mark it solved when done and may add to the user's reputation by clicking the scales icon..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks