Dynamic Array - Remove Subsequent Instances

[Cubist]

Suppose I have an array in column A, I want to remove subsequent repeated elements; only keeping the first. For subsequent instances, replace with "". The result should be an array. UNIQUE() doesn't work because I want to keep the same index.

Book1
	A	B
1	From	To
2	A	A
3
4	B	B
5
6	B
7	B
8	C	C
9	C
10
11
12
13	C
Sheet6

Cell Formulas
Range	Formula
A2:A13	A2	={"A";" ";"B";" ";"B";"B";"C";"C";" ";"";"";"C"}
B2:B13	B2	={"A";" ";"B";" ";"";"";"C";"";" ";"";"";""}
Dynamic array formulas.

 

[Alex Blakenburg]

As far as I can tell, this works:

=IF((XMATCH(A2#,A2#,0,1)+ROW()-1)=ROW(A2#),A2#,"")

 

[Cubist]

It's working so far. I'll need to test it on a few other data sets.

 

[shaowu459]

another solution for your reference:

工作簿1
	A	B	C
1	From	To
2	A	A
3
4	B	B
5
6	B
7	B
8	C	C
9	C
10
11
12
13	C
14
Sheet1

Cell Formulas
Range	Formula
A2:A13	A2	={"A";" ";"B";" ";"B";"B";"C";"C";" ";"";"";"C"}
B2:B13	B2	=MAP(A2#,LAMBDA(x,REPT(x,COUNTIF(A2:x,x)=1)))
Dynamic array formulas.

 

[ISY]

Hi

Option to use the reference..
=MAP(A2#,LAMBDA(x,REPT(x,COUNTIF((@A2#):x,x)=1)))

 

[Cubist]

Thanks for all the options. They all seem to work on my test data. I'll give Alex credit for the first solution.

 

[Alex Blakenburg]

Haha. Thanks for testing them all and giving us all feedback.