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

Question

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!

Answer 1

Use TOCOL and CONCATENATE unless empty then return error:

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

Or Using FILTER:

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

Answer 2

With Power Query:

Right click on "name" column, Unpivot other columns

Remove "Attribute" column

Merge "name" and "Value" columns