Concatenate multiple value and remove duplicate

[mduntley]

Hello. I am working Microsoft Office 2016 and I am trying to get a formula to work. I have a custom formula to use the TEXTJOIN function in Excel 2016, but i want to show it once. I am wondering if someone can help me

Data Warehouse.xlsx
	A	B	C	D
1	510000122	Apple	Value 4
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	510000122859	Apple	Value 3
9
10	My result now
11	510000122	763	Cost Center Cost Center Cost Center & Payor1 Cost Center & Payor2 Cost Center Cost Center	Value 1 Value 1 Value 2 Value 2 Value 2 Value 2
12	510000122	432
13	510000122	859	Apple	Value 3
14
15
16	What I'm Trying to get
17	510000122	763	Cost Center Cost Center & Payor1 Cost Center & Payor2 Cost Center	Value 1 Value 2 Value 2 Value 2
18	510000122	432		Value 4
19	510000122	859	Apple	Value 3
Sheet4

Cell Formulas
Range	Formula
C11:C13	C11	=myvlookup(A11&B11,$A$1:$C$8,2)
D11:D13	D11	=myvlookup(A11&B11,$A$1:$C$8,3)

Here is the custom formula that i found for myvlookup

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

 

[lrobbo314]

How about using Power Query?

XLTemplate1
	A	B	C
1	Number	Name	Value
2	510000122	Apple	Value 4
3	510000122763	Cost Center	Value 1
4	510000122763	Cost Center	Value 1
5	510000122763	Cost Center & Payor1	Value 2
6	510000122763	Cost Center & Payor2	Value 2
7	510000122763	Cost Center	Value 2
8	510000122763	Cost Center	Value 2
9	510000122859	Apple	Value 3
10
11
12
13
14	Number	Name	Value
15	510000122	Apple	Value 4
16	510000122763	Cost Center Cost Center & Payor1 Cost Center & Payor2 Cost Center	Value 1 Value 2 Value 2 Value 2
17	510000122859	Apple	Value 3
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merge = Table.CombineColumns(Source,{"Name", "Value"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Group = Table.Group(Merge, {"Number"}, {{"Data", each _, type table [Number=text, Merged=text]}}),
    Unique = Table.TransformColumns(Group,{{"Data", each List.Distinct(_[Merged])}}),
    Expand = Table.ExpandListColumn(Unique, "Data"),
    Split = Table.SplitColumn(Expand, "Data", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Name", "Value"}),
    ReGroup = Table.Group(Split, {"Number"}, {{"Data", each _, type table [Number=text, Name=nullable text, Value=nullable text]}}),
    Name = Table.AddColumn(ReGroup, "Name", each Text.Combine([Data][Name],Character.FromNumber(10))),
    Value = Table.AddColumn(Name, "Value", each Text.Combine([Data][Value],Character.FromNumber(10)))
in
    Value

 

[mduntley]

What is power Query? is it in excel 2016

 

[mduntley]

It seems that the Power Query will produce a new table, i am trying to update 2 cells in a document.