I have a excel file with data something like below:

A1 | 13,15,14,16,17

A2 | 13,16

I want the data to look like

A3 | 13, 15, 16, 17

Merge the selected cells data into single cell, avoid duplicate values.

Also, while doing this compare the values to other cells and then exclude those cells.

Say, B1 | 13,14

Then Final Output should not include values from B1

A3 | 15, 16, 17

I tried using Text Join, Unique functions but none of them are working. I found out about XMLconvert function but that is also not supporting excel.

### >Solution :

Since OP has tagged ** Excel Formula** then one could try the following :

• Formula used in cell `A3`

```
=TEXTJOIN(",",,UNIQUE(TOCOL(TEXTSPLIT(TEXTAFTER(","&A1:A2,",",SEQUENCE(,MAX(LEN(A1:A2)-LEN(SUBSTITUTE(A1:A2,",",))+1))),","),3)))
```

Or,

```
=ARRAYTOTEXT(DROP(UNIQUE(REDUCE("",A1:A2,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,","))))),1))
```

With Power Query, you can try using the following `M`

Code:

```
let
Source = Excel.CurrentWorkbook(){[Name="DelimitedTab"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter"),
Column1 = Text.Combine(#"Removed Duplicates"[Column1],",")
in
Column1
```

Or, if the data is limited then one could use the following as well:

```
=ARRAYTOTEXT(UNIQUE(TEXTSPLIT(TEXTJOIN(",",,A1:A2),,",")))
```

Edit:

As per OP’s comment:

Also, while doing this compare the values to other cells and then

exclude those cells. Say, B1 | 13,14 Then Final Output should not

include values from B1 A3 | 15, 16, 17

• Formula used in cell `B1`

```
=LET(α, TEXTSPLIT(A1,","), ARRAYTOTEXT(FILTER(α, ISNA(XMATCH(α,TEXTSPLIT(A2,","))))))
```