+ Reply to Thread
Results 1 to 19 of 19

Extract values based on two (2) criteria

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

    Extract values based on two (2) criteria

    Would like to extract values based on two (2) criteria. Criteria are in B1 and C1. Raw data is in Sales Tab and expected outcome is in Extract Tab. See attached Excel file. If South and 2010, 2011 are selected for B1 and C1, extract values should be.

    Please Login or Register  to view this content.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract values based on two (2) criteria

    perfect example of when to use pivot table and slicer

    Ps if you put the data into a table like i have in the example it will even auto update the pivot table data range
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Extract values based on two (2) criteria

    humding, can you explain how you did that?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract values based on two (2) criteria

    certainly

    i took the data converted it into a table (optional step) and inserted into a pivot table (you can choose a new sheet or place in the same sheet like i have in the example)

    For the fields used
    Report filter - dragged in "Region"
    Row filter - dragged in "Month"
    Column filter - dragged in "Year"
    Values field - "Sales" "Unit price" and "Profit" were all dragged in (in that order)

    if you do not know how to do this, here is a link for step by step instructions on inserting a pivot table and what the fields look like
    http://www.excel-easy.com/data-analy...ot-tables.html

    some additional (and optional) formating of numbers were applied to make it look prettier

    after the pivot table was setup i inserted two slicers
    one for "region" and one for "year"

    step by step instructions (with videos) on how to do this can be found here
    http://www.contextures.com/excelpivottableslicers.html
    Last edited by humdingaling; 07-19-2015 at 08:23 PM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Extract values based on two (2) criteria

    Cool, thank you

  6. #6
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    humdingaling: neat and cool solution! a formula approach will be great. Can you pls use a formula approach to achieve the same outcome?

    Thanks

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract values based on two (2) criteria

    see attached

    using combination of sumifs and basic logic based on the 2nd validation cell

    not exactly what you asked for but quite similar
    if you want exactly it can be done...the logic of what is blank is what isnt just would take a lot longer to think about
    Attached Files Attached Files
    Last edited by humdingaling; 07-17-2015 at 04:08 AM. Reason: *forgot to put region

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,509

    Re: Extract values based on two (2) criteria

    Ooops wrong thread
    I'm a newbie with PQ, so if my solution can be improved, please let me know. Thanks

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Extract values based on two (2) criteria

    some tinkering I've made it exactly as in your example....minus the formatting for unit price....that you can do with conditional formatting

    however as you can see
    the coding behind this is much more complicated and finicky than pivot table (and i consider pivot tables finicky already)
    its much harder to maintain
    its much harder to fix when something goes wrong
    Attached Files Attached Files

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

    Re: Extract values based on two (2) criteria

    humdingaling: great solution. I formula works great. However, when I changed Sales!$A:$A, Sales!$B:$B and Sales!$C:$C to Sales!$A$2:$A$37, Sales!$B$2:$B$37 AND Sales!$C$2:$C$37, i am getting value error.

    Please help take a look and fix the value error issue. See attached Excel file.

    Modified and getting value error code: in column J
    Please Login or Register  to view this content.
    Thanks
    Attached Files Attached Files

  11. #11
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Extract values based on two (2) criteria

    Maybe tad simpler..In Cell B5

    =IFERROR(SUMIFS(INDEX(Sales!$D:$F,,MATCH(B$3,Sales!$D$1:$F$1,0)),Sales!$B:$B,B$4,Sales!$A:$A,$A5,Sales!$C:$C,$B$1),"")

    Copied down and across
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  12. #12
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    Ace_XL: works but i am still getting no value when I changed Sales!$B:$B,B$4,Sales!$A:$A,$A5,Sales!$C:$C to Sales!$B$2:$B$37,B$4,Sales!$A$2:$A$37,$A5,Sales!$C$2:$C$37. Instead of Sales!$B:$B,B$4,Sales!$A:$A,$A5,Sales!$C:$C, i would like it to readSales!$B$2:$B$37,B$4,Sales!$A$2:$A$37,$A5,Sales!$C$2:$C$37. Reason being that my original data point does not start at A1:F1.

    Please Login or Register  to view this content.
    Thanks

  13. #13
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    Excel file added
    Attached Files Attached Files

  14. #14
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Extract values based on two (2) criteria

    You need the arrays to be consistent. Hence

    =IFERROR(SUMIFS(INDEX(Sales!$D$2:$F$37,,MATCH(B$3,Sales!$D$1:$F$1,0)),Sales!$B$2:$B$37,B$4,Sales!$A$2:$A$37,$A5,Sales!$C$2:$C$37,$B$1),"")

  15. #15
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    I have added the adjusted Excel file to reflect where the actual data start (A9 instead of A1). Would like the formula not to go from Sales!$A:$A. Only go from A10 to the last cell with value.

    Thanks

  16. #16
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    Ace_XL: works like charm! Outstanding work! What of formula to go from C5:F5? Thanks friend

  17. #17
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    Ace_XL: works like a charm! Outstanding work! What of formula to go from C5:G5?

    Thanks friend.

  18. #18
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Extract values based on two (2) criteria

    Just copy the formula in B5 down and across

    It will change column reference and pull up correct answers.

    See attached based on your last sample sheet
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    883

    Re: Extract values based on two (2) criteria

    Outstanding solution, my friend! 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. Replies: 3
    Last Post: 12-18-2014, 10:27 AM
  2. Replies: 1
    Last Post: 11-09-2014, 06:04 PM
  3. [SOLVED] Extract Unique Text Values based on Multiple Criteria
    By Kattenhove in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-26-2014, 07:59 AM
  4. [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
  5. Replies: 1
    Last Post: 02-14-2013, 02:32 PM
  6. [SOLVED] Extract unique values out of list based on an extra criteria
    By sven1975 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2012, 05:02 AM
  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