Return column headers of all non-blank cells

I have a list of customers in column A, and a list of products in columns E-AE. Some customers are only registered for one product, while others are registered more than one. I used a formula to input True or False is column C for if the customer is taking part in more than one product. But now I want column D to return a list of all the products the client is registered for. For instance, in the example below, I want D2 to return Product 1, Product 3. Is there a formula I can input? Using 2010 version

	a	b	c	d	e	f	g
1	Customer				Product 1	Product 2	Product 3
2	Cust 1				2		2
3	Cust 2				1		1
4	Cust 3					3

Try this formula in cell D2:

=TEXTJOIN(", ",TRUE,IF(E2:AE2<>"",E$1:AE$1,""))

NOTE: This is an array formula that must be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

Copy the formula down column D as far as you have data in column A.

Hope this helps / Lars-Åke

Hi

Quick & dirty code (no check nowhere…). In a VBA module past the following code:

Option Explicit

Public Function SPECIAL_JOIN(ByRef targetRange As Range, textRange As Range) As String

'

    Dim c       As Range

    Dim r       As Long

    Dim str     As String

    r = textRange.Row

    For Each c In targetRange.Cells

        If Not IsEmpty(c) Then

            str = str & Cells(r, c.Column).Value & ", "

        End If

    Next c

    str = Mid(str, 1, Len(str) - 2)

    SPECIAL_JOIN = str

End Function

In D2: =SPECIAL_JOIN(E2:AE2, E**$1:AE$**1)

Hi

TEXTJOIN is available with Excel 2016/365 only. With Power Query (aka Get & Transform in Excel 2016/365) - free add-in for Excel (download and install from this link) - if you can live with the Registered products column on the right:

Sample available here: after modifying data in column A-AD, right-click in column Registerd products > Refresh

If you have a large number of Customers/rows this will be way more efficient than any formula

EDIT: Forgot to mention. There's no solution to this problem with Excel 2010 and a formula. A VBA function can do it though

Hm im now getting an error: compile error: ambiguous name detected: special_join

Hi

TEXTJOIN is available with Excel 2016/365 only. With Power Query (aka Get & Transform in Excel 2016/365) - free add-in for Excel (download and install from this link) - if you can live with the Registered products column on the right:

Sample available here: after modifying data in column A-AD, right-click in column Registerd products > Refresh

If you have a large number of Customers/rows this will be way more efficient than any formula

EDIT: Forgot to mention. There's no solution to this problem with Excel 2010 and a formula. A VBA function can do it though

Hi and thank you for yor response. I an unable to download add-ins because of system restrictions on my work computer. Do you know how i can accomplish this with VBA?