Value matching with Range values

manojkumarsoni · April 1st, 2010, 02:10 AM

Dear Listers

Greetings from India.

I want help in excel ,

I have one column in which around 1000 names are there those are unique.

Now suppose i have one different sheet , i have to enter some names in one column and check that if the typed name is in the list of 1000 names or not, if it is in the list, i should say "preexist" and if not i should say " New "

1000 Names are on different sheets and data entry in which name column is being entered is in different sheet.

Example
In sheet 1

Names
a
b
c
d
x
y

In sheet 2

Name data entry

Name Checking

a preexist
ram New
c preexist
sil New
y preexist

hope you understand my problem and solve it.

thanks in advance

regards
Manoj

QuadFather · April 5th, 2010, 10:43 PM

Hello friend, just wanted to let you know that I read your question, but I don't have time to post the solution at the moment. I'll work on something and hopefully post it for you tomorrow.

I actually have spent time recently researching and solving a very similar problem myself, and I think I have an easy solution for you.

QuadFather · April 6th, 2010, 08:53 AM

It doesn't get much easier than this, my friend.

Code:

'CREATE A FORM (UserForm1)
'CREATE A TEXTBOX (TextBox1)
'CREATE A BUTTON (CommandButton1)
'Sheet1 CONTAINS LIST OF NAMES
'Sheet2 CONTAINS DATA ENTRY NAMES

Private Sub CommandButton1_Click()
'OPENS SHEET W/ LIST OF NAMES (Sheet1)
Sheets("Sheet1").Activate

'SEARCHES FOR MATCH IN COLUMN A
On Error Resume Next 'IF NO MATCH, CONTINUES TO NEXT CODE
NameMatch = Application.WorksheetFunction.Match(TextBox1.Text, Range("A1:A65536"), 0)

'OPENS SHEET W/ DATA ENTRY NAMES
Sheets("Sheet2").Activate

'DETERMINES NEXT BLANK ROW
NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

'CELLS(ROWINDEX, COLUMNINDEX)
'COUNT OF ROWS IS USED FOR THE ROW INDEX
'COLUMNINDEX 1 IS COLUMN A
Cells(NextRow, 1).Value = TextBox1.Text

'NameMatch WILL = 0 IF THERE IS NO MATCH
'COLUMNINDEX 2 IS COLUMN B
'... COLUMN B IS NEXT TO WHERE YOU JUST DROPPED THE NAME
If NameMatch = 0 Then
Cells(NextRow, 2).Value = "New"
Else
Cells(NextRow, 2).Value = "preexist"
End If

End Sub

One thing to be cautious of is that this code searches for exact matches. And as I'm sure you already know, exact matches are a fundamental weakness of manual data entry. So be careful w/ that keyboard.

Here is the code without my comments:

Code:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Activate

On Error Resume Next 'IF NO MATCH, CONTINUES TO NEXT CODE
NameMatch = Application.WorksheetFunction.Match(TextBox1.Text, Range("A1:A65536"), 0)

Sheets("Sheet2").Activate

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

Cells(NextRow, 1).Value = TextBox1.Text

If NameMatch = 0 Then
Cells(NextRow, 2).Value = "New"
Else
Cells(NextRow, 2).Value = "preexist"
End If

End Sub

Is beautiful, no?