Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Match Records (2000)

    I have this spreadsheet with 8000 records. In this list there is a field called "Client Number"
    I have another list of a 119 Client Numbers that I want to match to this list so I have only the records that match these 119 Client numbers.
    I need the complete record in the spreadsheet that has the 8000 records.
    Does anyone know how I can do this?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Records (2000)

    How many columns are in a complete record? What column is the client number in on both sheets? What are the sheet names? On the sheet with 8000 records, do any client numbers repeat? If yes, which record do you want?
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Records (2000)

    Six columns in the sheet that has 8000 records. The client number is in column A in both lists. Sheet names on both are Sheet1.
    No client number repeats

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Match Records (2000)

    One way to "do it on the fly" is to use an Autofilter.

    If your list of "119 names" is in Sheet2!A1:A119, and your 8000 records is in Sheet1 with col A have the "Client number". In the first blank col in the range (if you have 6 cols this would be Col G) enter in G1, as a header, the name doesn't matter):
    In List
    and in G2:
    <pre>=ISNUMBER(MATCH(A2,Sheet2!$A$1:$A$119,0))</pre>

    The copy G2 to G3:G8000 (or whatever), autofill should work

    Now create the autofilter: select a cell in the datarange in Sheet1 and Data - filter - autofilter.
    It will add "dropdowns" on each column. Goto the "In List" dropdown and select "TRUE"

    Now you will have a list of all the Info. You can copy this data to another sheet if you need a copy of it. You can also print directly from here. If you need summary Statistics (min,max, average, count, etc) you can use subtotal function, which only calculates on the "filtered" data.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Records (2000)

    I am having trouble entering the formula. Where you have [Book1]. Should there be brackets around the spreadsheet name?
    I have =vlookup($A1,[Check_List_from_Jerry] Sheet2!$A$1:$F$7384,2,False)

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Records (2000)

    The easiest way to get it right would be to open both workbooks. Then go to the formula and select [Check_List_from_Jerry] Sheet2!$A$1:$F$7384. Then switch to the other workbook and click on cell A1, and then scroll to cell F7384 and hold down the shift key and click on cell F7384. Then press enter. That should enter the link as it needs to be, but without the $ signs. Now select A1:F7384 and press F4 until the $ signs are where they should be (probably only one press of F4).
    Legare Coleman

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Match Records (2000)

    It will need the complete file name (including the .xls).

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Records (2000)

    Above you said both lists were on Sheet1. If that still holds, then change the 2 to 1

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Records (2000)

    Edited 1/27/2005 by Legare Coleman to fix problem pointed out by GoCush.

    In the sheet with the sheet with the 119 client numbers, place the following formulas in the indicated cells:

    <pre>Cell Formula
    B1 =VLOOKUP($A1,[Book1]Sheet1!$A$1:$F$8000,2,FALSE)
    C1 =VLOOKUP($A1,[Book1]Sheet1!$A$1:$F$8000,3,FALSE)
    D1 =VLOOKUP($A1,[Book1]Sheet1!$A$1:$F$8000,4,FALSE)
    E1 =VLOOKUP($A1,[Book1]Sheet1!$A$1:$F$8000,5,FALSE)
    F1 =VLOOKUP($A1,[Book1]Sheet1!$A$1:$F$8000,6,FALSE)
    </pre>



    The formulas are all the same except for the number just in front of "FALSE" which varies from 2 to 6. Replace the "Book1" with the name (including the path if it is in a different directory) of the workbook containing the 8000 records. Replace the 8000 with the actual last row number in the table of 8000 records.

    Once those formulas are in the cells, select cells B1 through F1 and double click on the fill handle in the lower right corner of the selection.

    That should give you what you asked for.
    Legare Coleman

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match Records (2000)

    Thanks, I missed that change from my testing setup.
    Legare Coleman

Posting Permissions

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