Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 27

Thread: Retrieve list of values from array which has duplicates

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


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

    Hi NBVC
    I've attached a more substantive data set and adjusted your formula for the grid to this one, so I've noticed that your formula only goes down 5 values, is there a way to adjust the formula to take into consideration unlimited number of values?

    The pivot tab might help explain the conditional formatting I'm trying to develop in the grid so I've shaded some position titles that appear under more than 1 level, so in grid view, I'd cells to conditionally colour when these position titles appear (to flag that the title appears in the grid more than once as illustrated in the pivot view off the data tab)

    I hope that makes sense and the updated data set is easiler to work with

    Sample_5.xlsx

  2. #12
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Ok. Please see attached for proposal.

    To adjust for larger number of returns, we will need to implement a vba function.

    I like to use this one:

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    Now add yet another helper Lookup column in the Data sheet after column A, with formula in B2 (this will be for the Conditional formatting):

    Then apply this new formula to B4 of the Grid sheet, which uses the vba aconcat() function:

    =SUBSTITUTE(SUBSTITUTE(TRIM(aconcat(IF((Data[[Lookup]:[Lookup]]=B$3&"_1")*(Data[[Level]:[Level]]=$A4),SUBSTITUTE(Data[[Job Title]:[Job Title]]," ","|"),"")," "))," ",CHAR(10)),"|"," ")

    Confirmed with CTRL+SHIFT+ENTER and copied down and across the table.

    Then for the conditional format I created to Dynamic Named Ranges... one called "Lookup2" and one called "Job_Title" referencing the corresponding columns in your Data sheet.

    Then apply conditional format formula

    =SUMPRODUCT(((ISNUMBER(SEARCH(Job_Title,B4)))*(RIGHT(Lookup2)="1")))>1

    Note, due to the array formula covering large number of cells and larger references, you may experience some noticeable lag....
    Attached Files Attached Files


  3. #13
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,593
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by NBVC View Post
    To adjust for larger number of returns, we will need to implement a vba function.
    This one's quite light on resources.
    Click the button on the Grid sheet of the attached.
    I struggled with keeping all data visible, (Technical category, level 3.3 has 29 results (cell G8)); max row height meant that I had to reduce the font size, but Excel loves to change things for you so I had to code it in.
    At the moment, the job titles highlighted in each cell are those where the job title exists at another level - categories are not taken into account at all. That can be changed.
    There's a bit of hard-coding in there but it can be made more general.
    I added a category (Executive) at Q3.
    If this is path you want to go down we can continue…
    Attached Files Attached Files

  4. #14
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Hi NBVC,
    That is amazing thank you! I figured out what is happening with the conditional formatting. it is picking up all the duplicates that have the count figure of 1, so its counting 1, 11, 12, 13 etc, is there a way to adjust this so that it only counts the first number of "1" and not figures of 10?

    I tried adding a "." at the end of the count formula which worked on the grid view but now the conditional formatting does not pick up the duplicate (eg. I changed say Manager, Project Development to appear in level 4 and 5, but the conditional formatting does not pick this up) - I tried to adjust the conditional formatting formula to reflect the "." added but no luck?

  5. #15
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Omg p45cal, that is pretty amazing and is exactly what I am looking for, especially the duplication indicator of which position titles appear in the grid more than once! Thank you so much

    NBVC - thank you also for your help! your tool is great as the grid updates on its own!

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

    Thank You!

    Quote Originally Posted by p45cal View Post
    This one's quite light on resources.
    Click the button on the Grid sheet of the attached.
    I struggled with keeping all data visible, (Technical category, level 3.3 has 29 results (cell G8)); max row height meant that I had to reduce the font size, but Excel loves to change things for you so I had to code it in.
    At the moment, the job titles highlighted in each cell are those where the job title exists at another level - categories are not taken into account at all. That can be changed.
    There's a bit of hard-coding in there but it can be made more general.
    I added a category (Executive) at Q3.
    If this is path you want to go down we can continue…
    Hi p45cal,
    Thank you so much, sorry i did not see your post before responding to NBVC. This is the definitely what I am after. I tried to throw in some of my original data and noticed that it does not deal too well with the VBA coding
    So I've made adjustments to your document to include additional columns of data that would be required in the final document, wondering if you could work your magic and adjust the coding? (I'm not savvy with VBA unfortunately to figure out what to change)

    Reference to Cell G8 - would this still be an issue if position titles are added ontop of this increasing the # of results to over 29 values? or does your hard coding eliminate the issue?

    Your help on this is truly appreciated
    ExelGuru9201Sample_6.xlsm

  7. #17
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,593
    Articles
    0
    Excel Version
    365
    In haste, 'cos it's my bedtime, try:
    Code:
    Sub blah()
    Dim rngCellsToCheck  As Range
    Set mydictionary = CreateObject("Scripting.Dictionary")    'this will contain dictionaries of each category-level job titles
    Set JobTitleDic = CreateObject("Scripting.Dictionary")    'this will contain Job titles which exist at more than one level
    Set CategoryHdrs = Sheets("Grid").Range("B3:Q3")    'hard coded
    Set LevelHdrs = Sheets("Grid").Range("A4:A12")    'hard coded
    CatLookup = CategoryHdrs.Value
    LevelLookup = LevelHdrs.Value
    For i = 1 To UBound(LevelLookup):  LevelLookup(i, 1) = CStr(LevelLookup(i, 1)): Next i
    Set Tbl = Range("data").ListObject
    'SourceData = Range("data").Value
    SourceData = Tbl.DataBodyRange.Value
    With Tbl.HeaderRowRange
      CatColm = Application.Match("Category", .Value, 0)
      LevelColm = Application.Match("Level", .Value, 0)
      JobTitleColm = Application.Match("Job Title", .Value, 0)
    End With
    For rw = 1 To UBound(SourceData)
      DicName = SourceData(rw, CatColm) & "|" & SourceData(rw, LevelColm)    '3 is cat,4 is level,5 is jobtitle.
      If Not mydictionary.exists(DicName) Then
        Set NewDic = CreateObject("Scripting.Dictionary")
        mydictionary.Add DicName, NewDic
      End If
      If Not mydictionary(DicName).exists(SourceData(rw, JobTitleColm)) Then mydictionary(DicName).Add SourceData(rw, JobTitleColm), SourceData(rw, JobTitleColm)
      
      'create list of job titles which exist at more than one level:
      If Not JobTitleDic.exists(SourceData(rw, JobTitleColm)) Then
        For ro = rw + 1 To UBound(SourceData)
          If SourceData(ro, JobTitleColm) = SourceData(rw, JobTitleColm) Then
            If SourceData(ro, LevelColm) <> SourceData(rw, LevelColm) Then
              JobTitleDic.Add SourceData(rw, JobTitleColm), SourceData(rw, JobTitleColm)
              Exit For
            End If
          End If
        Next ro
      End If
      
    Next rw
    Set rngDestn = Intersect(CategoryHdrs.EntireColumn, LevelHdrs.EntireRow)    'where the results will go
    rngDestn.Clear
    DestVals = rngDestn.Value
    For Each DicName In mydictionary.keys
      x = Split(DicName, "|")
      Z = Join(mydictionary(DicName).keys, vbLf)
      DestVals(Application.Match(x(1), LevelLookup, 0), Application.Match(x(0), CatLookup, 0)) = Z
    Next DicName
    
    With rngDestn    'struggling a bit with font sizes, veretical alignment, both being changed by Excel without asking..
      .EntireColumn.ColumnWidth = 110    'much too wide to prevent word wrapping of single job titles
      .Value = DestVals
      .Font.Size = 9
      .EntireColumn.AutoFit
    End With
    'Highlight the multi-level job titles (this was more difficult that I thought it would be!):
    Set rngCellsToCheck = rngDestn.SpecialCells(xlCellTypeConstants, 2)
    If Not rngCellsToCheck Is Nothing Then
      For Each cll In rngCellsToCheck.Cells
        cvsplit = Split(cll.Value, vbLf)
        For Each jt In JobTitleDic.keys
          pop = Application.Match(jt, cvsplit, 0)
          If Not IsError(pop) Then
            posn = pop
            For j = 0 To pop - 2
              posn = posn + Len(cvsplit(j))
            Next j
            cll.Characters(Start:=posn, Length:=Len(jt)).Font.Color = -16776961
          End If
        Next jt
      Next cll
    End If
    End Sub
    Quote Originally Posted by TTTT View Post
    especially the duplication indicator of which position titles appear in the grid more than once!
    Be aware that this is NOT just those titles appearing more than once; it's those appearing at more than one level. If there are ten similar job titles, but they're all at the same level, it won't be highlighted.
    I will need to look more closely at your criteria at the end of your msg#6.

    Quote Originally Posted by TTTT View Post
    Reference to Cell G8 - would this still be an issue if position titles are added ontop of this increasing the # of results to over 29 values? or does your hard coding eliminate the issue?
    Not necessarily. I've just reduced the font size in the code to a size that accommodates the 29 results. If there are 40 results, then I could reduec the size of the font to unreadable levels, but it would probably better to go looking for the full cells and reducing their font size until you can see that all results are visible.
    If you're going to have a lot of such cells, then it might be worth considering putting more than one job title per line (at the moment, there's a linefeed between job titles (and it's this multiline property which prevents shrink-to-fit from working and forces word wrap in each cell)), but that also raises a readability problem in that you already use commas within a job title, so we'd have to use something else to identify them as different titles if there are more than one to a line.

    Note also that it might be worth checking the job titles. For example there a job title Manager Asset, and another one Manager Assets, are they actually the same job title? I remember there being others but I've forgotten which they were.
    Last edited by p45cal; 2018-07-26 at 01:20 AM.

  8. #18
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    In haste, 'cos it's my bedtime, try:
    Code:
    Sub blah()
    Dim rngCellsToCheck  As Range <snip>

    Hi p45cal
    That is amazing thank you, the VBA coding works like a treat!

    Originally Posted by TTTT
    especially the duplication indicator of which position titles appear in the grid more than once!



    Quote Originally Posted by p45cal View Post
    Be aware that this is NOT just those titles appearing more than once; it's those appearing at more than one level. If there are ten similar job titles, but they're all at the same level, it won't be highlighted.
    I will need to look more closely at your criteria at the end of your msg#6.
    Thats what I we need, titles that appear in more than one level more and/or more than one category which I can see it does.

    Quote Originally Posted by p45cal View Post
    Not necessarily. I've just reduced the font size in the code to a size that accommodates the 29 results. If there are 40 results, then I could reduec the size of the font to unreadable levels, but it would probably better to go looking for the full cells and reducing their font size until you can see that all results are visible.
    If you're going to have a lot of such cells, then it might be worth considering putting more than one job title per line (at the moment, there's a linefeed between job titles (and it's this multiline property which prevents shrink-to-fit from working and forces word wrap in each cell)), but that also raises a readability problem in that you already use commas within a job title, so we'd have to use something else to identify them as different titles if there are more than one to a line.
    It shouldnt be an issue for now, but I'd imagine as the business grows there may be additional titles which will grow the list

    Quote Originally Posted by p45cal View Post
    Note also that it might be worth checking the job titles. For example there a job title Manager Asset, and another one Manager Assets, are they actually the same job title? I remember there being others but I've forgotten which they were.
    I'll take note of that but at the moment I think we will treat it as two different positions
    Last edited by p45cal; 2018-07-26 at 10:21 AM. Reason: trimmed down wholesale quoting

  9. #19
    Acolyte TTTT's Avatar
    Join Date
    Jul 2018
    Posts
    20
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    Be aware that this is NOT just those titles appearing more than once; it's those appearing at more than one level. If there are ten similar job titles, but they're all at the same level, it won't be highlighted.
    I will need to look more closely at your criteria at the end of your msg#6
    Hi p45cal - I checked in with my colleague and the colour coding should be: if the job title appears across more than 1 category (so they can appear under more than 1 level, but would like it to be visibly identifiable if they across categories - would it be possible to achieve this via VBA? and if so how could we do this?

  10. #20
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,593
    Articles
    0
    Excel Version
    365
    Change:
    Code:
    If SourceData(ro, LevelColm) <> SourceData(rw, LevelColm) Then
    to:
    Code:
    If (SourceData(ro, LevelColm) <> SourceData(rw, LevelColm)) Or (SourceData(ro, CatColm) <> SourceData(rw, CatColm)) Then
    to highlight Job Titles which exist on multiple Levels and/or multiple Categories.

Page 2 of 3 FirstFirst 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
  •