Distinct Values

[BILLYBOB1972]

Is there a way to display the distinct values from a column in a row using a formula

AAAA
AAAA
BBBB
AAAA
CCCC
AAAA
DDDD
CCCC

From this list the values would appear in a row like below

AAAA

BBBB

CCCC

DDDD

I'm using Excel 2016 and tried to find the answer but can only get the distinct values in another column format. I could remove duplicates and transpose the paste values but trying not to have to go down this route.

 

[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.xlsm
	A	B	C	D	E	F	G
1
2	AAAA		AAAA	BBBB	CCCC	DDDD
3	AAAA
4	BBBB
5	AAAA
6	CCCC
7	AAAA
8	DDDD
9	CCCC
10
Report

Cell Formulas
Range	Formula
C2:G2	C2	=IFNA(INDEX($A$2:$A$9,MATCH(0,COUNTIFS($B2:B2,$A$2:$A$9),0)),"")

This may need to be confirmed with Ctrl Shift Enter.

 

[BILLYBOB1972]

Thanks Fluff. Just what I was after.

 

[Fluff]

Glad to help & thanks for the feedback.