macro to change numbers to names
 

I need a macro that will convert a list of numbers to a
list of names. On sheet 1 there is a list of numbers 1
through 18 and in the cell next to it is a list of names.
The names are listed in the next cell, i.e., cell b3 is
the number 1 cell c3 is John, cell b4 is the number 2, c4
is the name Joan, etc. On sheet two I have two numbers in
each cell in the range D 11 L 27. The numbers on sheet 2
look like this, 1 - 2, 3 - 4, 5 - 6, etc through 17 - 18.
Once I activate the macro I would like to have sheet 2
look like this; John - Joan, Betty - Roxie, Harry - Wally.
Thanks in advance

Perhaps a formula approach might also work/suffice ?

Assume in Sheet1, in B3:C20
is the source list of numbers and names

1 John
2 Joan
3 Betty
4 Roxie
5 Harry
6 Wally
....
....
17 Peter
18 Jane

In Sheet2
---------
Assume the list below is in A2:A5
(the list will be text)

1 - 2
3 - 4
5 - 6
17 - 18

Put in B2:

=IF(A2="","",INDEX(Sheet1!$C$3:$C$20,MATCH(MID(A2, 1,SEARCH("-",A2)-1)+0,Shee
t1!$B$3:$B$20,0))&" -
"&INDEX(Sheet1!$C$3:$C$20,MATCH(MID(A2,SEARCH( "-",A2)+2,99)+0,Sheet1!$B$3:$B
$20,0)))

Copy B2 down

B2:B5 will return:

John - Joan
Betty - Roxie
Harry - Wally
Peter - Jane

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"WAB" wrote in message
...
I need a macro that will convert a list of numbers to a
list of names. On sheet 1 there is a list of numbers 1
through 18 and in the cell next to it is a list of names.
The names are listed in the next cell, i.e., cell b3 is
the number 1 cell c3 is John, cell b4 is the number 2, c4
is the name Joan, etc. On sheet two I have two numbers in
each cell in the range D 11 L 27. The numbers on sheet 2
look like this, 1 - 2, 3 - 4, 5 - 6, etc through 17 - 18.
Once I activate the macro I would like to have sheet 2
look like this; John - Joan, Betty - Roxie, Harry - Wally.
Thanks in advance

Max, thank you very much, works great.
wab

"Max" wrote:

Perhaps a formula approach might also work/suffice ?

Assume in Sheet1, in B3:C20
is the source list of numbers and names

1 John
2 Joan
3 Betty
4 Roxie
5 Harry
6 Wally
....
....
17 Peter
18 Jane

In Sheet2
---------
Assume the list below is in A2:A5
(the list will be text)

1 - 2
3 - 4
5 - 6
17 - 18

Put in B2:

=IF(A2="","",INDEX(Sheet1!$C$3:$C$20,MATCH(MID(A2, 1,SEARCH("-",A2)-1)+0,Shee
t1!$B$3:$B$20,0))&" -
"&INDEX(Sheet1!$C$3:$C$20,MATCH(MID(A2,SEARCH( "-",A2)+2,99)+0,Sheet1!$B$3:$B
$20,0)))

Copy B2 down

B2:B5 will return:

John - Joan
Betty - Roxie
Harry - Wally
Peter - Jane

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"WAB" wrote in message
...
I need a macro that will convert a list of numbers to a
list of names. On sheet 1 there is a list of numbers 1
through 18 and in the cell next to it is a list of names.
The names are listed in the next cell, i.e., cell b3 is
the number 1 cell c3 is John, cell b4 is the number 2, c4
is the name Joan, etc. On sheet two I have two numbers in
each cell in the range D 11 L 27. The numbers on sheet 2
look like this, 1 - 2, 3 - 4, 5 - 6, etc through 17 - 18.
Once I activate the macro I would like to have sheet 2
look like this; John - Joan, Betty - Roxie, Harry - Wally.
Thanks in advance

You're welcome !
Glad it worked for you
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"wally667" wrote in message
...
Max, thank you very much, works great.
wab