+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    bobf
    Guest

    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?

  2. #2
    Max
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?




  3. #3
    Max
    Guest

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

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



  4. #4
    Rick Hansen
    Guest

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

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?




  5. #5
    bobf
    Guest

    Re: Search a worksheet, extract rows using a list from another she

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


  6. #6
    bobf
    Guest

    Re: Search a worksheet, extract rows using a list from another she

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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?

    >
    >
    >


  7. #7
    bobf
    Guest

    Re: Search a worksheet, extract rows using a list from another she

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


  8. #8
    Max
    Guest

    Re: Search a worksheet, extract rows using a list from another she

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  9. #9
    Max
    Guest

    Re: Search a worksheet, extract rows using a list from another she

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



  10. #10
    Rick Hansen
    Guest

    Re: Search a worksheet, extract rows using a list from another she

    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
    [email protected] .

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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?

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1