Text join and unique

[mduntley]

Hello, I am trying to use the unique value and the text join function. But I am having some trouble.

Data Warehouse.xlsx
	A	B	C	D
1	510000122763	Cost Center	Value 1
2	510000122763	Cost Center	Value 1
3	510000122763	Cost Center	Value 1
4	510000122763	Cost Center & Payor1	Value 2
5	510000122763	Cost Center & Payor2	Value 2
6	510000122763	Cost Center	Value 2
7	510000122763	Cost Center	Value 2
8
9	What I am getting for my Result
10	510000122	763	Cost Center Cost Center & Payor1 Cost Center & Payor2	value 1 Value 2
11			=TEXTJOIN(CHAR(10),1,UNIQUE(IF(A9&B9=A1:A7,B1:B7,"")))	=TEXTJOIN(CHAR(10),1,UNIQUE(IF(A9&B9=A1:A7,C1:C7,"")))
12
13	What I'm trying to get
14	510000122	763	Cost Center Cost Center Cost Center & Payor1 Cost Center & Payor2	Value 1 Value 2 Value 2 Value 2
Sheet4

Thank you

 

[mduntley]

In that case try adding the bit in red

=TEXTJOIN(CHAR(10),,INDEX(UNIQUE(FILTER($B$1:$C$8,A10&B10=$A$1:$A$8,FILTER($B$1:$C$8,A10&""=$A$1:$A$8))),,1))

Fluff, do you think you can turn this into a formula that can work in Excel 2016? I have this fomula as a custom formula but i want to have it show it only once instead of repeating

Function myvlookup(pValue As String, pWorkRng As Range, pIndex As Long)
For Each rng In pWorkRng
    If rng = pValue Then
        xResult = xResult & vbNewLine & rng.Offset(0, pIndex - 1)
    End If
Next
myvlookup = Mid(xResult, 3)
End Function

 

[Fluff]

Fraid not as 2016 doesn't have the textjoin function.

 

[mduntley]

Fraid not as 2016 doesn't have the textjoin function.

I have the text join function formulated in my custom formula.

 

[Fluff]

i want to have it show it only once instead of repeating

Originally you wanted the values repeated. But as this is now a different question, you will need to start a new thread. Thanks