Excel - Vlookup - multiple conditions - textual data

[rosieribbons]

Hi Everybody.

Was wondering if anyone knew how to do the following (apologies if my excel jargon is off, I'm not what you'd call a pro!)

OK so my situation is this. I'm working with a database that holds information on individuals backgrounds. At the moment I'm looking at education history. I have pulled off a list which details what the database currently says each individual has specified as their degree, but they are all worded slightly differently, and I need to introduce some consistency into the wording to allow us to search and sort the data better.
So I've got sheet1, which has a list of all the unique ways a degree is listed within the database, and an adjacent column which specifies what I want to change it to.
And in sheet2 I have a list of serial numbers for the individual records, and adjacent to that, a column detailing the unique degree listings.

So I need to find a way to tell excel "when you find X in column 2 of sheet 2, change it to Y from sheet 1 column 2, but if you don't find X don't do anything. And if you find Z in column 2 of sheet 2, change it to F from sheet 1 column 2, but if you don't find Z don't do anything".....

BUT I have about 2,500 different conditions that I need to apply at the same time.

Does that make sense? and can anyone suggest a way to achieve this? a friend suggested Vlookup but I can't seem to find any evidence that Vlookup can apply such a large volume of conditions simultaneously. Also all the data I'm working with is textual rather than numerical and I can't find much on how to make that work. I have 20,000 records to amend, I really don't want to do them one at a time!

thanks,

Rosie

 

[Robert Mika]

Hallo and welcome.
Could you post a sample of your data?
You can download and install two of the following programs:
HTLMaker
or
Excel Jeanie

or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[rosieribbons]

I wish I could.... however our data policy is very strict- I'm not allowed to post anything anywhere, even if it's meaningless to 'the outside world'!

 

[Robert Mika]

Any fake data with your desired result?

 

[rosieribbons]

Serial	Current Course Name	New Course Name
112233	English And Criminology	Criminology & English
454487	English And Criminology	Criminology & English
659855	English And Criminology	Criminology & English
323136	English And Criminology	Criminology & English
495782	English, Criminology	Criminology & English
363599	English And Criminology	Criminology & English
121266	English And Criminology	Criminology & English
558897	Criminology and English	Criminology & English
223344	Management, Business Admin	Business Administration & Management
656688	Management and business Administration	Business Administration & Management
331332	BA&M	Business Administration & Management
569874	BA Management and business admin	Business Administration & Management
653121	Management, Business Admin	Business Administration & Management
554599	Business Administration and management	Business Administration & Management

<colgroup><col><col><col></colgroup><tbody>
</tbody>

	<tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[rosieribbons]

that's what I want to end up with.... except that list would be about 20,000 lines long...

 

[Laurent C]

From your example, there is only one condition, if the course name can be found in the lookup table, then it be used. If I understood this correctly, something like the following should do the trick:
= IFERROR(VLOOKUP(CurrentCourseNameColumn, YourLookupTableRange, 2, FALSE), CurrentCourseNameColumn))

Do you have examples of what the other "2500 conditions" would look like?

 

[rosieribbons]

Computer Science & Music	Computer Science & Music
Computer Science & Music Technology	Computer Science & Music Technology
Computer Science & Neuroscience	Computer Science & Neuroscience
Computer Science & Philosophy	Computer Science & Philosophy
Computer Science AND Physical Geography	Computer Science & Physical Geography
Computer Science & Physics	Computer Science & Physics
Computer Science & Psychology	Computer Science & Psychology
Computer Science & Sociology	Computer Science & Sociology
Computer Science & Statistics	Computer Science & Statistics
Computer Science and Visual Arts	Computer Science & Visual Arts
Computer Science with Astrophysics	Computer Science with Astrophysics
Computer Science with Forensic Science	Computer Science with Forensic Science
Computer Science with Mathematics	Computer Science with Mathematics
Computer Science with Physical Geography	Computer Science with Physical Geography
Computer Science with Physics	Computer Science with Physics
Computer Science with Psychology	Computer Science with Psychology
Computer Science, and Business Administration & Music Technology	Computer Science, Business Administration & Music Technology
Computer Science, Electronics & Physics	Computer Science, Electronics & Physics
Computer Science, Mathematics & Certificate in Education	Computer Science, Mathematics & Certificate in Education
Computer Studies, Physics & Certificate in Education	Computer Science, Physics & Certificate in Education
Computer Studies	Computer Studies

<colgroup><col span="2"></colgroup><tbody>
</tbody>

maybe i used the term 'condition' incorrectly... I meant 2,500 unique original course names with new names to change to.

the other thing I'm wondering is if I have course names like 'Computer Studies' and course names that incorporate that within a longer name, e.g. Computer Studies, Physics Certificate in Education will one overwrite the other? I need it to recognise the whole cell exactly.

 

[Laurent C]

VLOOKUP should be able to handle a table of this size. Used with the parameter FALSE, this function will give you an exact match.

 

[rosieribbons]

thanks for your help! I'm gonna give it a whirl now...wish me luck!