Oh, nutz <g! The problems of being adept in one programming language, and
then jumping into another. I have a nice little book called VB & VBA in a
nutshell. I need to find something on one worksheet based on a search
criterion from another.

Sooooo I go to the index -- seems reasonable thing to do. I look for FIND,
SEEK, LOCATE, LOOKUP, SEARCH. None of these words except SEEK appear in the
index, and SEEK isn't what I need. Phooey <g!!!

So haere's what I'm trying to do:

Worksheet A has columns A, B, and C respectively containing SSN, Last name,
First Name.

Worksheet B has column P containing Last name & ", " & First name in it.

Both worksheets have name data in all upper case.

I want to scroll through Column P of Worksheet B, grab a name, pop over to
worksheet A, find the lastname, firstsheet pair that matches, grab the SSN
from column A and take it back to Worksheet B and drop it into Column Q.

Worksheet A is sorted on First name & Last name.

Have I given enough info for someone to be able to give me a clue on how to
proceed?
--
Dave
Temping with Staffmark
in Rock Hill, SC

Dave,

Put this formula in WorksheetB!Q2 and copy it down:

=INDEX(WorksheetA!A:C,MATCH(TRIM(LEFT(P2,FIND(",", P2)-1)),WorksheetA!
B:B,0),1)

Regards,

David Miller

Dave the below code will work too:

Regards,

David Miller

***********************Begin Code***********************

Sub getSSN()
Dim WorksheetA, WorksheetB As Worksheet, _
FULL_NAME, CONCAT_NAME, _
C, R As Range


Set WorksheetA = Sheets("WorksheetA")
Set WorksheetB = Sheets("WorksheetB")

Set FULL_NAME = WorksheetB.Range("P1:P" & _

WorksheetB.Range("P65536").End(xlUp).Row)
Set CONCAT_NAME = WorksheetA.Range("B1:B" & _

WorksheetA.Range("B65536").End(xlUp).Row)

For Each C In FULL_NAME
For Each R In CONCAT_NAME
If R.Value & ", " & R.Offset(0, 1).Value = C.Value Then
C.Offset(0, 1).Value = R.Offset(0, -1).Value
End If
Next R
Next C

Set FULL_NAME = Nothing
Set CONCAT_NAME = Nothing
Set WorksheetA = Nothing
Set WorksheetB = Nothing
End Sub

Mindreader <g! I was just coming back to call on you and ask how to build it
into a macro, and there you were with my answer waiting. You be one kewl dude
<g!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Miller" wrote:

Dave the below code will work too:

Regards,

David Miller

***********************Begin Code***********************

Sub getSSN()
Dim WorksheetA, WorksheetB As Worksheet, _
FULL_NAME, CONCAT_NAME, _
C, R As Range


Set WorksheetA = Sheets("WorksheetA")
Set WorksheetB = Sheets("WorksheetB")

Set FULL_NAME = WorksheetB.Range("P1:P" & _

WorksheetB.Range("P65536").End(xlUp).Row)
Set CONCAT_NAME = WorksheetA.Range("B1:B" & _

WorksheetA.Range("B65536").End(xlUp).Row)

For Each C In FULL_NAME
For Each R In CONCAT_NAME
If R.Value & ", " & R.Offset(0, 1).Value = C.Value Then
C.Offset(0, 1).Value = R.Offset(0, -1).Value
End If
Next R
Next C

Set FULL_NAME = Nothing
Set CONCAT_NAME = Nothing
Set WorksheetA = Nothing
Set WorksheetB = Nothing
End Sub

Slicker 'n a pig in poop! On the 8500 lines of WorksheetB, it took 24 minutes
to run -- and saved about 3 days (at least) of the most horrendous type of
data entry -- Manual -- !<G
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Miller" wrote:

Dave the below code will work too:

Regards,

David Miller

***********************Begin Code***********************

Sub getSSN()
Dim WorksheetA, WorksheetB As Worksheet, _
FULL_NAME, CONCAT_NAME, _
C, R As Range


Set WorksheetA = Sheets("WorksheetA")
Set WorksheetB = Sheets("WorksheetB")

Set FULL_NAME = WorksheetB.Range("P1:P" & _

WorksheetB.Range("P65536").End(xlUp).Row)
Set CONCAT_NAME = WorksheetA.Range("B1:B" & _

WorksheetA.Range("B65536").End(xlUp).Row)

For Each C In FULL_NAME
For Each R In CONCAT_NAME
If R.Value & ", " & R.Offset(0, 1).Value = C.Value Then
C.Offset(0, 1).Value = R.Offset(0, -1).Value
End If
Next R
Next C

Set FULL_NAME = Nothing
Set CONCAT_NAME = Nothing
Set WorksheetA = Nothing
Set WorksheetB = Nothing
End Sub

Glad I could help, I am sure it could be done faster using FIND,
instead of looping through each cell, but Hell it was free right,
hehe.

Dave

Riiiiiiiiight <g!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Miller" wrote:

Glad I could help, I am sure it could be done faster using FIND,
instead of looping through each cell, but Hell it was free right,
hehe.

Dave

Actually, because the "searched into" WS was about 7500 rows, with a high
level of duplication in the columns we were interested in, I did a merge to
squoosh out the dupes, and condensed it to 1300 or so rows. Running against
that the process time dropped from 24 minutes to a little over 4! Just as
well as I had to run the macro again against another WS with 15,000+ rows!

If you have a moment, could you take a look at an item I posted yesterday
(and replied to myself 3 times)? It is called "Consolidate Rows" and hasn't
had any takers yet.

Actually I have managed to consolidate the rows with MERGE, but the part I
am trying to learn is the reference to a numeric variable in VBA.
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Miller" wrote:

Glad I could help, I am sure it could be done faster using FIND,
instead of looping through each cell, but Hell it was free right,
hehe.

Dave

I said it didn't "solve" the probelm -- however it gave me sufficient
information that I can hack into it and get where I need to go. Thanks a
million for your help!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Dave Miller" wrote:

Dave,

Put this formula in WorksheetB!Q2 and copy it down:

=INDEX(WorksheetA!A:C,MATCH(TRIM(LEFT(P2,FIND(",", P2)-1)),WorksheetA!
B:B,0),1)

Regards,

David Miller