Need formula for to display overall concatenate value

suyogpat

New Member
Joined
Jun 29, 2017
Messages
27
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...

<tbody>
</tbody>
ABCDEFGHIJ
YESNOYESNOYESNOYESNOYESNO
111222333444555666777888999521

<tbody>
</tbody>

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 :)
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hello,

You can test following:

Code:
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Assuming your data is in A1 to J2

Put YES in B7 and No in B8 In C7 put this long winded formula;
=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&", ","")
<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2528;"> <col width="72" style="width: 54pt;" span="33"> <tbody> </tbody>

Copy it down to C8.
 
Upvote 0
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

:)
 
Upvote 0
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.. :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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