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

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 )

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?

as an option

Option Explicit
Option Compare Text

Sub ertert()
'Dim tm!: tm = Timer
Dim x, y, i&, j&, k&, n&, bu As Boolean
Dim ubx&, lbx&, arrSh, wsh
Application.ScreenUpdating = 0

arrSh = Array("MaleNames", "FemaleNames", "Surnames")
For k = 2 To 3
    y = ActiveSheet.Columns(k).SpecialCells(2).Value: n = k   'что ищем
    For Each wsh In arrSh
        x = Sheets(wsh).Columns(1).SpecialCells(2).Value: n = n + 2   'где ищем
        For i = 1 To UBound(y)
            lbx = 1: ubx = UBound(x)
            Do
                bu = 0: j = (lbx + ubx) \ 2
                Select Case y(i, 1)
                    Case Is > x(j, 1): lbx = j + 1
                    Case Is < x(j, 1): ubx = j - 1
                    Case Is = x(j, 1): bu = 1: Exit Do
                End Select
            Loop Until ubx < lbx
            If bu Then Cells(i, n) = x(j, 1)
        Next
    Next wsh
Next k
Application.ScreenUpdating = 1
'MsgBox Timer - tm
End Sub

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

try it

Option Explicit
Option Compare Text

Sub ertert()
Dim x, y, i&, j&, k&, n&, bu As Boolean
Dim ubx&, lbx&, arrSh, wsh
Application.ScreenUpdating = 0
arrSh = Array("MaleNames", "FemaleNames", "Surnames")
For k = 2 To 3
    y = Range(Cells(1, k), Cells(Rows.Count, k).End(xlUp)).Value: n = k 'edited line
    For Each wsh In arrSh
        x = Sheets(wsh).Range("A1:A" & Sheets(wsh).Cells(Rows.Count, 1).End(xlUp).Row).Value: n = n + 2 'edited line
        For i = 1 To UBound(y)
            lbx = 1: ubx = UBound(x)
            Do
                bu = 0: j = (lbx + ubx) \ 2
                Select Case y(i, 1)
                    Case Is > x(j, 1): lbx = j + 1
                    Case Is < x(j, 1): ubx = j - 1
                    Case Is = x(j, 1): bu = 1: Exit Do
                End Select
            Loop Until ubx < lbx
            If bu Then Cells(i, n) = x(j, 1)
        Next
    Next wsh
Next k
Application.ScreenUpdating = 1
End Sub

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!

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!

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

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!