CONCAT question

[LauraJackson]

Hi everyone, 

I need some assistance please with a concatenation. Here is an example of my data: 

Owner 1	Owner 2	Owner 3	Owner 4	Owner 5
Josue	Josue
		Caitlyn
	Svitlana		Svitlana
				Erica

Here is my formula so far, which I will put into an additional column: 

=CONCAT(J2:N2) 

The problem is that sometimes J2 through N2 will have someone's name more than once. Then the concatenation looks like this: 

Owner 1	Owner 2	Owner 3	Owner 4	Owner 5	Owner
Josue	Josue				JosueJosue
		Caitlyn			Caitlyn
	Svitlana		Svitlana		SvitlanaSvitlana
				Erica	Erica

Any thoughts on how to write the formula so that it only shows one value? Note: The name is always the same in a row. 

Thanks!

[#2]

@LauraJackson 

=INDEX(A2:E2,SMALL(IF(A2:E2<>"",COLUMN($A$1:$E$1)),1))

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

Which version of Excel do you work with? If you have Office 365 or Excel 2021 then there are more possibilities than with e.g. Excel 2013.

[#3]

Josue switlana

[#4]

@Quadruple_Pawn this worked perfectly in my fake spreadsheet so thank you! However when I tried to use the same format but for my real spreadsheet I received this message: #REF! or #NUM!

I changed the formula to:

=INDEX(J2:P2,SMALL(IF(J2:P2<>"",COLUMN($J$1:$P$1)),1))

Do I need to update some of the COLUMN part of the formula?
Here is an example of my real spreadsheet:

[#5 · Accepted solution]

@LauraJackson 

=IFERROR(INDEX(J2:P2,SMALL(IF(J2:P2<>"",COLUMN($A$1:$G$1)),1)),"")

In my first reply unfortunately i didn't consider that the data is in columns J to P. The above formula returns the intended results in my sheet. The COLUMN part is COLUMN($A$1:$G$1) because this evaluates to {1.2.3.4.5.6.7}. In range J2:P2 column J is number 1, column K is number 2 and so on.

=IFERROR(INDEX(J2:P2,SMALL(IF(J2:P2<>"",COLUMN($A$1:$G$1)),1)),"")
 

The IFERROR part of the formula (highlighted in red) returns an empty cell if there isn't a name in the row.

[#6]

That worked beautifully. Thank you very much for taking the time to help me with this today.