Results 1 to 9 of 9

Thread: Looking up names matching multiple criteria along with duplicates

  1. #1
    Acolyte Nagaraj475's Avatar
    Join Date
    Apr 2014
    Location
    India
    Posts
    29
    Articles
    0
    Excel Version
    Excel2010

    Question Looking up names matching multiple criteria along with duplicates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a list of contact center employees with certain number of surveys and certain CSAT(Customer Satisfaction score for surveys). I figured out way to find maximum surveys and respective CSAT achieved for that particular number of surveys. I also have found formula to get names of employees matching max survey and max CSAT criteria.The problem with the formula is it omits duplicate entries. As in the example,there are 2 employees with same scores but the formula lists only employee 10. How do I improvise the formula to list all employees including duplicates? I have attached the sample data file and a screenshot below.

    Click image for larger version. 

Name:	ForumPost.png 
Views:	38 
Size:	32.3 KB 
ID:	10292ForumPost1.xlsx

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,854
    Articles
    0
    Excel Version
    O365
    What is the formula that you have so far?

  3. #3
    Acolyte Nagaraj475's Avatar
    Join Date
    Apr 2014
    Location
    India
    Posts
    29
    Articles
    0
    Excel Version
    Excel2010
    For finding top largest values using formula =LARGE(Survey,A2) Survey is named range

    For finding maximum CSAT for a particular large value, using formula =MAX((Survey=F2)*CSAT)

    CSAT is a named range here.
    To get employee names matching highest survey and highest CSAT criteria using formula =INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0))

    All these formulae are included in the attached file. Thank you for looking into. Thanks for your time.

    Regards,
    Nagaraj

  4. #4
    Seeker Roger Haney's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    Microsoft Excel Office 365
    Quote Originally Posted by Nagaraj475 View Post
    For finding top largest values using formula =LARGE(Survey,A2) Survey is named range

    For finding maximum CSAT for a particular large value, using formula =MAX((Survey=F2)*CSAT)

    CSAT is a named range here.
    To get employee names matching highest survey and highest CSAT criteria using formula =INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0))

    All these formulae are included in the attached file. Thank you for looking into. Thanks for your time.

    Regards,
    Nagaraj
    One option is to simplify the arrangement of your Data (if this is allowed ). Put your Emp. names in numerical order in Column H. In G2 type =D2 and fill down. In F2 type =C2 and fill down and then F9 to see results. if you need the "Survey" results to be in descending order as in your example, then you can copy the table range and "Past Special" values and then sort descending by that Column. if this in not an option ( re-arranging your Emp. names ), then this is still possible by a formula. Please advise.

  5. #5
    Seeker Roger Haney's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    Microsoft Excel Office 365
    (Could you not sort by the "Survey" Column initially, which seems to be easiest ? )

  6. #6
    Acolyte Nagaraj475's Avatar
    Join Date
    Apr 2014
    Location
    India
    Posts
    29
    Articles
    0
    Excel Version
    Excel2010
    Quote Originally Posted by Roger Haney View Post
    (Could you not sort by the "Survey" Column initially, which seems to be easiest ? )
    Thank you, Roger Haney for looking into this and sharing your suggestion. Sorting multiple levels is easier but, wanted a way to getting results without altering or modifying original data.

    Thanks,
    Nagaraj

  7. #7
    Seeker Roger Haney's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    Microsoft Excel Office 365

    Formula Modification

    Quote Originally Posted by Nagaraj475 View Post
    Thank you, Roger Haney for looking into this and sharing your suggestion. Sorting multiple levels is easier but, wanted a way to getting results without altering or modifying original data.

    Thanks,
    Nagaraj
    The following appendage to your original formula will compensate for duplication, while also leaving your original data structure intact : =IF(AND(F2=F3,G2=G3),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)-1),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)))

  8. #8
    Seeker Roger Haney's Avatar
    Join Date
    Jan 2021
    Posts
    11
    Articles
    0
    Excel Version
    Microsoft Excel Office 365
    As a further refinement in case there are multiple duplicates beyond two, then this formula : =IF(AND(F2=F3,G2=G3),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)-(COUNTIF(F2:$F$11,F2)-1)),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)))

  9. #9
    Acolyte Nagaraj475's Avatar
    Join Date
    Apr 2014
    Location
    India
    Posts
    29
    Articles
    0
    Excel Version
    Excel2010
    Quote Originally Posted by Roger Haney View Post
    As a further refinement in case there are multiple duplicates beyond two, then this formula : =IF(AND(F2=F3,G2=G3),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)-(COUNTIF(F2:$F$11,F2)-1)),INDEX($B$2:$B$11,MATCH(1,(Survey=F2)*(CSAT=G2),0)))
    Thank you Roger Honey for sharing your expertise and helping. Many thanks to Excelguru.ca and team for helping get answer for many Excel related queries from the past 7 years.

    Thanks,
    Nagaraj

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •