Unique Concatenate Range, multiple rows, Index Match Mutliple Criteria

[youngja]

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

[Admin]

Hi

Welcome to board !!!

See if this helps !

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

[youngja]

Hello All

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

' 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

[snb]

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