SOLVED

CONCAT question

Copper Contributor

CONCAT question

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!

5 Replies

Re: CONCAT question

``=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.

Josue switlana

Re: CONCAT question

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

best response confirmed by LauraJackson (Copper Contributor)
Solution

Re: CONCAT question

``=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.

Re: CONCAT question

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

Accepted Solutions
best response confirmed by LauraJackson (Copper Contributor)
Solution

Re: CONCAT question

``=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.