+ Reply to Thread
Results 1 to 5 of 5

Extract list of values from one column based on values in another column

  1. #1
    Registered User
    Join Date
    07-21-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Extract list of values from one column based on values in another column

    Hi,
    My first post and i think it is kinda simple!

    I have data in the following format (example only, not real information)

    Sample Criteria Cow_Sample
    123456 Cow
    234872 Dog
    384755 cat
    222222 Cow
    837465 Dog
    873927 Cow

    I want to extract all the sample values where Criteria = "Cow"
    the extracted data i want to put in a new column called Cow_Sample

    all the various formula i have tried extract the data, but dont remove the blank cells, ie i get this:
    123456


    222222

    873927

    i want to get the values consecutivly, no gaps.

    Im not a VBA user, ive just started to understand array formulas which i think is what i need.

    many thanks!

    TS

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extract list of values from one column based on values in another column

    welcome to the forum, TS. assuming data in column A and B, try this array formula in cell D2:
    =IFERROR(INDEX($A$2:$A$7,SMALL(IF($B$2:$B$7="Cow",ROW($A$2:$A$7)),ROWS($D$2:D2))-ROW($A$2)+1),"")

    ...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 your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    you could also use a PivotTable for this. click on cell A1
    go to INSERT tab -> PivotTable
    click OK. on the right side of the Excel window is the PivotTable Fields. that controls what you wish to see in the report
    click and drag the Criteria Field to the FILTERS area
    click and drag the Sample Field to the ROWS area
    choose Cow for Filters.

    you could also not use step 4 and 6 and replace with using slicers. INSERT tab -> Slicers. choose criteria. those buttons allow you to choose your criteria

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    07-21-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Re: Extract list of values from one column based on values in another column

    Thanks benishiryo
    I have just managed to find the following which seams to work, i will try your solution also
    =IFERROR(INDEX($A$2:$A$7,SMALL(IF($B$2:$B$7="Cow",ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($A$2:$A2))),"") a google search
    =IFERROR(INDEX($A$2:$A$7,SMALL(IF($B$2:$B$7="Cow",ROW($A$2:$A$7)),ROWS($D$2:D2))-ROW($A$2)+1),"") your solution

    as i have over 2,000 rows of data and this will grow to over 10,000 within a few months, can i use columns, ie A:A instead of actual cell ranges.

    thanks for your help!
    TS
    Last edited by The Speculator; 07-21-2019 at 09:47 PM.

  4. #4
    Registered User
    Join Date
    07-21-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    3

    Re: Extract list of values from one column based on values in another column

    I have attached a slightly modified verion of the data.
    I actually want to extract the ID numbers that correspond to Stand-1, Stand-2, Stand-3 & Duplicate. so perhaps a NOT logical expresion based around the "original" value would select the 4 different Type values

    If possible I dont want to constrain the data by row, but leave it open so as I add more data it automatically updats the extracted list.

    all help greatly appreciated

    TS
    Attached Files Attached Files

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Extract list of values from one column based on values in another column

    array formulas slow down your file if you use the entire column. the easiest option is to convert your data into a Table. just click on cell A1, go INSERT tab -> Table. or CTRL + T.

    when you redo your range, it becomes something along:
    =IFERROR(INDEX(Table1[ID],SMALL(IF(Table1[Type]="Original",ROW(Table1[ID])),ROWS($I$2:I2))-ROW($E$2)+1),"")

    i don't know if your scenario is exactly what you mentioned. in the eg, it seems you want everything EXCEPT "Original". that makes it easy. just find everything not equals to "Original".
    =IFERROR(INDEX(Table1[ID],SMALL(IF(Table1[Type]<>"Original",ROW(Table1[ID])),ROWS($J$2:J2))-ROW($E$2)+1),"")

    if you really need to label the 4 criteria, then:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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] How to extract values in one column based on criteria in another column
    By TimLarson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-27-2019, 07:06 PM
  2. Need VBA function to extract values from one column based on values enlisted
    By suhasg1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2018, 10:54 AM
  3. [SOLVED] VBA Creating a list of unique values from one column based on criteria from another column
    By bilbo85 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2016, 02:38 PM
  4. Replies: 4
    Last Post: 04-26-2015, 12:18 PM
  5. Replies: 3
    Last Post: 02-14-2013, 10:25 AM
  6. Extract column label based on MAX and MIN values
    By luis33 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-14-2010, 07:26 PM
  7. Extract and sum values based on Key Column
    By cpremesh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2009, 04:38 AM

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