A diffuclt formula (for me)

[Welsh Mark3]

Company Name<strike></strike>	Group ID<strike></strike>	<strike></strike>PM Account No<strike></strike>	<strike></strike>Value<strike></strike>	<strike></strike> Desired Result <colgroup><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>
ELUMATEC USA INC<strike></strike>	<strike></strike>Group 1<strike></strike>	<strike></strike>	0	ELUMATEC UNITED KINGDOM LIMITED $1295 | Elumatec Benelux B.V. $300<strike></strike>	<strike></strike>
ELUMATEC UNITED KINGDOM LIMITED <colgroup><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>Group 1<strike></strike>	<strike></strike>	1295	ELUMATEC UNITED KINGDOM LIMITED $1295 | Elumatec Benelux B.V. $300<strike></strike>	<strike></strike>
Elumatec Benelux B.V. <colgroup><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>Group 1<strike></strike>	<strike></strike>	300	ELUMATEC UNITED KINGDOM LIMITED $1295 | Elumatec Benelux B.V. $300<strike></strike>	<strike></strike>
4IMPRINT DIRECT LIMITED <colgroup><col></colgroup><tbody> </tbody> <strike></strike>	Group 2<strike></strike>	<strike></strike>	1000	4IMPRINT DIRECT LIMITED $1000 <colgroup><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>
4 IMPRINT INC. <colgroup><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>Group 2<strike></strike>	<strike></strike>	0	4IMPRINT DIRECT LIMITED $1000 <colgroup><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>
FLIGHTVIEW INC<strike></strike>	<strike></strike>Group 2<strike></strike>	<strike></strike>	0	4IMPRINT DIRECT LIMITED $1000 <colgroup><col></colgroup><tbody> </tbody> <strike></strike>	<strike></strike>

<tbody>
</tbody>

I'm looking for a formula to be used in the desired result column. Essentially where the group numbers are the same I want to return the company name and value if the value is above zero. There will be instances where there are multiple results and some where there are just one.

 

[Eric W]

If you have one of the newer versions of Excel (2016, 365) with the TEXTJOIN function, you can put this in E2:

=TEXTJOIN(" | ",TRUE,IF(($B$2:$B$7=B2)*($D$2:$D$7>0),$A$2:$A$7&TEXT($D$2:$D$7," $0.00"),""))

and confirm it by pressing Control+Shift+Enter. If you don't have TEXTJOIN, I'm afraid it will require VBA.

 

[Welsh Mark3]

If you have one of the newer versions of Excel (2016, 365) with the TEXTJOIN function, you can put this in E2:

=TEXTJOIN(" | ",TRUE,IF(($B$2:$B$7=B2)*($D$2:$D$7>0),$A$2:$A$7&TEXT($D$2:$D$7," $0.00"),""))

and confirm it by pressing Control+Shift+Enter. If you don't have TEXTJOIN, I'm afraid it will require VBA.

This is returning an error, I have excel 2016

 

[Eric W]

That probably means you don't have TEXTJOIN, not every version of 2016 has it. You could still use a UDF (User-Defined Function). To do that, open a copy of your workbook, right click on the sheet tab on the bottom and select View Code. Then from the VBA editor menu, select Insert > Module. On the window that opens, paste this code:

Public Function GetList(myRange As Range, ByVal myValue As String)
Dim myData As Variant, i As Long

    myData = myRange.Value
    For i = 1 To UBound(myData)
        If myData(i, 2) = myValue And myData(i, 4) > 0 Then
            GetList = GetList & " | " & myData(i, 1) & " " & Format(myData(i, 4), "$0.00")
        End If
    Next i
    GetList = Mid(GetList, 4)
    
End Function

Now close the editor (Alt-Q, or the red X in the upper right corner). Now put this formula in E2:

=GetList($A$2:$D$7,B2)

Change the range as needed, copy down as needed. The numbers in red in the code represent columns, if you change the layout of your data, you may need to change them.