Excel2016. Return several criteria for an article as text

Nat_2112

New Member
Joined
Jun 25, 2019
Messages
2
Hello,

I have a list with articles which have technical criteria. I would now like to have the criteria returned as text but the complicated bit for me is how I can show the different temperatures... I tried if functions but it didn't really work. Would anybody know how best to solve this? My Excel version is 2016.

Many thanks in advance.

Material NumberMaterial DescriptionCEULATEXIECExGLMOT SouthMOT NRTLEACHigher temperatureMechanical SupportOutput
028031BK1111CE, UL_60, ATEX_60
045065EK1234CE, UL_60, ATEX_60, IECEx_60
177357EK1111CE, UL_55, ATEX_55, IECEx_55,GL,TÜV_Süd

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>


If "Higher Temperature" is ticked the temperature for UL, ATEX and IECEx should be 60, else 55
I am looking for a formula which will return all the criterias which are ticked as a text in column M. The formula can either work with the material number or description
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try the following UDF.
Put the code in a module and use the function as shown in the following example:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:109.31px;" /><col style="width:128.32px;" /><col style="width:20.91px;" /><col style="width:21.86px;" /><col style="width:36.12px;" /><col style="width:38.02px;" /><col style="width:21.86px;" /><col style="width:72.24px;" /><col style="width:67.49px;" /><col style="width:29.47px;" /><col style="width:124.51px;" /><col style="width:124.51px;" /><col style="width:286.1px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Material Number</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Material Description</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">CE</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">UL</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">ATEX</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">IECEx</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">GL</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">MOT South</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">MOT NRTL</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">EAC</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Higher temperature</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Mechanical Support</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Output</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">28031</td><td >BK1111</td><td >?</td><td >?</td><td >?</td><td > </td><td > </td><td > </td><td > </td><td >?</td><td >?</td><td >?</td><td >CE, UL_60, ATEX_60</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">45065</td><td >EK1234</td><td >?</td><td >?</td><td >?</td><td >?</td><td > </td><td > </td><td > </td><td >?</td><td >?</td><td > </td><td >CE, UL_60, ATEX_60, IECEx_60</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">177357</td><td >EK1111</td><td >?</td><td >?</td><td >?</td><td >?</td><td >?</td><td >?</td><td > </td><td >?</td><td > </td><td > </td><td >CE, UL_55, ATEX_55, IECEx_55, GL, MOT South</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >M2</td><td >=Return_several()</td></tr></table></td></tr></table>

---
Code:
Function Return_several()
    Dim c As Range, f As Long, cad As String
    f = ActiveCell.Row
    For Each c In Range("C" & f & ":I" & f)
        If c.Value <> "" Then
            Select Case c.Column
                Case 4 To 6
                    If Cells(f, "K") <> "" Then
                        cad = cad & Cells(1, c.Column) & "_60" & ", "
                    Else
                        cad = cad & Cells(1, c.Column) & "_55" & ", "
                    End If
                Case Else
                    cad = cad & Cells(1, c.Column) & ", "
            End Select
        End If
    Next
    If cad <> "" Then Return_several = Left(cad, Len(cad) - 2)
End Function
 
Last edited:
Upvote 0
Thanks Dante, it works in the way that I get the temperatures returned (60 and 55) but the actual words attributes mentioned. Where do i need to amend the formula to have it all returned?

Many thanks,
Natalie

Material NumberMaterial DescriptionCEULATEXIECExGLMOT SouthMOT NRTLEACHigher temperatureMechanical SupportCriterias
028031BK1111, _60, _60
045065EK1234, _60, _60, _60
177357EK1111, _55, _55, _55, ,


<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Thanks Dante, it works in the way that I get the temperatures returned (60 and 55) but the actual words attributes mentioned. Where do i need to amend the formula to have it all returned?

Many thanks,
Natalie

Material NumberMaterial DescriptionCEULATEXIECExGLMOT SouthMOT NRTLEACHigher temperatureMechanical SupportCriterias
028031BK1111, _60, _60
045065EK1234, _60, _60, _60
177357EK1111, _55, _55, _55, ,

<tbody>
</tbody>


You have to put the formula according to my example, in column M and in row 2.
My example starts at M2.
If your data is not as I put it in my example, then you must clearly explain where you have each of your data. An image would help a lot.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top