How to select particular rows in column

**pralav** · 06-11-2005, 08:58 PM

Hi

I have two sheets. In sheet 1 I have two columns. Column A has 500 names and column b has tel numbers but not for all of the names. So some names donot have any values in column B.

In sheet 2 I have the same two columns. But here column A has only a subset of the names in sheet 1 i.e., around 350 names, and all these names are present in that name list in sheet 1.

I want to import the tel numbers from sheet 1 to names in sheet 2. I will greatly appreciate any help regarding this. I need to know how we can write a formula to match the names and import their corresponding values.

Thank you very much
Pralav

**Ron Coderre** · 06-11-2005, 09:15 PM

On Sheet2 (and if the phone numbers are numeric):
Insert this formula in B2 (assuming there's a column heading) and copy down:

=IF(VLOOKUP(A2,Sheet1!$A$1:$B$500,2,0),VLOOKUP(A2,Sheet1!$A$1:$B$500,2,0),"")

Actually, this formula works either way (text or numeric):

=IF(VLOOKUP(A2,Sheet1!$A$1:$B$500,2,0)=0,"",VLOOKUP(A2,Sheet1!$A$1:$B$500,2,0))

Does that help?

Ron

**pralav** · 06-11-2005, 09:25 PM

Thanks a lot ron.. I will try it and let you know. Greatly appreciate it. Could you also explain that formula if possible. Thank you

**pralav** · 06-11-2005, 09:39 PM

It is not working. May be I am doing something wrong. Is there anyway I can send you the sheet so you can actually take a look at it. I simplified the explanation by saying tel numbers. The actuall data I have is set of classes. I have around 667 in column a sheet 1, so I changed the 500 number to 667. But it doesnt look like it is comparing properly. Please let me know. Thank you

Peo Sjoblom · 06-11-2005, 10:05 PM

One way would be to use vlookup

=VLOOKUP(cell_with_name_from_A,Sheet2!$A$2:$B$350,2,0)

assume the names start in A2 in sheet1

=VLOOKUP(A2,Sheet2!$A$2:$B$350,2,0)

then copy down

to get all blank numbers first you could select both columns and sort
ascending on B that will put all empty numbers on the top, then put in the
above formula in the first empty cell and copy down, it might be amended
with

=IF(ISNUMBER(MATCH(A2,Sheet2!,A$2:$A$350,0)),VLOOKUP(A2,Sheet2!$A$2:$B$350,2,0),"")

--
Regards,

Peo Sjoblom

(No private emails please)

"pralav" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
>
> I have two sheets. In sheet 1 I have two columns. Column A has 500
> names and column b has tel numbers but not for all of the names. So
> some names donot have any values in column B.
>
> In sheet 2 I have the same two columns. But here column A has only a
> subset of the names in sheet 1 i.e., around 350 names, and all these
> names are present in that name list in sheet 1.
>
> I want to import the tel numbers from sheet 1 to names in sheet 2. I
> will greatly appreciate any help regarding this. I need to know how we
> can write a formula to match the names and import their corresponding
> values.
>
> Thank you very much
> Pralav
>
>
> --
> pralav
> ------------------------------------------------------------------------
> pralav's Profile:
> http://www.excelforum.com/member.php...o&userid=24220
> View this thread: http://www.excelforum.com/showthread...hreadid=378346
>