I have an excel table where I calculate the total number of machine parts required in a large-scale manufacturing operation. Each row represent a different machine parts and each column after some description of the machine part represents a machine number. The same machine part may or may not be used in multiple machine numbers. I have a "Machine Numbers" column where I would like to return all the Column Names where a value is present. As in, if that machine part is required in the Machine Numbers MN1, MN3 and MN10, it should return "MN1, MN3, MN10". I have posted the table image below for further clarification with some example answers on the red column:
I have done it manually. What formula should I write so that the Machine Numbers column fills up automatically? Please note that this is a TABLE. Let’s assume the table name is "MP". I also use Excel 265.
>Solution :
You can use this formula:
=TEXTJOIN(", ",TRUE, FILTER(MP[[#Headers],[MN1]:[MN5]],MP[@[MN1]:[MN5]]<>""))
The FILTER selects all column headers where there are – per row – values (using Implicit intersection operator @
TEXTJOIN combines the result to a string.