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

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?

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)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bobf" wrote in message
...
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?

Here's a link to a demo file with the implemented set-up:
http://www.savefile.com/files/5254674
File: Search_n_extract_rows_using_list_from_anothersheet .xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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


"bobf" wrote in message
...
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?

Thanks Max, I'll try that today to see if it's what I want.

"Max" wrote:

Here's a link to a demo file with the implemented set-up:
http://www.savefile.com/files/5254674
File: Search_n_extract_rows_using_list_from_anothersheet .xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

"Rick Hansen" wrote:

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


"bobf" wrote in message
...
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?

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

"Max" wrote:

Here's a link to a demo file with the implemented set-up:
http://www.savefile.com/files/5254674
File: Search_n_extract_rows_using_list_from_anothersheet .xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bobf" wrote in message
...
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

Here's the link to the revised demo file (just in case needed):
http://www.savefile.com/files/4670322
File: Search_n_extract_rows_using_list_from_othersht_v2

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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
.

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




"bobf" wrote in message
...
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

"Rick Hansen" wrote:

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


"bobf" wrote in message
...
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?