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

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	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	510000122	763	Cost Center Cost Center & Payor1 Cost Center & Payor2 Cost Center	Value 1 Value 2 Value 2 Value 2
Master

Cell Formulas
Range	Formula
C9	C9	=TEXTJOIN(CHAR(10),,INDEX(UNIQUE(IF(A9&B9=A1:A7,B1:C7,"")),,1))
D9	D9	=TEXTJOIN(CHAR(10),,INDEX(UNIQUE(IF(A9&B9=A1:A7,B1:C7,"")),,2))

 

[Eric W]

Try:

=UNIQUE(FILTER(B1:C7,A1:A7=A10&B10))

Edit: Missed the TEXTJOIN requirement.

 

[mduntley]

Try:

=UNIQUE(FILTER(B1:C7,A1:A7=A10&B10))

That will not work as i need it all in one cell.

Thanks for the answer Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[mduntley]

Glad we could help & thanks for the feedback.

I have another question. I am trying to find a easy way to solve this. I am trying to pull in criteria if one option does not work. So in the example below I want Value 4 in the very last row

Data Warehouse.xlsx
	A	B	C	D
1	510000122763	Cost Center	value 1
2	510000122763	Cost Center	value 1
3	510000122763	Cost Center & Payor1	Value 2
4	510000122859	Apple	Value 3
5	510000122	Apple	Value 4
6	510000122763	Cost Center & Payor2	Value 2
7	510000122763	Cost Center	Value 2
8	510000122763	Cost Center	Value 2
9
10	510000122	763	Cost Center Cost Center & Payor1 Cost Center & Payor2 Cost Center	value 1 Value 2 Value 2 Value 2
11	510000122	859	Apple	Value 3
12	510000122	432
Sheet4

Cell Formulas
Range	Formula
C10:C12	C10	=_xlfn.TEXTJOIN(CHAR(10),,INDEX(_xlfn.UNIQUE(IF(A10&B10=A1:A8,B1:C8,"")),,1))
D10:D12	D10	=_xlfn.TEXTJOIN(CHAR(10),,INDEX(_xlfn.UNIQUE(IF(A10&B10=A1:A8,B1:C8,"")),,2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1	510000122763	Cost Center	value 1
2	510000122763	Cost Center	value 1
3	510000122763	Cost Center & Payor1	Value 2
4	510000122859	Apple	Value 3
5	510000122	Apple	Value 4
6	510000122763	Cost Center & Payor2	Value 2
7	510000122763	Cost Center	Value 2
8	510000122763	Cost Center	Value 2
9
10	510000122	763	Cost Center Cost Center & Payor1 Cost Center & Payor2 Cost Center	value 1 Value 2 Value 2 Value 2
11	510000122	859	Apple	Value 3
12	510000122	432	Apple	Value 4
Master

Cell Formulas
Range	Formula
C10:C12	C10	=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))
D10:D12	D10	=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))),,2))

 

[mduntley]

How about

+Fluff 1.xlsm
	A	B	C	D
1	510000122763	Cost Center	value 1
2	510000122763	Cost Center	value 1
3	510000122763	Cost Center & Payor1	Value 2
4	510000122859	Apple	Value 3
5	510000122	Apple	Value 4
6	510000122763	Cost Center & Payor2	Value 2
7	510000122763	Cost Center	Value 2
8	510000122763	Cost Center	Value 2
9
10	510000122	763	Cost Center Cost Center & Payor1 Cost Center & Payor2 Cost Center	value 1 Value 2 Value 2 Value 2
11	510000122	859	Apple	Value 3
12	510000122	432	Apple	Value 4
Master

Cell Formulas
Range	Formula
C10:C12	C10	=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))
D10:D12	D10	=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))),,2))

in cell C12 and D12, i am getting #CALC!

 

[mduntley]

My orginization does not allow us to use Office 365 onto our desktop, but we can do it on the web.

 

[Fluff]

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))