SOLVED

CONCAT question

Copper Contributor

Hi everyone, 

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

Owner 1Owner 2Owner 3Owner 4Owner 5
JosueJosue   
  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 1Owner 2Owner 3Owner 4Owner 5Owner
JosueJosue   JosueJosue
  Caitlyn  Caitlyn
 Svitlana Svitlana SvitlanaSvitlana
    EricaErica

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

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

concat.JPG

 

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

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

Capture.JPG

best response confirmed by LauraJackson (Copper Contributor)
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.

index small.JPG

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

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

index small.JPG

View solution in original post