• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

compare and replace?

I

itakunk

New Member
Hello, I think I have a simple problem, but I don't know where to look the answer exactly..

I have two lists of names - List A and List B. List A has also a extra column with their ID numbers. My problem is that I need to compare and add.

I need to add those ID's from List A to the List B in to an extra column. List B is an updated list and don't have all the names, so I can't just sort A-Z and copy/paste. Most of the names are the same but there are some missing and some added.

I hope You undestanded my problem and can help!:)

Thank you so much!:)
 
Hi, itakunk


The following formula will serve your pupose:

=INDEX(NameInListA,MATCH(0,COUNTIF(FirstCellOfListB,NamesInListA),0))


This is very simple have a look at this post of NARAYANK991 who has quoted and explained the formula:


http://chandoo.org/forums/topic/stock-scanning


Regards,

FASEEH
 
I'm sorry, I don't understand:( Not a professional excel user...

Excample:


column A

List A

David Smith

Diane Lane

Gary Johnson


column A.........column B

List B...........ID

David Smith......1113

Diane Lane.......4598

Gary Johnson.....7777


Now - I need to have those ID numbers, from list B, matched/copyed to names in the list A in a separate column.

So that I would have finally two list with two columns with names and ID's.

Thanks for your help!:)
 
Hi ,


If all you want to do is match the names in List A , with the names in List B , and retrieve their IDs , from List B , then the Excel VLOOKUP function is ideal.


If we assume your list A starts from cell A2 , downwards ( in Sheet1 ) , and if your List B extends from A2 through B77 ( in Sheet2 ) ; then in cell B2 of Sheet1 , put in the following formula :


=VLOOKUP(A2,Sheet2!$A$2:$B$77,2,FALSE)


in cell B2 , and copy downwards.


Narayan
 
Yes, thanks very much! That was all I needed. I didn't have them even in separate sheets.

One thing though - In my excel version, I can't use komas, but semicolons.

I replaced all komas and it worked:)
 
You must log in or register to reply here.
Back
Top