Search a worksheet, extract rows using a list from another sheet

G

Guest

I am trying to use a list of names (first, last) in a colum in worksheetA to
find corresponding entries in an imported worksheet B, Once I find the name
in worksheet B I want to extract the entire row of data into another new
worksheet.

For example
Worksheet A:
Column A
Bill Jones
Fred Smith

Worksheet B:
Place Overall Name Swim Bike
Run Finish
1 12 Bill Jones 24 anytown ST 13:45 54:45
23:00 1:34:45
4 78 Fred Smith 56 anothertown ST 15:00 56:12
24:34 1:48:34

The data in worksheet B in the Name column is all in the same cell (not
different columns)

Now if there is a match between worksheetA name and the name exists in
worksheet B I want to copy the entire row from worksheet B into a new row in
Worksheet C. Not all names in worksheet A will have an entry in worksheet B

Any ideas?
 
M

Max

One play ..

In sheet: A
-----------
Names are listed in col A, in A1 down

In sheet: B
-----------
Assume the table is in cols A to I, data from row2 down
Names are assumed to be in col C, from C2 down

Use an empty column to the right of the table, say col K
Put in K2: =IF(ISNUMBER(MATCH(C2,A!A:A,0)),ROW(),"")
Copy K2 down to say, K100, to cover the max expected data
(Leave K1 empty)

In sheet: C
----------
Copy > paste the same col headers over from sheet B

Put in A2:
=IF(ISERROR(SMALL(B!$K:$K,ROWS($A$1:A1))),"",INDEX(B!A:A,MATCH(SMALL(B!$K:$K
,ROWS($A$1:A1)),B!$K:$K,0)))

Copy A2 across to I2, fill down to I100
(cover the same range size as done in col K in sheet B)
 
R

Rick Hansen

Bob f, you really didn't specify if wanted formula or vba code to solve
your problem. Here is alittle bit of vba code as macro that should solve
your problem. This macro can be run fom any sheet.


rgds to ya

Rick

Public Sub FindNameMoveData()
Dim A As Worksheet, B As Worksheet, C As Worksheet
Dim iArow As Integer, iBrow As Integer, iCrow As Integer
Dim Name As String

' set object pointer for each sheet "A","B","C"
Set A = Worksheets("A")
Set B = Worksheets("B")
Set C = Worksheets("C")


iArow = 1 ' set beginning row position
iBrow = 2
iCrow = 2

Application.ScreenUpdating = False ' stop screenupdate
Do Until IsEmpty(A.Cells(iArow, "A")) ' loop thru names on sheet "A"
Name = A.Cells(iArow, "A") ' save name for compare
Do Until IsEmpty(B.Cells(iBrow, "C")) ' loop thru names on sheet
"B"
If Name = B.Cells(iBrow, "C") Then ' find same name in sheet "B"
' copy from sheet "B"
B.Range("A" & CStr(iBrow) & ":" & "I" & CStr(iBrow)).Copy
' paste to sheet "C"
C.Range("A" & CStr(iCrow) & ":" & "I" &
CStr(iCrow)).PasteSpecial (xlPasteValues)
' increment sheet "C" row pointer
iCrow = iCrow + 1
Exit Do ' name was found, exit this loop
End If
iBrow = iBrow + 1
Loop
iBrow = 2
iArow = iArow + 1
Loop
Application.CutCopyMode = False ' clear clipbroad

End Sub ' we B done
 
G

Guest

At this point I'll take either. Thanks for your reply. I'll try your VBA
code to see if it's what I am looking for.

Thanks
 
G

Guest

Max, I tried your download and it appears to work except for one thing. The
column of names in the worksheet that I am trying to search is not just
names. It MAY be just names but it may also be a first name, last name, age,
city and state, all in the same column. so now somehow I have to search that
column and be able to filter out the rest of the information for the search.

Hope that makes sense

Thanks again
 
M

Max

In sheet: B
------
Try changing the formula in K2 to:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A!$A$1:$A$100,C2))*(A!$A$1:$A$100<>""))=1,R
OW(),"")

Copy K2 down to K100, as before

Adapt the range: A!$A$1:$A$100 in the formula to suit
(use the smallest possible range)

(No change to formulas in sheet C)

Now it should work even if you have, say:

Bill Jones 24 anytown ST
Fred Smith 56 anothertown ST
etc

instead of just the names:

Bill Jones
Fred Smith
etc

within col C in sheet: B
 
R

Rick Hansen

Hello again Bobf,
After reading yours and max's threads, I modified my vba code now to
search name in column C as you originaly requested (see code below). Maybe
this code will do you better. if have any questions email me at
(e-mail address removed) .

HTH
Rick

Public Sub FindNameMoveData3()
Dim A As Worksheet, B As Worksheet, C As Worksheet
Dim iArow As Integer, iBrow As Integer, iCrow As Integer
Dim x As Integer
Dim Name As String, CkName As String

' set object pointer for each sheet "A","B","C"
Set A = Worksheets("A")
Set B = Worksheets("B")
Set C = Worksheets("C")


iArow = 1 ' set beginning row position
iBrow = 2
iCrow = 2

Application.ScreenUpdating = False ' stop screenupdate
Do Until IsEmpty(A.Cells(iArow, "A")) ' loop thru names on sheet "A"
Name = A.Cells(iArow, "A") ' save name for compare
Do Until IsEmpty(B.Cells(iBrow, "C")) ' loop thru names on sheet
"B"
CkName = B.Cells(iBrow, "C") ' get name,address, etc
For x = 1 To Len(CkName)
' search and compare names
If Name = Mid(CkName, x, Len(Name)) Then
' copy from sheet "B"
B.Range("A" & CStr(iBrow) & ":" & "I" & CStr(iBrow)).Copy
' paste to sheet "C"
C.Range("A" & CStr(iCrow) & ":" & "I" &
CStr(iCrow)).PasteSpecial (xlPasteValues)
' increment sheet "C" row pointer
iCrow = iCrow + 1
Exit Do ' name was found, exit this loop
End If
Next x
iBrow = iBrow + 1
Loop
iBrow = 2
iArow = iArow + 1
Loop
Application.CutCopyMode = False ' clear clipbroad

End Sub ' we B done
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top