I have a training spreadsheet that has courses and names. We are importing
to a new training system and need to put new codes with the old course names.
There are about 11,000 lines and it is in Excel 2007. The first sheet has
all of the old information, person's name, date of training, and old course
number or name. The second sheet has the course info, old and new numbers.
Is there a macro that will start at the second line (headers on first line)
take the old course number from the first sheet, match it with the old number
on the second sheet, fill in the new number in a blank column on the first
sheet. then move to the next line and repeat until it is finished. I can use
paste special if there is a way to display the correct info from the second
sheet with an equation in each cell of the currently blank column.

Thanks in advanced for the help.
--
Tom

Can I use lookup?
--
Tom


"Tom" wrote:

I have a training spreadsheet that has courses and names. We are importing
to a new training system and need to put new codes with the old course names.
There are about 11,000 lines and it is in Excel 2007. The first sheet has
all of the old information, person's name, date of training, and old course
number or name. The second sheet has the course info, old and new numbers.
Is there a macro that will start at the second line (headers on first line)
take the old course number from the first sheet, match it with the old number
on the second sheet, fill in the new number in a blank column on the first
sheet. then move to the next line and repeat until it is finished. I can use
paste special if there is a way to display the correct info from the second
sheet with an equation in each cell of the currently blank column.

Thanks in advanced for the help.
--
Tom

Tom

I think the best way to do this is to use vlookup. You need to make sure
that old number is the leftmost column in the lookup array. then you put this
into sheet 1 where you want to new number to be placed:

=VLOOKUP(Sheet1!<old number cell, Sheet!2<array with old and new numbers,
FALSE)

The FALSE argument ensures that only exact matches are found so you DON'T
need to sort your old numbers in ascending order.

If this helps, please click "Yes"
<<<<<<<<<<<


"Tom" wrote:

Can I use lookup?
--
Tom


"Tom" wrote:

I have a training spreadsheet that has courses and names. We are importing
to a new training system and need to put new codes with the old course names.
There are about 11,000 lines and it is in Excel 2007. The first sheet has
all of the old information, person's name, date of training, and old course
number or name. The second sheet has the course info, old and new numbers.
Is there a macro that will start at the second line (headers on first line)
take the old course number from the first sheet, match it with the old number
on the second sheet, fill in the new number in a blank column on the first
sheet. then move to the next line and repeat until it is finished. I can use
paste special if there is a way to display the correct info from the second
sheet with an equation in each cell of the currently blank column.

Thanks in advanced for the help.
--
Tom

Using Vlookup is probably the fastest way to get the job done

Let's assume Sheet 1 has the 'old' number in Cell A2 and the old (resp. new) numbers are on Sheet2, in B2 (resp. c2) then the formula in cell B2 (sheet 1) will be :

=Vlookup(a2;Sheet2!B:C;2;false)

A2 = reference of value to look for
Sheet2!B:C = tells Excel in which columns the 'Old' and 'New' values are
2 = will return whatever is in the 2nd column (here 'C')
False = Will only return full matches (even slight differences will return an error)



Tom wrote:

Get info from second sheet
09-Nov-09

I have a training spreadsheet that has courses and names. We are importing
to a new training system and need to put new codes with the old course names.
There are about 11,000 lines and it is in Excel 2007. The first sheet has
all of the old information, person's name, date of training, and old course
number or name. The second sheet has the course info, old and new numbers.
Is there a macro that will start at the second line (headers on first line)
take the old course number from the first sheet, match it with the old number
on the second sheet, fill in the new number in a blank column on the first
sheet. then move to the next line and repeat until it is finished. I can use
paste special if there is a way to display the correct info from the second
sheet with an equation in each cell of the currently blank column.

Thanks in advanced for the help.
--
Tom

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
XML Data Compression / Decompression Over the Wire
http://www.eggheadcafe.com/tutorials...ssion--de.aspx