Horizontal Unique Formula Needed

[BrettOlbrys1]

I need to look horizontally across multiple columns, and return the unique values horizontally as well. For example:

If I look across A2:F2, the unique values would appear in H2:M2. There will only be the six columns of data (A - F), and the results will always appear in the possible six results columns (H - M).

How do I accomplish this?

Thanks

Brett

 

[KRice]

Try this:

MrExcel_20220315.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name 1	Name 2	Name 3	Name 4	Name 5	Name 6
2	Brett	George	Tom	Brett	Brett	Joe		Brett	George	Tom	Joe
3	George	Brett	Tom	Greg	Greg			George	Brett	Tom	Greg
4	Greg	George	Brett	Joe	Rich	Tom		Greg	George	Brett	Joe	Rich	Tom
Sheet4

Cell Formulas
Range	Formula
H2:K2,H4:M4,H3:L3	H2	=UNIQUE(A2:F2,TRUE)&""
Dynamic array formulas.

 

[Peter_SSs]

I think better to eliminate the blanks rather than having the null string value in cell L3 in the above results. This is particularly so if the list (H3#) is to be used in some other operation later.

22 03 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name 1	Name 2	Name 3	Name 4	Name 5	Name 6
2	Brett	George	Tom	Brett	Brett	Joe		Brett	George	Tom	Joe
3	George	Brett	Tom	Greg	Greg			George	Brett	Tom	Greg
4	Greg	George	Brett	Joe	Rich	Tom		Greg	George	Brett	Joe	Rich	Tom
Unique

Cell Formulas
Range	Formula
H4:M4,H2:K3	H2	=UNIQUE(FILTER(A2:F2,A2:F2<>""),TRUE)
Dynamic array formulas.

 

[BrettOlbrys1]

Thank you, both formulas work and will help me. Now, let me throw one more wrinkle into this, which I should have included in the first post:

The cells in A to F are populated by a lookup and in some case, an actual name may not appear in A:F because that lookup may be blank, and when it is blank, I have populated those blank cells with "UNKNOWN". So applying the formula above, the actual table would look like the new image.

What I would like to happen is that anytime there is an "UNKNOWN" with other names, such as the highlighted UNKNOWN, I want that UNKNOWN to not appear. The only time an UNKNOWN would appear is when it is the only result of the dynamic result.

How do I ignore "UNKNOWN" when it is with other results?

 

[Peter_SSs]

22 03 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name 1	Name 2	Name 3	Name 4	Name 5	Name 6
2	Brett	George	Tom	Brett	Brett	Joe		Brett	George	Tom	Joe
3	George	Brett	Tom	Greg	Greg	UNKNOWN		George	Brett	Tom	Greg
4	Greg	George	Brett	Joe	Rich	Tom		Greg	George	Brett	Joe	Rich	Tom
Unique

Cell Formulas
Range	Formula
H4:M4,H2:K3	H2	=UNIQUE(FILTER(A2:F2,(A2:F2<>"")*(A2:F2<>"UNKNOWN")),TRUE)
Dynamic array formulas.

.. or if there will definitely be no blanks in A:F then

22 03 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name 1	Name 2	Name 3	Name 4	Name 5	Name 6
2	Brett	George	Tom	Brett	Brett	Joe		Brett	George	Tom	Joe
3	George	Brett	Tom	Greg	Greg	UNKNOWN		George	Brett	Tom	Greg
4	Greg	George	Brett	Joe	Rich	Tom		Greg	George	Brett	Joe	Rich	Tom
Unique

Cell Formulas
Range	Formula
H4:M4,H2:K3	H2	=UNIQUE(FILTER(A2:F2,A2:F2<>"UNKNOWN"),TRUE)
Dynamic array formulas.

 

[Fluff]

Another option if you want the "Unknown" to appear on that last row

=UNIQUE(FILTER(A2:F2,A2:F2<>"UNKNOWN","UNKNOWN"),TRUE)

 

[BrettOlbrys1]

Thanks, but I only cut the image off at row 5, there are 10,000 rows of data and the UNKNOWN that you see in row 5 could be throughout the entire dataset, so it's not the last row. The only time UNKNOWN should appear is when every cell it compared in the row is UNKNOWN, like I show in Row 5.

 

[Fluff]

That is what the formula I posted does.

 

[Peter_SSs]

Another option if you want the "Unknown" to appear on that last row

=UNIQUE(FILTER(A2:F2,A2:F2<>"UNKNOWN","UNKNOWN"),TRUE)

Ah, I hadn't caught on to that last row requirement. ?

 

[BrettOlbrys1]

Another option if you want the "Unknown" to appear on that last row

=UNIQUE(FILTER(A2:F2,A2:F2<>"UNKNOWN","UNKNOWN"),TRUE)

Thanks Fluff.

Brett