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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,759
Office Version
2007
Platform
Windows
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:

Nat_2112

New Member
Joined
Jun 25, 2019
Messages
2
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>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,759
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,377
Messages
5,468,256
Members
406,575
Latest member
Joe00

This Week's Hot Topics

Top