+ Reply to Thread
Results 1 to 2 of 2

Excel 2008 : Extracting certain rows from one sheet to another based on specific list

  1. #1
    Registered User
    Join Date
    Calais, France
    MS-Off Ver
    Excel 2008

    Extracting certain rows from one sheet to another based on specific list


    OK, I'm a numpty at this and have done my best to find the answer already! I think this is a VLOOK formula that's needed but I try and try and don't get the result. If anyone can help, I'm certainly grateful.

    I have a list of clients in sheet1 with data in columns A - I
    Each row has data all cells. There are 30000+ rows so automating this is a must.

    Column C contains a customer reference.

    In sheet2 I have a single column of selected customer reference numbers and need to extract just the rows (all columns) in sheet1 that contain that list of numbers, but not the ones that dont. These numbers are the same as C in sheet1 but just a fairly random selection rather than all of them. Don't mind where I extract them too but into Sheet3 will be nice.

    I'm missing something really obvious?

    Help appreciated if there is a solution before I begin manually finding, selecting, copying and pasting the rows manually as need to pull out 7000 of them.

    Last edited by loneforfree; 02-15-2011 at 11:32 AM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016

    Re: Extracting certain rows from one sheet to another based on specific list

    Here's an approach to try:
    (It's less complicated than it might look)

    Sheet1 contains your data in cells A1:I30000 with the headings in Row_1....eg C1: CustRef

    Sheet2 contains the list of CustRef's you want pulled from Sheet1
    A1: CustRef (it must match the heading from Sheet1!C1)
    A2: (a cust ref to pull)
    A3: (another cust ref to pull)

    Sheet3 is the destination for the extracted data
    A1:I1 contains the same headings as Sheet1

    Using Sheet3:
    • Select cells A1:I2 (The Advanced Filter will want to start with at least 2 rows)
    • Data.Advanced_Filter
    ...Check: Copy to another location
    ...List Range: Select Sheet1!$A$1:$I$30000
    ...Criteria Range: Select Sheet2!$A$1:$A$10 (or whatever range contains the list)
    ...Copy To: Select Sheet3!$A$1:$I$1
    ...Click [OK]

    (Notice: you are on Sheet3 and but the source data is on Sheet1)

    The reason: An advanced filter cannot SEND data to another sheet, but
    it can PULL data from another sheet.

    Is that something you can work with?
    Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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