0

Please see image below:

Before

I have an excel sheet with 500+ products. The numbers represent various sizes available (Y=Yes). So for example, 'Product D' is available in 3mm and 12mm. Is there a formula that will take the 'Y' from the columns and convert it into mm in the sizes column, separated by a comma and space? See example below:

After

user2538833
  • 197
  • 2
  • 14
  • See [Concatenate top row cells if column below has 1](http://stackoverflow.com/questions/28679758/concatenate-top-row-cells-if-column-below-has-1/28680713#28680713). It should be a small matter to add *mm* to the string concatenation. –  Jul 23 '15 at 03:52

2 Answers2

0
K2 = If(B2="y",B$1 & "mm, ","") & If(C2="y",C$1 & "mm, ","") & If(D2="y",D$1 & "mm, ","") & If(E2="y",E$1 & "mm, ","") & If(F2="y",F$1 & "mm, ","") & If(G2="y",G$1 & "mm, ","") & If(H2="y",H$1 & "mm, ","") & If(I2="y",I$1 & "mm, ","") & If(J2="y",J$1 & "mm, ","")

You will get a trailing comma and space, so you may want to wrap the function in:

=if(len(<long formula>)>0,left(<long formula>,len(<long formula>)-2),"")
Gordon K
  • 824
  • 1
  • 8
  • 19
0

Thanks for your answers, hadn't used excel since school. I ended up replacing 'Y' in each column with the data (example 12mm), then merging the data into a new cell.

user2538833
  • 197
  • 2
  • 14