Replace "name" with "key"

[sera]

I want to replace a name field with the name's key

In sheet1 you have the following:

[u]names[/u]
bob
sue
jim
james
bob
james
sue
fred
jim
fred

In sheet2 you have the key to this

[u]key[/u]    [u]name[/u]
14	bob
28	sue
22	jim
61	james
25	fred

I want to replace the names in the first sheet by the key on the second sheet.

This is for converting a lookup value from the name to the key.

Any help?

Sera

 

[Bong]

Why would you want to use a macro rather than the Excel lookup function?

_________________
Bob Rashkin

 

[sera]

im cleaning alot of data to import into a DB (mysql)

Sera

 

[sera]

Bong,
I think I see what you are saying....

the data is not in "lookup" form. currently it is listing the values (text of the lookup). I need to convert it so it is no longer the text but the index (key). This is so I can import it into a SQL database.

There are LOTS of entries or I would do it by hand.

I'm open for all suggestions.



Sera

 

[EBGreen]

Why not just do it as a join in the query that imports the data into SQL? Or is that not an option?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[sera]

tomthumbkp
can you give a simple example? i'm not exaclty following you

Sera

 

[Jayla]

You could do it with formulas

Sheet1

names
bob
sue
jim
james
bob
james
sue
fred
jim
fred

Sheet2 (NB: now ordered by name)

key	name	key
14	bob	14
25	fred	25
61	james	61
22	jim	22
28	sue	28

Sheet3

key
=VLOOKUP(Sheet1!A2,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A3,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A4,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A5,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A6,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A7,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A8,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A9,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A10,Sheet2!$B$1:$C$6,2,FALSE)
=VLOOKUP(Sheet1!A11,Sheet2!$B$1:$C$6,2,FALSE)

The VLOOKUP function should return the key of the names supplied in sheet 1. If you need to have values for your import, just select all > copy > paste special > paste values.

It will be easier and quicker than writing a VB macro.

Although I agree with Tom, you could just simpily import both sets of data to SQL and then merge the data together with a join.

 

[EBGreen]

Well, I'm not the best SQLer around (as a matter of fact I'm probably one of the worst) but basically import each sheet into a seperate temp table then do an Insert query using a join to insert the data the way you wnat it into the table that you want it to reside in permanently.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]