Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 27

Thread: Retrieve list of values from array which has duplicates

  1. #1
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365

    Smile Retrieve list of values from array which has duplicates



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

    Hi All
    Hope you are well, I've been stuck on this problem for a while now, I've been able to figure out half of it but not the rest. Basically I have a data sheet that lists categories, levels and titles, and I'm trying to get a formula that will populate a grid of results based on the category and level of the job titles. There are duplicates in this data set so I only want to retrieve 1 of the duplicates and all unique values.

    Also I'm looking for the formula to stop when there are no more results, and if a new result is added, the formula should pick this up and populate on the grid.

    Lastly something more advance, I'm interested to find out from all the experts out there if its possible to list these results one line after another but in 1 cell? In the Grid tab I've added 2 examples of what the final grid should look like

    Below is a sample of my data

    sample.xlsx

    Thank you so much in advance for your help and taking the time to look at my query

    Regards
    TTTT

  2. #2
    Acolyte Azumi's Avatar
    Join Date
    Jan 2014
    Location
    Indonesia
    Posts
    30
    Articles
    0
    Excel Version
    2010
    Hope this helps, just works for your sample file not your real workbook
    Attached Files Attached Files

  3. #3
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Hi Azumi,
    That is pretty cool thank you, I tried replicating your formula in my master and it works well, however it does not produce 1 of the duplicates for some... (I realised I forgot to add some duplicate job titles in the mix in my sample data set), also it does not appear to list them 1 title per line as it has so neatly done in the sample you've worked on. I've had to use wrap text and that does not nearly list?

  4. #4
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Hi Azumi,

    I've attached a revised spreadsheet which include duplicates (in this one you'll see Administration Assistant appear twice under group Administration and Level 2, so in the grid I need to to only appear once

    Your help or anyone else's help would be greatly appreciated
    Attached Files Attached Files

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    Try adding this helper column to D2 of List sheet (this will help with finding unique entries):

    =A2&"_"&COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)

    copied down

    Then change Azumi's ARRAY* formula to:

    =SUBSTITUTE(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),1)),"")&IF(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),2)),"")="","","| "&IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),2)),""))&IF(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),3)),"")="","","| "&IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),3)),""))&IF(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),4)),"")="","","| "&IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),4)),""))&IF(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),5)),"")="","","| "&IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),5)),"")),"| ",CHAR(10))

    * Array formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down and across

    Format the result cells by going to Alignment tab and selecting to "Wrap text". You may have to autofit columns and/or rows afterward.


  6. #6
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by NBVC View Post
    Try adding this helper column to D2 of List sheet (this will help with finding unique entries):

    =A2&"_"&COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)

    copied down

    Then change Azumi's ARRAY* formula to:

    =SUBSTITUTE(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),1)),"")&IF(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),2)),"")="","","| "&IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),2)),""))&IF(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),3)),"")="","","| "&IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),3)),""))&IF(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),4)),"")="","","| "&IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),4)),""))&IF(IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),5)),"")="","","| "&IFERROR(INDEX(List!$C$2:$C$12,SMALL(IF((List!$B$2:$B$12=$A4)*(List!$D$2:$D$12=B$3&"_"&1),ROW(List!$C$2:$C$12)-ROW(List!$C$2)+1),5)),"")),"| ",CHAR(10))

    * Array formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down and across

    Format the result cells by going to Alignment tab and selecting to "Wrap text". You may have to autofit columns and/or rows afterward.
    Hi NBVC
    That is fantastic thank you! it works, another caveat sorry... is there a way to highlight duplicate position titles in the grid if they appear in different boxes?
    • eg. Position title: Administration Assistant - Appears under same category (eg. Administration) just different levels (eg. level 1, 2 and 3.3)
    • eg. Analyst, Systems - appears in the same level (eg. 3.2) just different category (eg. Administration and Development)
    • eg. Lead, Change - appears in different category and level (Administration and Development and level 3.2 and 4.2)
    • Sample attached - sample_3.xlsx

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    After selecting B4:C10 try this conditional format formula:

    =SUMPRODUCT(((ISNUMBER(SEARCH(List!$C$2:$C$15,B4)))*(RIGHT(List!$D$2:$D$15)="1")))>1


  8. #8
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by NBVC View Post
    After selecting B4:C10 try this conditional format formula:

    =SUMPRODUCT(((ISNUMBER(SEARCH(List!$C$2:$C$15,B4)))*(RIGHT(List!$D$2:$D$15)="1")))>1
    Hi NBVC
    That is awesome thank you, one thing on that, it shades everything that had a duplicate, what about if for those examples we dont want to remove duplicate but it could be an error in category selected or level for that job title, so to correct the conditional formatting we either correct the category or level to match with the other is so in the grid it does not appear twice and therefore conditional formatting should not shade those boxes?

    I tested your formula and it works great if we remove the duplicate/incorrect entry completely from the list then the boxes unshade? But what if we want to retain the duplicates and just correct the field that was incorrect? (the data set is pretty much a headcount listing, so despite the job titles often matching, theoretically so should the category and level for that job title, so we would want to identify the anomalies to correct them not remove them completely

    I hope that helps explain

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,509
    Articles
    0
    Excel Version
    Excel 2016
    im not sure I understand.

    You know you can only conditionally colour the whole cells, so as long as one of the items in the cell matches the conditions, the cell will get coloured.


  10. #10
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Hi NBVC,

    Thank you, currently it conditionally colours the whole cell when there is more than 1 value in the list, whereas I'm looking to have the cell conditionally coloured if the value appears on the grid twice (not on the list), so the position title can appear on the list view multiple times, but it if appears on the grid twice (eg. either under different categories or in different levels) then conditionally colour

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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