Excel 2007 : Find & Select Help

[jwalker52]

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

[tlafferty]

Take a look at the VLOOKUP function.

Please Login or Register  to view this content.

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.

Have I understood what you're trying to do correctly?

[tigeravatar]

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
.
.
.

?

[jwalker52]

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!

[jwalker52]

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

[tlafferty]

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.

[nlm]

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

Please Login or Register  to view this content.

[jwalker52]

A brilliant simple solution. Thankyou. I'll give it a go.

[nlm]

Please mark it solved when done and may add to the user's reputation by clicking the scales icon..