+ Reply to Thread
Results 1 to 10 of 10

Index Problem to extract multiple rows based in one column

  1. #1
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    Index Problem to extract multiple rows based in one column

    I have a spread sheet with 800+ rows of data. I am trying to extract all the rows where column B indicates the age of interaction is less than 365 days. I have copied a simplified version below for ease, but essentially I want it to produce the data for columns F&G where B has a value of less than 366.
    Attached Images Attached Images

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Problem to extract multiple rows based in one column

    Hi there... and welcome to the Excel Forum. You have attached an image. That's not very easy to work with. We have to re-type your information before we can begin to address your problem. That will put a lot of people off completely. Not only that, but (depending on their choice of browser) many members cannot even see the image. And we can't see what the column and row labels are.

    Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    Re: Index Problem to extract multiple rows based in one column

    hi

    sorry bout that. I though a screenshot would work fine. (Newbie teething issues). I have uploaded a simple workbook
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Problem to extract multiple rows based in one column

    Hi. use this array formula, copied across and down:
    =IFERROR(INDEX(F:F,SMALL(IF($B$2:$B$19<366,ROW($B$2:$B$19)),ROWS($1:1))),"")

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index Problem to extract multiple rows based in one column

    This array formula** entered in L2:

    =IFERROR(INDEX(F:F,SMALL(IF($B$2:$B$19<366,ROW(B$2:B$19)),ROWS(L$2:L2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy across to M2 then down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    Re: Index Problem to extract multiple rows based in one column

    Thanks for this, If I wanted to extract to a different sheet would it be OK to insert sheet1! before index.

    Chris

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index Problem to extract multiple rows based in one column

    Quote Originally Posted by chrisk67 View Post
    Thanks for this, If I wanted to extract to a different sheet would it be OK to insert sheet1! before index.
    Yes, like this...

    =IFERROR(INDEX(Sheet1!F:F,SMALL(IF(Sheet1!$B$2:$B$19<366,ROW(Sheet1!B$2:B$19)),ROWS(A$1:A1))),"")

    Still array entered.

  8. #8
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    Re: Index Problem to extract multiple rows based in one column

    Many thanks for all your help on this.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Index Problem to extract multiple rows based in one column

    You're welcome.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index Problem to extract multiple rows based in one column

    You're welcome!

+ 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. [SOLVED] Pasting multiple rows based on index number
    By kwagam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2014, 06:08 AM
  2. extract rows based on value in column A
    By fabrecass in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-12-2013, 10:52 AM
  3. extract rows from sheet based on multiple criteria
    By frederikk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2012, 04:12 AM
  4. Extract multiple rows from multiple worksheets with Index formula
    By RedApple in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2011, 02:33 PM
  5. Vlookup and Index/Match problem with multiple rows
    By excelismyfriend in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-01-2009, 12:25 PM
  6. How to extract duplicate rows base on multiple column values
    By masai_chadi in forum Excel General
    Replies: 12
    Last Post: 09-25-2009, 04:31 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