3 columns to one Column

[menor59]

Hello all,
I have a head scratching question

in Q6:Q35 I have Data with Blanks (Theres is a formula here but has text) - These Are names
in R6:R36 I have Data with Blanks (Theres is a formula here but has text) - These Are names
in S6:S36 I have Data With blanks (Theres is a formula here but has text) - These Are names

in T6, I want All the data from Q6:S36 Without Blanks, and eliminate Duplicates if possible.

In Q6:S36 these are basically names...Say my Name appears 7 times in Q6:S36. T6 Should only show me once.

This is a macro free workbook, so if possible a formula.

Please Help

 

[jtakw]

Hi,

Can you show a few samples with expected results?

 

[shg]

For example, ...?

 

[menor59]

Sure...
One second...

 

[menor59]

https://www.dropbox.com/s/ivh0yaqgkje1xiv/1.JPG?dl=0

See attached

 

[menor59]

basically all the names in Q6:S36 Keep 1 unique and remove blanks in T

 

[menor59]

here is a Work book i created ...very small..

https://www.dropbox.com/s/a7fte6p7v51ir3m/What i need.xlsx?dl=0

 

[shg]

VBA is easy, I have no suggestion for a formula.

 

[jtakw]

Hi,

I can do this with 3 Helper Columns, then filter blanks, is that something you can work with?

Book1
	Q	R	S	T	U	V	W
6	Steve			Steve			Steve
7
8
9	Steve		John			John	John
10
11
12
13
14			Adam			Adam	Adam
15
16	Adam
17
18
19	Larry			Larry			Larry
20
21		Joe			Joe		Joe
22
23
24	Joe
25
26
27
28		Emily			Emily		Emily
29
30	John
31
32			Adam
33			Steve
34
35
36
Sheet1

Cell Formulas
Range	Formula
T6	=IF(COUNTIF($Q$6:$S6,Q6)=1,Q6,"")
W6	=T6&U6&V6

T6 formula copied down and across to V36.
W6 formula copied down to W36.
Filter Column W for Blanks.
Hide Columns T, U, V and/or Q, R, S if you wish.

 

[menor59]

i think i got it,,

=INDIRECT(TEXT(MIN(IF(($Q$6:$S$35<>"")*(COUNTIF($T$5:T5,$Q$6:$S$35)=0),ROW($6:$35)*100+COLUMN($Q:$S),7^8)),"R0C00"),)&""