Matching names from sheet2, sheet3, sheet4 with names in sheet1

**Lynx2x** · 02-22-2012, 10:10 AM

Hello again!

Yesterday guys hellped me out with some problem and now as I move on I got another one, that is connected with the one before, but diferent, so I'm posting new thread.

I have a workbook (attached at the bottom of post), where in column A there are Data (mixed names and surnames), in column B and C there are cleaned and separated names and surnames, but we still don't know what is what. So in sheet3, 4 and 5 (MaleNames, FemaleNames, Surnames) we have databases of names(M/F) and surnames and my question is if it is possible to lookup every cell from column B and:
1. compare with male names (sheet3) and if there is a match we copy that name in D (Name Male B)
2. compare with female names (sheet4) and if there is a match we copy that name in F (Name Female B)
3. compare with surnames (sheet5) and if there is a match we copy that name in H (Surname B)

Same lookup and compare we then do for column C only that we copy names in columns E, G, I (Name, Male,Female, Surname C).

Can please someone can help me with that?

Thank you guys, you are the best

Tilen

Example22.xlsx

**NBVC** · 02-22-2012, 10:15 AM

Try in D2:

=IF(ISNUMBER(MATCH(B2,MaleNames!A:A,0)),B2,"")

and in E2:

=IF(ISNUMBER(MATCH(C2,MaleNames!A:A,0)),C2,"")

repeat for other columns substituting appropriate sheetnames.

copy alll formulas down

**Lynx2x** · 02-22-2012, 10:26 AM

First of all thank you for your quick reply.

Yeah, I've tried using match or VlookUp but then in the cell there is formala and not its value (I know for paste special) and most of all I would like to have that solved in way that when I get a new base of names and surnames and would simply click on a button and macro will compare and copy values in its proper place.
Hope you know what I mean and I hope it is not to much to ask but you would really help me with that (not just for today...but for everyday

)

**NBVC** · 02-22-2012, 10:30 AM

If you convert your ImePriimek sheet table into an Excel Table, through Insert|Table, then when you add new names to bottom the formulas will automatically copy down to those rows... and since the MATCH() functions reference the whole columns in the other sheets, you don't need to make any other adjustments. This will avoid having to use VBA.

**Lynx2x** · 02-23-2012, 06:05 AM

Hi.

Yes, that is all true, and I thank you for that, but I want to have a macro that does this, so everytime I get new uncleaned database, I could clean it with few simple clicks on buttons.

Can you help me with that?

**NBVC** · 02-23-2012, 08:57 AM

I can't help you with VBA.. but others here might be able to.

**snb** · 02-23-2012, 10:09 AM

This should do the trick:

Please Login or Register  to view this content.

If it's a male's name it will be put into column D
If it's a female's name it will be put into column E
If it's a surname it will be put into column F

No need to differentiate any further.

**nilem** · 02-23-2012, 10:21 AM

as an option

Please Login or Register  to view this content.

**Lynx2x** · 02-24-2012, 04:40 AM

Hello guys!

First of all thank you for your replies and your help, you are really the best.

Both of codes are great and way beyond my knowledge of VBA codes, so once again I'm very thankfull for that. Although code from user snb is not working as it should, but code from nilem is working great with one little problem. When I add this code to a bigger database is working great until it reaches an empty cell in column B or C. Lets say it reaches the empty cell in column B. When it does it stops comparing names from that column, but it countinoues to compare from column C. Then lets say also one cell in column C is empty programe stops comparing everything.

Hope you know what I mean and if may I ask you to help me with that. What should I add to the code?

Thank you in advance!

Tilen

**nilem** · 02-24-2012, 05:01 AM

try it

Please Login or Register  to view this content.

**Lynx2x** · 02-24-2012, 06:07 AM

Yes, it is working perfectly! Thank you very very much. You have made my life much easier

!

Just one more question. Now that I have all this, I've made few macros myself for sorting all these names and surnames and everything is working great, but I've made everything with selection. So if I want to sort surnames I must select all of them from column H and I'm wondering how to do that without selection, so when I click on button it will automaticly know that it must select column H.

Do you know what I mean? Thanks once again!

**Lynx2x** · 02-28-2012, 03:49 AM

Hello again!

I have one more question on this topic.

In some cells A there is NAME, MIDDLE NAME, SURNAME...so three records. What should I change in the code above (from nilem), that programe is comparing and sorting all three records if it is male or female name or surname.

Any thoughts?

Thank you very much!

**Lynx2x** · 02-28-2012, 06:26 AM

Mybe it will be easier if I post you some example, to see what I am looking for.

There is a macro(from nilem) that works fine if there is only one name and one surname. But now in some cells there are two names or surnames and code must lookup all three records and compare it if it is a male name or female name or surname and write it into correct cell.

hope anyone could help me!

Thank you very much guys, you are the best!

Examp01.xlsx

**nilem** · 02-28-2012, 07:29 AM

try it

Please Login or Register  to view this content.

PS The data on sheets 'MaleNames', 'FemaleNames', 'Surnames' should be sorted from A to Z (Podatki o listov "MaleNames", "FemaleNames", "Surnames" je treba razvrstiti od A do Ž)

**Lynx2x** · 02-28-2012, 07:51 AM

Yes, perfect!

Thank you very very very much. Working perfectly.
I knew that there is not much to change, but like I said my knowledge of VBA is very poor and you have helped me a lot!

Have a great day!

Matching names from sheet2, sheet3, sheet4 with names in sheet1

LinkBack

Thread Tools

Rate This Thread

Display

Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Re: Matching names from sheet2, sheet3, sheet4 with names in sheet1

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions