Need formula for to display overall concatenate value

[suyogpat]

Hi Guys,

I need your expert help in one formula.

So in below table i have headers,"YES" &"NO" and value of the respective YES and no.

What i am trying to achieve here is in input cell when i enter Yes so it should display all YES value in single cell (it should be like YES-111,YES-333,YES-555,YES-777 and when No will be in input cell it should mentioned No-222,No-444,No-666 and so on...[TABLE="width: 640"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[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]
[/TR]
[TR]
[TD]YES[/TD]
[TD]NO[/TD]
[TD]YES[/TD]
[TD]NO[/TD]
[TD]YES[/TD]
[TD]NO[/TD]
[TD]YES[/TD]
[TD]NO[/TD]
[TD]YES[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]222[/TD]
[TD]333[/TD]
[TD]444[/TD]
[TD]555[/TD]
[TD]666[/TD]
[TD]777[/TD]
[TD]888[/TD]
[TD]999[/TD]
[TD]521[/TD]
[/TR]
</tbody>[/TABLE]

I tried many ways of Hlookup and others but no luck with my limited knowledge, so thought to ask experts now.
Thank you in advance for your help

 

[James006]

Hello,

You can test following:

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                            Optional delimiter As String, Optional NoDuplicates As Boolean) As String


' With your data in Range A2:J2 and your choice in cell L2 :
' =L2&"-"&ConcatIf($A$2:$J$2,L2,$A$3:$J$3," , "&L2&"-")


Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                                stringsRange.Column - compareRange.Column)
    
        For i = 1 To compareRange.Rows.Count
            For j = 1 To compareRange.Columns.Count
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                    If InStr(ConcatIf, delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIf = ConcatIf & delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
    ConcatIf = Mid(ConcatIf, Len(delimiter) + 1)
End Function

Hope this will help

 

[suyogpat]

Hi Thank you for your immediate reply,appreciate the same but is it possible to achieve the same by formula..as i need the same in the dashboard so macro wont work for the same.Thank you again for your help

 

[nanndu141]

Enter Yes/No in M2 Cell and enter the below formula and hit Shift+Enter and see.

{=TEXTJOIN(",",1,IF(--(A1:J1=M2),M2&"-"&A2:J2,""))}

hope this will help.

Regards,
Nandu

 

[nanndu141]

Control + Shift + Enter

 

[suyogpat]

Hello,

Thank you for your reply but seems like this formula is not working at my place its giving me error or #Name ?

 

[AlKey]

Enter Yes/No in M2 Cell and enter the below formula and hit Shift+Enter and see.

{=TEXTJOIN(",",1,IF(--(A1:J1=M2),M2&"-"&A2:J2,""))}

hope this will help.

Regards,
Nandu

Please note that TEXTJOIN function is available only for the Office 365 subscribers.

 

[BGY23]

Assuming your data is in A1 to J2

Put YES in B7 and No in B8 In C7 put this long winded formula;
[TABLE="width: 1841"]
<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2528;"> <col width="72" style="width: 54pt;" span="33"> <tbody>[TR]
[TD="width: 2455, bgcolor: transparent, colspan: 34"]=IF(A$1=$B7,$B7&" -"& A$2&", ","")&" "&IF(B$1=$B7,$B7&" -"& B$2&", ","")&" "&IF(C$1=$B7,$B7&" -"& C$2&", ","")&" "&IF(D$1=$B7,$B7&" -"& D$2&", ","")&" "&IF(E$1=$B7,$B7&" -"& E$2&", ","")&IF(F$1=$B7,$B7&" -"& F$2&", ","")&IF(G$1=$B7,$B7&" -"& G$2&", ","")&IF(H$1=$B7,$B7&" -"& H$2&", ","")&IF(I$1=$B7,$B7&" -"& I$2&", ","")&IF(J$1=$B7,$B7&" -"& J$2&", ","")[/TD]
[/TR]
</tbody>[/TABLE]

Copy it down to C8.

 

[James006]

Hi Thank you for your immediate reply,appreciate the same but is it possible to achieve the same by formula..as i need the same in the dashboard so macro wont work for the same.Thank you again for your help

Hello,

The proposed solution ... is a Formula ... called a User Defined Function ...

You have to use it like a standard formula in your spreadsheet ...

With your data in Range A2:J2 ... and your choice YES or NO in cell L2 ...

In the cell where you need to see the result : =L2&"-"&ConcatIf($A$2:$J$2,L2,$A$3:$J$3," , "&L2&"-")

Hope this clarifies

 

[suyogpat]

Hey BGY23..Thank you for your quick response ..your formula work like a charm..really appreciate your time for the same.. Thanks a ton..

to all others also ..thanks a lot for your time and help..