CONCAT question

Occasional Contributor

Hi everyone, 

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

Owner 1Owner 2Owner 3Owner 4Owner 5
 Svitlana Svitlana 


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


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


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

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. 


5 Replies



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.

Josue switlana

@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:


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


best response confirmed by LauraJackson (Occasional Contributor)



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 {}. In range J2:P2 column J is number 1, column K is number 2 and so on.



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

index small.JPG

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