Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Returning multiple results match with no duplicates

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Returning multiple results match with no duplicates

    Hi all,

    New here and would really like some help with a formula.

    (Table created for clarity) I would need the formula to firstly find the persons name in the first Row, once it has been found it would look along the row and return a specific cell (For this instance say its the second row), it would then in the next cell to the returned information do the search again but ignore the previous returned result and return a different one.

    Say we wanted to know the result from the third row for Katie for all of her accounts, the first returned result in the cell would be 'Open 1st', then to the right of that result it would return the next one 'Closed 5th', then lastly 'Open 2nd', and so forth until it has returned all results.

    The only sort of duplicates I dont want returned is when its from the same cell, if the same text appears on a different cell then that is fine.

    Katie 20/06/1984 Open 1st
    John 06/02/1978 Open 2nd
    Peter 31/01/1991 Closed 1st
    Katie 20/06/1984 Closed 5th
    Suzie 15/12/1988 Open 1st
    Katie 20/06/1984 Open 2nd

    Thanks in advance for the help if its possible, it would be a massive help for what is needed to be done with over 20000 accounts.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    Hi & welcome to MrExcel.
    How about

    ABCDEFGH
    1
    2Katie20/06/1984Open 1stKatieOpen 1stClosed 5thOpen 2nd
    3John06/02/1978Open 2ndJohnOpen 2nd
    4Peter31/01/1991Closed 1stPeterClosed 1st
    5Katie20/06/1984Closed 5thSuzieOpen 1st
    6Suzie15/12/1988Open 1st
    7Katie20/06/1984Open 2nd

    Sheet1



    Worksheet Formulas
    CellFormula
    F2=IFERROR(INDEX($C$2:$C$7,AGGREGATE(15,6,(ROW($C$2:$C$7)-ROW($C$2)+1)/($A$2:$A$7=$E2),COLUMNS($A$1:A$1))),"")

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    Quote Originally Posted by Fluff View Post
    Hi & welcome to MrExcel.
    How about

    A B C D E F G H
    1
    2 Katie 20/06/1984 Open 1st Katie Open 1st Closed 5th Open 2nd
    3 John 06/02/1978 Open 2nd John Open 2nd
    4 Peter 31/01/1991 Closed 1st Peter Closed 1st
    5 Katie 20/06/1984 Closed 5th Suzie Open 1st
    6 Suzie 15/12/1988 Open 1st
    7 Katie 20/06/1984 Open 2nd
    Sheet1

    Worksheet Formulas
    Cell Formula
    F2 =IFERROR(INDEX($C$2:$C$7,AGGREGATE(15,6,(ROW($C$2:$C$7)-ROW($C$2)+1)/($A$2:$A$7=$E2),COLUMNS($A$1:A$1))),"")

    Hi and thanks for the response and welcome!

    Tried the formula above and it did not work, does it make any difference if I noted its Excel 2016?

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    As I dont know how to edit posts I wanted to add/Clarify.

    I have a specific list of names/reference numbers, and I need to get the results for those names/references. So I would need it to lookup the reference number to locate it within the first column.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    That's exactly what the formula does.
    Please define "did not work"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    Quote Originally Posted by Fluff View Post
    That's exactly what the formula does.
    Please define "did not work"
    Apologies, It is just showing up blank.

    When I change the reference being matched to the same as the first reference being checked then it works perfectly, anyway else and its blank.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    What is the formula you are using & what are the ranges?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    =IFERROR(INDEX($C$2:$C$15000,AGGREGATE(15,6,(ROW($C$2:$C$15000)-ROW($C$2)+1)/($A$2:$A$15000=$E2),COLUMNS($A$1:A$1))),"")

    Same Formula just increasing the ranges the for the amount of data

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,353
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    In that case if its returning "" it sounds like the value in E2 cannot be found in col A
    Check that you don't have any leading/trailing spaces.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning multiple results match with no duplicates

    Thanks for all the help, it finally worked. Sorting out the data to be ascending got it to click together.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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