Results 1 to 4 of 4

Thread: Unique Concatenate Range, multiple rows, Index Match Mutliple Criteria

  1. #1
    Junior Member
    Join Date
    Oct 2015
    Posts
    2
    Rep Power
    0

    Unique Concatenate Range, multiple rows, Index Match Mutliple Criteria

    Book2.xlsmHello Excel Gurus,

    The problem I am trying to solve is to have a function that allows for concatenated criteria which matches concatenated range, then takes information from the relevant rows and uniquely concatenates the results..

    Program Sub Program Project Manager Client 1 Client 2 Client 3
    Program 1 A 1 John CSIRO
    Program 1 B 1 Bob Uni LLS
    Program 1 B 2 John CMA UNI TAFE
    Program 2 A 1 Alex LLS CSIRO
    Program 2 A 2 Alex CMA LLS TAFE
    Program 3 C 1 Sue Private


    Example :
    Program Program 1
    Sub Program B

    Unique Concatenated Result (MANAGERS): Bob, John
    Unique Concatenated Result CLIENTS Uni, LLS, CMA, TAFE



    Program Program 2
    Sub Program A

    Unique Concatenated Result (MANAGERS): Alex
    Unique Concatenated Result CLIENTS LLS, CSIRO, CMA, TAFE


    Regards

    John
    Last edited by youngja; 10-20-2015 at 04:14 AM. Reason: Added file xlms

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Welcome to board !!!

    See if this helps !

    http://www.excelfox.com/forum/f22/lo...d-results-345/
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Oct 2015
    Posts
    2
    Rep Power
    0
    Hello All

    I found this via google searching and have been able to modify slightly to find empty cells and duplicates.

    Code:
    ' MyConCat
    '
    ' Very simple By Apostolos Goulandris
    Function MyConCat(myDelimiter As String, Avar) As String
        
        Dim b As Variant, Dum As String
        
        If IsMissing(myDelimiter) Then myDelimiter = ""
        
        For Each b In Avar
            
            If b <> 0 Then 'ADDED THIS LINE TO ALLOW FOR BLANK CELLS
                If InStr(1, Dum, b) = 0 Then 'ADDED THIS LINE TO CHECK DUPLICATES
                    Dum = IIf(Len(b) > 0, Dum & myDelimiter & b, Dum)
                End If
            End If
        Next
        
        MyConCat = IIf(Len(myDelimiter) > 0, Mid(Dum, Len(myDelimiter) + 1, Len(Dum)), Dum)
    
    End Function

    In the previous post, i needed to concatenate 2 cells and compare against 2 column ranges, then return the concatenated unique string... example how I tried in is

    =MyConCat(",",IF(((A2:A7)&(B2:B7))=((C11)&(C12)),E 2:G7,""))

    This seemed to work fine for me. Any suggestions or improvements most welcome.

    Please acknowledge Apostolos Goulandris as he wrote the initial script
    Last edited by bakerman; 02-12-2016 at 06:31 AM. Reason: Code tags added

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Code:
    Sub M_snb()
       sn = Sheet1.Cells(1).CurrentRegion
       
       With CreateObject("scripting.dictionary")
          For j = 2 To UBound(sn)
            sp = Array(sn(j, 4), Replace(Trim(Join(Array(sn(j, 5), sn(j, 6), sn(j, 7)))), " ", ", "))
            If .exists(sn(j, 1) & sn(j, 2)) Then
               sq = .Item(sn(j, 1) & sn(j, 2))
               sp = Array(sq(0) & ", " & sp(0), sq(1) & ", " & sp(1))
            End If
            .Item(sn(j, 1) & sn(j, 2)) = sp
          Next
          
           Sheet1.Cells(30, 1).Resize(.Count, 2) = Application.Index(.items, 0, 0)
        End With
    End Sub

Similar Threads

  1. Upgrade to LookUpConcat UDF: Search Multiple Criteria and concatenate.
    By DJE in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 05-23-2018, 12:18 AM
  2. Replies: 1
    Last Post: 02-13-2015, 07:30 AM
  3. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  4. Replies: 4
    Last Post: 04-24-2013, 10:04 AM
  5. Replies: 4
    Last Post: 08-11-2012, 10:50 PM

Posting Permissions

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