Excel2016. Return several criteria for an article as text

N

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.

[TABLE="width: 1467"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Material Number[/TD]
[TD]Material Description[/TD]
[TD]CE[/TD]
[TD]UL[/TD]
[TD]ATEX[/TD]
[TD]IECEx[/TD]
[TD]GL[/TD]
[TD]MOT South[/TD]
[TD]MOT NRTL[/TD]
[TD]EAC[/TD]
[TD]Higher temperature[/TD]
[TD]Mechanical Support[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]028031[/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]
[TD]045065[/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]
[TD]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,TÜV_Süd[/TD]
[/TR]
</tbody>[/TABLE]


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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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

[TABLE="width: 1467"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Material Number[/TD]
[TD]Material Description[/TD]
[TD]CE[/TD]
[TD]UL[/TD]
[TD]ATEX[/TD]
[TD]IECEx[/TD]
[TD]GL[/TD]
[TD]MOT South[/TD]
[TD]MOT NRTL[/TD]
[TD]EAC[/TD]
[TD]Higher temperature[/TD]
[TD]Mechanical Support[/TD]
[TD]Criterias[/TD]
[/TR]
[TR]
[TD]028031[/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], _60, _60[/TD]
[/TR]
[TR]
[TD]045065[/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], _60, _60, _60[/TD]
[/TR]
[TR]
[TD]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], _55, _55, _55, ,

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Nat_2112 said:
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

[TABLE="width: 1467"]
<tbody>[TR]
[TD]Material Number[/TD]
[TD]Material Description[/TD]
[TD]CE[/TD]
[TD]UL[/TD]
[TD]ATEX[/TD]
[TD]IECEx[/TD]
[TD]GL[/TD]
[TD]MOT South[/TD]
[TD]MOT NRTL[/TD]
[TD]EAC[/TD]
[TD]Higher temperature[/TD]
[TD]Mechanical Support[/TD]
[TD]Criterias[/TD]
[/TR]
[TR]
[TD]028031[/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], _60, _60[/TD]
[/TR]
[TR]
[TD]045065[/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], _60, _60, _60[/TD]
[/TR]
[TR]
[TD]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], _55, _55, _55, ,
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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