what brands a user has.?

[Chetan]

Dear Excel Gurus,

i came across a situation wherein i have to answer what Brands a user has.?
Please refer to attached file.

i have a user list with product brands owned by them. Now in the last columns (K in my data), want to get the Name of Brands a user has. (column K will update as soon a put Yes in Range C3:J15).

Any help in this will be appreciated.

Thanks,
Chetan

 

[PCosta87]

Dear Excel Gurus,

i came across a situation wherein i have to answer what Brands a user has.?
Please refer to attached file.

i have a user list with product brands owned by them. Now in the last columns (K in my data), want to get the Name of Brands a user has. (column K will update as soon a put Yes in Range C3:J15).

Any help in this will be appreciated.

Thanks,
Chetan

Hi,

Please refer to attachment...

I used helper rows to concatenate using a UDF (user defined function).
UDF can be found here:
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Hope this helps

 

[Hui]

If you have Excel 2016 with Office 365
K3: =TEXTJOIN(",",TRUE,IF(C3:J3="Yes",$C$2:$J$2,"")) Ctrl+Shift+Enter
Copy down

Otherwise have a look at Concatif() functions I describe in the comments of the post
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

 

[Chetan]

Dear Hui and PCosta,

thank you very much for your respective replies. It was a learning for me.

After several research on Google i found below UDF. and it is working exactly as per my requirement.

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

Thank you for your Help. i may bother you again.

Regards,
Chetan