Search code examples
excelexcel-formulaattributesconcatenationexcel-tables

In Excel, How would I attach, or concatenate single attributes together when there are multiple

I want to attach each attribute to a "name" in a single row. each row could have one or many attributes. If there are more than one attributes, I want to create a new line with the 2nd, 3rd, or 4th attributes attached to the names in the first column. What I am trying to do is take a table that looks like this.

name Attr_1 Attr_2 Attr_3
X 101
Y 201 202
Z 302 303

I want to convert it into a single column that looks like this.

name
X101
Y201
Y202
Z302
Z303

I am looking to do this with Microsoft Power Automate, or python via Pandas data frame eventually. For now, I just need to figure out if what I am trying to do is possible through Excel.

Currently I am using nested IF statements to concatenate an attribute to the "name" column when the first or second attribute is blank. However, this leaves out the possibility of a "name" having multiple attributes.

Many thanks!

Solution

  • Use TOCOL and CONCATENATE unless empty then return error:

    =TOCOL(IF(B2:D4<>"",A2:A4&" - "&B2:D4,NA()),3)

    enter image description here

    Or Using FILTER:

    =FILTER(TOCOL(A2:A4&" - "&B2:D4),TOCOL(B2:D4)<>"")