Unique Concatenate Range, multiple rows, Index Match Mutliple Criteria

[youngja]

1809Hello 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/lookup-value-and-concatenate-all-found-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)),E2: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