A formula to solve my Code problem

Hi all,

I have a table like in the picture

And also I tried to copy paste below

Pro_code are my products and on the rows starting from A to E are my raw materials

At new column I want to create an unique code that will show which RM's that my products are using

For instance i distinct counted rm's for Product 1 and the product using RM A and RM C so new column should make a code named A_C

What kind of formula should i proceed?

PRO_CODE A B C D E New Column
2.39.06.030 1 1 A_C
2.39.06.031 1 1 1 A_B_C
2.39.06.032 1 C
2.39.06.033 1 1 B_C
2.39.06.034 1 E
2.39.06.036 1 C




Capture.PNG

If it is as simple as 5 columns then something like

=SUBSTITUTE(TRIM(IF(N(B2),B$1&" ","")&IF(N(C2),C$1&" ","")&IF(N(D2),D$1&" ","")&IF(N(E2),E$1&" ","")&IF(N(F2),F$1&" ",""))," ","_")

If, in reality you have a lot more columns to work with then you might be better to look at a UDF like ACONCAT

I think that office 365 includes a similar function, but other versions do not, you would need to add it as a custom function.

Works perfectly,

Thank you so much