Find data in other worksheet

[Guest]

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 Miller]

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

 

[Guest]

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 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" & _

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

 

[Guest]

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>!

 

[Guest]

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 Miller]

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

 

[Guest]

Riiiiiiiiight <g>!

 

[Guest]

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.