+ Reply to Thread
Results 1 to 7 of 7

Pulling rows of data based on criteria in one column

  1. #1
    Registered User
    Join Date
    11-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Pulling rows of data based on criteria in one column

    Hello Excel Forum,

    Here goes nothing. I've had great success coming here and finding solutions, so hopefully someone can answer my specific question. I've attached a workbook with a description of what I'm trying to do. I'm not sure if it can be done with formulas or if it will require VBA. I can move it to the VBA section if the latter terms out to be the case. As I am clueless about using VBA, I'm hoping another solution exists (it's not that I'm opposed to copying code, I just like to know how it works with enough depth that I can fix/modify it, and I can't do that with VBA).

    Cheers~
    Ezekedes

    EDIT: As pointed out, I totally flubbed in saying which column was being evaluated. It should be column E, thank you daffodil11
    Attached Files Attached Files
    Last edited by Ezekedes; 12-17-2013 at 06:11 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Pulling rows of data based on criteria in one column

    Your directions say Column D, but it's Column E that's labeled with the header that says Criteria for Selection.

    Can you clarify the criteria column?
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    11-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Pulling rows of data based on criteria in one column

    You're right daffofil11, my bad: I definitely intended to say Column E. I edited my original post, but I was unsure if that would alert you that I'd clarified on that point so I figured I'd also reply to the thread. Thanks for taking interest in my problem.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Pulling rows of data based on criteria in one column

    Have you considered using the Advanced Filter?

    First, you'll need a Filter to base your results on. I added a tab called Filter. It's just a copy of your column headers, and then another row that declares what you need.

    Make sure each column that you want to pull data from has a header. (I put in placeholders for each column of X's).

    You're trying to filter based on E not being blank. In filter language, this translates to ="<>"&"" which means "not equal to blank" so thats what I have directly below the Criteria filter of E.

    Normally, you can only do an advanced filter on the same page, but if you start in a different page you can make it cross the workbook.

    So, starting on your consolidated tab go to Data -> Sort & Filter -> Advanced.

    Choose the 2nd option, Copy To another location

    Your list range is: 'Report (2)'!$D$15:$AG$40
    Criteria range is: Filter!$D$15:$AG$16
    Your copy to is: 'Consolidated Report '!$D$15
    (not 16, the filtered results pull the headers with them)

    If this feels like a hassle to go through the steps each time, a nifty macro can be built to automate the process.

    Here are a few examples:

    Copy of Referral(1).xlsm

    This is a real basic one, with a short list of example data and the filters right next to it and the buttons that filter to tabs 2, 3, & 4.
    adv filter macro for trobertfl.xlsm

  5. #5
    Registered User
    Join Date
    11-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Pulling rows of data based on criteria in one column

    I'm trying to make this work but I'm struggling. I've managed, one time, to get it to pull the values in column D. Every time I've tried to do it since, I've gotten an error message saying that "The extract range has a missing or illegal field name". Setting that aside, I'm still not sure how to get the whole row to follow the column D value when it's copied. I'm going to tinker with it and see if I can fix it. I've tried a couple different things and still keep getting the same error though, so I'm not sure what to do. I can't tell if anything I do works if I can't even get the filter to work in the first place

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Pulling rows of data based on criteria in one column

    If toying with the filters and/or VB proves unfruitful, you can always use a Rank and INDEX(..MATCH( as a workaround. (which I did for months before I made the leap to VB)

    Starting in Report (2) tab, go out to column AM. In AM16 put =if(e16<>"",1,"") and copy down. This qualifies the records you want to keep.

    In AN16 put, =RANK(AM16,$AM$16:$AM$40)+COUNTIF($AM$16:AM16,AM16)-1 and copy down. This takes the numbers, skips the errors, and ranks them from the top.

    Now, on your Consolidated Tab put #s down the left side, starting with 1 in A16, 2 in A17, etc.

    Plop this into B16:

    =IFERROR(IF(INDEX('Report (2)'!$B$16:$AG$40,MATCH('Consolidated Report '!$A16,'Report (2)'!$AN$16:$AN$40,0),COLUMN(A1))=0,"",INDEX('Report (2)'!$B$16:$AG$40,MATCH('Consolidated Report '!$A16,'Report (2)'!$AN$16:$AN$40,0),COLUMN(A1))),"")

    and copy over and downwards as needed to fill the whole area.


    This takes the table of origin, and finds what row the first rank record is on, and then pulls it.

    When it's copied downward, it's targeting the 2, and so on. The IF and IFERROR just produce blanks when it can't find numbers that don't exist.


    INDEX MATCH SORT.xlsx

  7. #7
    Registered User
    Join Date
    11-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Pulling rows of data based on criteria in one column

    I actually have found the issue with the advanced filter.

    After toying with some dummy data, I've noticed that, after selecting your "copy to" location, that range of cells gets assigned the name "Extract". In the dummy data, this name is reassigned to whatever new range of cells I specify as my "copy to" location. This is not the case in my actual data; the same range of cells is named "Extract" regardless of the range of cells I specify as my "copy to" location. The only way to eliminate this name assignment is to delete the cell.

    Here's where it gets weird: You would think that, after deleting the name "Extract", the next specified "copy to" location would be reassigned that name. Not so. The same error message pops up, which makes sense since the extract field name is missing entirely. I'm going to try naming that field manually and then, if that doesn't work, I'll try moving the whole operation to a fresh workbook to see if that reboots the advanced filters auto-assignment of the name "Extract" to the copy-to location.

    Sorry if this is an excessively pedantic explanation. If anyone else has a similar problem in the future, though, I hope this pops up on their search feed, since it is a most infuriating issue.

    If all of this fails, I'll give your workaround a try, daffofil. I'd like to reiterate: Thanks for the support.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to pull rows of data based on a column cell value (Criteria)
    By PaulLor89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2013, 11:26 AM
  2. Pulling data from one worksheet into another based on several criteria
    By kguenzel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2012, 12:37 PM
  3. [SOLVED] Consolidate data in 2 rows into 1 row, based on column criteria
    By PeS in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-22-2012, 11:41 AM
  4. Pulling data lines based on criteria - VBA
    By jamesh777 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-18-2012, 05:06 AM
  5. pulling data from another worksheet based on criteria search
    By spectrum in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-29-2005, 06:18 PM

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