+ Reply to Thread
Results 1 to 28 of 28

Using Multiple Criteria to Extract Data

  1. #1
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Using Multiple Criteria to Extract Data

    Hi,

    I am trying to extract data in Excel using multiple criteria. i have this being done in Access Database but I wanted to check if Access is working the way it should. Attached is my data set. What i am trying to extract is WHERE (((SalesRep.[YTD PTQ %])>=1) AND ((SalesRep.[C YTD Sales])>=(3000000))) OR (((SalesRep.[YTD Growth %])>0.05) AND ((SalesRep.[C YTD Sales])>=(3000000)))

    How do i do this in Excel?

    Thanks for your help
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Using Multiple Criteria to Extract Data

    What is your expected outcome? Are you wanting to extract a subset of data?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    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: Using Multiple Criteria to Extract Data

    Try

    =IFERROR(INDEX(Data!$A$2:$A$400,SMALL(IF(((Data!$M$2:$M$400>=1)*(Data!$I$2:$I$400>3000000)+(Data!$K$2:$K$400>=0.05)*(Data!$I$2:$I$400>=3000000)),ROW(Data!$A$2:$A$400)-ROW(Data!$A$2)+1,""),ROWS($A$2:A2))),"")

    enter with Ctrl+Shift+Enter: you will see { ..} brackets appear rounf the formula if this is done correctly.

    Copy down until you get blank cells

    Lists "Participant ID" meeting criteria

    Change range highlighted for any other ouput

  4. #4
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Thank you for your response. I want to extract all reps with YTD PTQ% of 100% and sales equal to or greater than $3Million or YTD Growth % greater than equal to 5% and sales greater than equal to $3Million. So this is and or criteria. Thanks

    The criteria is also pasted on the second tab of the attachment. Thanks

  5. #5
    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: Using Multiple Criteria to Extract Data

    That's what the formula does: AND conditions are "*" and OR conditions are "+"

    Did you actually try it???

  6. #6
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    The formula i pasted is out of Access. I dont know how to do it in Excel. Would you mind showing me on the attachment and send it back to me? Thanks for your help.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Using Multiple Criteria to Extract Data

    Just copy and paste John's formula into cell A2 of the criteria tab in the file you provided and confirm by pressing CTRL+SHIFT+ENTER (not just ENTER), then drag the box in the bottom right corner of the cell down to copy down.

  8. #8
    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: Using Multiple Criteria to Extract Data

    See B3 on Criteria
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Thank you John, i see what you were trying to tell me earlier. Appreciate your help with this.

  10. #10
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Thank you for your help. I got a little confused by John's message at first till i saw the second message. Thanks again.

  11. #11
    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: Using Multiple Criteria to Extract Data

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  12. #12
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Hi John,

    if i were to replace the rep id number with the rep name then this formula does not work. I guess the word 'small" in the formula should be replaced? Can you please help me with this?

    attached is the file with the names. What i am trying to do is extract reps who have YTD PTQ% >= 100% and have CYTD Sales >= $3Million or have YTD Growth % >=5%

    Thanks
    Attached Files Attached Files

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Using Multiple Criteria to Extract Data

    As you are requesting further help, I have marked the thread as unsolved, otherwise John may not revisit it.

  14. #14
    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: Using Multiple Criteria to Extract Data

    Try

    =IFERROR(INDEX(Data!$D$2:$D$400,SMALL(IF(((Data!$M$2:$M$400>=1)*(Data!$I$2:$I$400>3000000)+(Data!$K$2:$K$400>=0.05)*(Data!$I$2:$I$400>=3000000)),ROW(Data!$A$2:$A$400)-ROW(Data!$A$2)+1,""),ROWS($A$2:A2))),"")

    Simply change range you want output to be: column D is the name.

    Still enter with Ctrl+Shift+Enter
    Attached Files Attached Files
    Last edited by JohnTopley; 11-20-2016 at 12:06 PM.

  15. #15
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Thank you John. This works. Really appreciate your help with this.

  16. #16
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Hi John,

    would you mind explaining to me how this formula works in lay man's terms. I get some portions of it but not the whole thing. Thanks

  17. #17
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Hi John, somethings not right with the formula. Tim Vanshaick is only at 75% so he shouldnt come up on this list. The criteria is the rep should be 100% for YTD PTQ% and he must have greater than or equal to $3Million in sales or they must have greater than or equal to 5% in YTD Growth % Would you mind looking at it?

    Thanks,

  18. #18
    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: Using Multiple Criteria to Extract Data

    I want to extract all reps with YTD PTQ% of 100% and sales equal to or greater than $3Million or YTD Growth % greater than equal to 5% and sales greater than equal to $3Million.
    He has sales > 3,000,000 (column I) and YTD growth of 9.8% (column K)
    Last edited by JohnTopley; 11-20-2016 at 05:21 PM.

  19. #19
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Correct but his YTD PTQ% is only 75.7% so the first criteria is that he should reach YTD PTQ of 100% or more and also achieve CYTD Sales greater than or equal to $3Million. The second part of this condition is or YTD growth greater than or equal to 5% and CYTD sales greater than equal to 5% Thanks.

  20. #20
    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: Using Multiple Criteria to Extract Data

    You need to explain your conditions as your OR conditions does not make sense: OR means either Condition 1(YTD PTQ% of 100% and sales equal to or greater than $3Million) is met OR condition 2 (YTD Growth % greater than equal to 5% and sales greater than equal to $3Million.) is met.

    So if condition 1 is not met, check condition 2.

    Your condition (as described in your first post) is equivalent to "Sales equal to or greater than $3Million" AND (YTD PTQ% of 100% OR YTD Growth % greater than equal to 5%)

    You seem to be implying the following:

    "Sales equal to or greater than $3Million" AND "YTD PTQ% of 100%" AND "YTD Growth % greater than equal to 5%"

    So which one is the required condition: AND / OR??
    Last edited by JohnTopley; 11-21-2016 at 05:35 AM.

  21. #21
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    i think you explained it correctly. The first condition is YTD PTQ of 100% or more and sales equal to and greater than$3Million. if this is not met then the next condition is YTD growth of 5% or more and sales greater than equal to $3Million.

    Does that make sense? The SQL in Access reads like this :WHERE (((SalesRep.[YTD PTQ %])>=1) AND ((SalesRep.[C YTD Sales])>=(3000000))) OR (((SalesRep.[YTD Growth %])>0.05) AND ((SalesRep.[C YTD Sales])>=(3000000)))

    so if we were to recreate this same criteria in Excel how would we do it? Thanks.

  22. #22
    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: Using Multiple Criteria to Extract Data

    The first condition is YTD PTQ of 100% or more and sales equal to and greater than $3Million. if this is not met then the next condition is YTD growth of 5% or more and sales greater than equal to $3Million.
    Exactly the case for the Salesman you quoted: first condition is not met so look at second!!!!

  23. #23
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Thank you John. Appreciate your help and patience in resolving this.

  24. #24
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Hi John,

    Would you mind please explaining the logic behind this formula? =IFERROR(INDEX(Data!$D$2:$D$400,SMALL(IF(((Data!$M$2:$M$400>=1)*(Data!$I$2:$I$400>3000000)+(Data!$K$2:$K$400>=0.05)*(Data!$I$2:$I$400>=3000000)),ROW(Data!$A$2:$A$400)-ROW(Data!$A$2)+1,""),ROWS($A$2:A2))),"")

    what does SMALL mean here? Thanks.

  25. #25
    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: Using Multiple Criteria to Extract Data

    This is an "Array formula"

    =IFERROR(INDEX(Data!$D$2:$D$400,SMALL(IF(((Data!$M$2:$M$400>=1)*(Data!$I$2:$I$400>3000000)+(Data!$K$2:$K$400>=0.05)*(Data!$I$2:$I$400>=3000000)),ROW(Data!$A$2:$A$400)-ROW(Data!$A$2)+1,""),ROWS($A$2:A2))),"")

    This checks the first condition with "*" meaning "AND"

    Data!$M$2:$M$400>=1)*(Data!$I$2:$I$400>3000000)

    This checks the second condition

    (Data!$K$2:$K$400>=0.05)*(Data!$I$2:$I$400>=3000000)


    Combining the 2 , the "+" acts as an OR condition

    (Data!$M$2:$M$400>=1)*(Data!$I$2:$I$400>3000000)+(Data!$K$2:$K$400>=0.05)*(Data!$I$2:$I$400>=3000000)

    Every time there is a match of either condition, we record the (relative row) ROW of the matched record: if there is no match we record a blank ("")

    ROW(Data!$A$2:$A$400)-ROW(Data!$A$2)+1,""

    so we finish with an array of numbers and blanks e.g. 1,2 ,3,"","",6,"" ..... etc

    The SMALL function then uses ROWS($A$2:A2) to select the first smallest ...ROWS($A$2:A2)=1, the second smallest ROWS($A$2:A3)=2 ... as the formula is dragged down the rows the ROWS($A$2:A2) increments by 1.

    So we will get 1,2,3,6, ......

    These then are used to select the data from our INDEX range INDEX(Data!$D$2:$D$400) so in row 1 of our output we get D2 (relative position 1), D3 (2), D4(3) and D7 (6)

    Hope this helps.

  26. #26
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Thank you John. Really appreciate it. I wont say i understand it all but will make an attempt to take it in small chunks. Thanks again

  27. #27
    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: Using Multiple Criteria to Extract Data

    Click on a cell containing the formula then "Formulas"==>"Evaluate Formula".


    Click the "Evaluate" button and you will see the formula slowly "unwrapped"!

  28. #28
    Forum Contributor
    Join Date
    07-14-2014
    Location
    New City, NY
    MS-Off Ver
    2016
    Posts
    181

    Re: Using Multiple Criteria to Extract Data

    Thanks John for making this even more simpler. Really appreciate it. I was looking for a way to explain this to my colleagues in a real simple way. Let me ask you this, can i not do this using an advanced filter function in Excel? can you show me how to do this using an advanced filter with criteria. Thanks

+ 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 data based on multiple criteria
    By Thanks4helping in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-21-2016, 04:31 PM
  2. [SOLVED] Extract data from table with multiple criteria
    By weedtm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2016, 01:43 PM
  3. [SOLVED] Using Macro to Extract unique data with multiple criteria
    By Xiaojiang in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-09-2015, 11:56 PM
  4. How to extract data based on multiple criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-11-2015, 07:56 PM
  5. [SOLVED] Dynamic Formula Extract Data for multiple criteria
    By Karroog in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-13-2014, 04:08 PM
  6. Extract data from Excel Worksheet using multiple criteria
    By Beudean Dorin in forum Excel General
    Replies: 0
    Last Post: 03-26-2013, 01:31 PM
  7. Extract Data Based on Multiple Criteria
    By trevaaaaaaa in forum Excel General
    Replies: 2
    Last Post: 03-14-2012, 11:42 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