+ Reply to Thread
Results 1 to 10 of 10

Extract values based one criteria

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Extract values based one criteria

    Looking for formula to extract values in G2:G10 into A17:A27 if alphabet select in A13 (for example, A) matches the first letter of values in G2:G10. For example, if A was selected in A13, need formula to only extract all values that has "A" as their first letter (see F2:F10) into A17:A27 and their corresponding values.

    See attached sample file.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extract values based one criteria

    Isn't this a repeat ...???


    http://www.excelforum.com/showthread...t=#post4343090

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values based one criteria

    No. I modified the file. The first post had check boxes.

    Thanks

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extract values based one criteria

    Not in the version I looked at: it is identical except your Table is in different columns :

  5. #5
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values based one criteria

    Yes, identical but table setup is different.

    Thanks

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extract values based one criteria

    in J2

    =IF(F2=$A$13,MAX(J$1:J1)+1,"")

    Copy down

    in B17

    =IF(ROWS($1:1)>MAX($J:$J),"",INDEX(H$2:H$10,MATCH(ROWS($1:1),$J$2:$J$10,0)))

    in C17

    =IF(ROWS($1:1)>MAX($J:$J),"",INDEX(I$2:I$10,MATCH(ROWS($1:1),$J$2:$J$10,0)))

    Custom Format B as 0;0;;@ to hide 0s in B17 onwards

    These are just a minor modification to previous formulae given by Pete.

  7. #7
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values based one criteria

    JohnTopley: great! formula works. Need formula for A17 to copy down.

    Thanks
    Last edited by bjnockle; 04-09-2016 at 05:35 PM.

  8. #8
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values based one criteria

    JohnTopley: Can you modify this formula to start at a row number versus the entire column? See sample below. =IF(ROWS($110:110)>MAX($J1:$J10),"",INDEX(H$2:H$10,MATCH(ROWS($1:1),$J$2:$J$10,0)))

    Thanks

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extract values based one criteria

    Column A

    =IF(ROWS($1:1)>MAX($J:$J),"",INDEX(B$2:B$10,MATCH(ROWS($1:1),$J$2:$J$10,0)))

    From Pete's original post ....

    I've used Conditional Formatting on cells A18:B27 with a formula of =A18=A17 to change the foreground colour to the background colour, to effectively hide the values that are the same as the row above (i.e. Apples, Antioxidants etc.)

    ROWS($1:1) is not a whole column (it is a row): it is simple a counter starting at 1 and incrementing by 1 as the formula is dragged down.


    As an observation: I fail to see the merit of changing the layout from the posting replied to by Pete!
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract values based one criteria

    Great work JohnTopley. Works like a charm. Thanks a million.

+ 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] Extract values based on two (2) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-07-2015, 04:58 PM
  2. [SOLVED] Extract values based on three (3) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-23-2015, 02:30 PM
  3. Extract values based on two (2) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-19-2015, 04:14 AM
  4. [SOLVED] Extract values based on two (2) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-18-2015, 05:10 AM
  5. [SOLVED] Extract duplicated values based on an extra criteria
    By Eduard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2013, 05:50 AM
  6. Replies: 1
    Last Post: 02-14-2013, 02:32 PM
  7. Extract Unique Values from an Array based on Criteria
    By ronleex324 in forum Excel General
    Replies: 1
    Last Post: 10-04-2011, 06:37 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