+ Reply to Thread
Results 1 to 21 of 21

Identifying Most Common Occurrences (Text) with Criteria

  1. #1
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Identifying Most Common Occurrences (Text) with Criteria

    Hello,

    I am trying to generate a list of the five most common text occurrences using criteria to filter the results.

    So far, I am able to create an INDEX MATCH function to identify which text occur most frequently in a range using criteria, but if I copy this formula down, I duplicate the result. Instead, I am using a different formula for results two - four, but do not know how to modify it so I can include my criteria. Can someone please explain how I can implement the criteria into the formula for the remaining four results?

    Please find a copy of my workbook and let me know if you need any clarification.

    Kind regards,
    preppy_prince

    preppy_prince_Example_30052023.xlsx

    *UPDATE*

    I have amended my workbook as I realised I need to return results based on more than one criteria, with each criterion containing multiple 'correct' values which my formula is unable to account for.

    The criteria I am now wanting the INDEX MATCH function to refer to are:

    • D2:D31 | "Full-time" or "Part-time"
    • E2:E31 | "Trainer" or "Trainee"

    This should return the result 'Emma', (as her name appears the most out of all texts in B2:B31 which also satisfy the two criteria) but instead I am receiving #N/A.

    preppy_prince_Example_30052023_V2.xlsx

    *UPDATE*

    New workbook where I am trying to return results using multiple 'or' criteria:

    preppy_prince_Example_30052023_V3.xlsx
    Last edited by preppy_prince; 06-04-2023 at 07:49 PM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Identifying Most Common Occurrences (Text) with Criteria

    with Power Query

    I'm not sure your Mac contains PQ but you can try
    Last edited by sandy666; 05-29-2023 at 11:01 PM.

  3. #3
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Hi sandy666,

    Thank you very much for the suggestion. Unfortunately I do not think it does as the results in the attachment are stored as raw values, which I think may suggest the formula does not work...

    I have also just made an amendment to my spreadsheet as I realised I have multiple criteria (including OR criteria) which I need to satisfy.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Your description does not contain any additional criteria,
    don't change original source file but post additional file with full explanation because posts after changes doesn't make sense

  5. #5
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Apologies - I have restored the original file to the post!

    G2 is now failing to return a result after I added in the second set of criteria.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Identifying Most Common Occurrences (Text) with Criteria

    there are still no defined criteria for the new file
    post detailed description

  7. #7
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Thanks sandy666, I have updated it now.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Identifying Most Common Occurrences (Text) with Criteria

    it's not hard to describe criteria in the post I think

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Identifying Most Common Occurrences (Text) with Criteria

    my dear friend, I am not fixing your formula, I just want to know what you want to achieve and fie should contain proper manually created result

  10. #10
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Sorry I wasn't clear, I meant that I have updated the post with the criteria.

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Identifying Most Common Occurrences (Text) with Criteria

    sorry, but you didn't understood
    so wait for someone else who give you a formula solution
    have a nice day

  12. #12
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Sorry, I must have.

    Thanks for your time and help and have a good day too

  13. #13
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,456

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Maybe,

    G2
    Please Login or Register  to view this content.
    commited with Ctrl+Shift+Enter

  14. #14
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Hi windkinfe,

    This worked! Thank you so much!

    Do you know how I would be able to capture the next four most common occurrences?

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

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    there were NO other matches, apart from Emma, so I introduced a few.

    Also, please confirm the Excel PRODUCT (e.g. O365 for Mac) and VERSION that you are currently using
    Attached Files Attached Files
    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

  16. #16
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Hi Glenn,

    Thanks very much.

    Is there another way I can capture multiple 'or' criteria (I can see you used "time" and "Traine" to capture both cells which contained both "Full-time" and "Part-time" and "Trainee" and "Trainer")?

    I have uploaded another workbook example where I tried using your formula in cells F2 to F6 and listed multiple criteria with {} brackets but it has not returned any results...

    Also, I updated my version information - thanks for letting me know it was unclear.

  17. #17
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,456

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Please Login or Register  to view this content.
    committed with Ctrl+Shift+Enter
    Last edited by windknife; 05-30-2023 at 10:56 PM.

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

    Re: Identifying Most Common Occurrences (Text) with Criteria

    windknife, that is incorrect. It is ONLY matching the first criterion (123 and Cancelled). You need to use MATCH.

    Thus:

    =IFERROR(INDEX($B$2:$B$31,MODE(IF((COUNTIF($F$1:$F1,$B$2:$B$31)=0)*(ISNUMBER(MATCH($C$2:$C$31,{123,126,127},0)))*(ISNUMBER(MATCH($D$2:$D$31,{"Cancelled","Rescheduled","TBD"},0))),MATCH($B$2:$B$31,$B$2:$B$31,0)))),"")

    If you also want to return Diane and Sophie, who meet the criteria, BUT only occur once, you need to add the +{0,0}

    =IFERROR(INDEX($B$2:$B$31,MODE(IF((COUNTIF($F$1:$F1,$B$2:$B$31)=0)*(ISNUMBER(MATCH($C$2:$C$31,{123,126,127},0)))*(ISNUMBER(MATCH($D$2:$D$31,{"Cancelled","Rescheduled","TBD"},0))),MATCH($B$2:$B$31,$B$2:$B$31,0)+{0,0}))),"")
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    01-19-2022
    Location
    Melbourne, Australia
    MS-Off Ver
    MS Office 2019 for Mac | Version 16.73
    Posts
    20

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Hi Glenn,

    Thanks so much to you and windknife - this worked like a charm!

  20. #20
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,456

    Re: Identifying Most Common Occurrences (Text) with Criteria

    Glenn, thanks for your teach.

    preppy_prince, you are welcome.

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

    Re: Identifying Most Common Occurrences (Text) with Criteria

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

+ 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] Function to Find Most Common Text Based on Criteria
    By ProcessG in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-17-2023, 09:12 AM
  2. 1st, 2nd & 3rd most common text based on multiple criteria?
    By CARTERLAND68 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2021, 08:40 AM
  3. Identifying common threads in a spreadsheet
    By tbrookes3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2015, 03:06 AM
  4. identifying 2 of 3 occurrences in a column
    By Cary Patterson in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 03-16-2015, 03:43 PM
  5. Count occurrences of text with various criteria
    By ludgirardi in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-20-2012, 05:31 AM
  6. Count occurrences of text with various criteria
    By ludgirardi in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 12-18-2012, 07:50 PM
  7. Identifying most common word in a list
    By srilakshmi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-04-2012, 11:03 AM

Tags for this Thread

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