• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting a concatenated text list of applicable values

E

ertraid

New Member
Hi All, I have a situation that I'd like to see if somebody has an elegant solution for..


Imagine a list of scenarios, and a list of issues that might or not be contained in each scenario... what is the best way to get a concatenated list of all the issues contained within each scenario?

[pre]
Code: 
.       A         B       C      D        E        F
1              Issue1  Issue2  Issue3  Issue4   List of issues
2  Scenario 1     x       x               x     Issue1, Issue2, Issue4
3  Scenario 2             x      x              Issue2, Issue3
4  Scenario 3     x              x        x     Issue1, Issue3, Issue4
5  Scenario 4             x      x        x     Issue2, Issue3, Issue4
[/pre]
The maskers could be "x" or "1" or any other marker that makes the solution simpler.


I think I got close using an array formula: {=IF(B2:E2="x",B$1:E$1,"")}, and getting an array of applicable Issues, but then CONCATENATE() will not work with an array.


Any different ideas? Any way to make CONCATENATE() work with an array?


Thought an elegant solution for this might be a candidate for a forensic post...

Thanks in advance...

ET
 
=CONCATENATE(IF(B2<>"",B$1&",",""),IF(C2<>"",C$1&",",""),IF(D2<>"",D$1&",",""),IF(E2<>"",E$1&",",""))


or you can use equal (=) sign with your value
 
Good day ertraid


=A1&B1&C1&D1&E1&F1

The above in G1 would give this if A1,B1,D1,F1 contained data and C1 and E1 had the mask


12X3X4 in G1.


And with this data set,Senario 1 1 2 X 3 X 4 Issue 1 Issue 2 Issue 3


In K1 =A1&B1&C1&D1&E1&F1&G1&H1&I1&J1 would give this

Senario 112X3X4Issue 1Issue 2Issue 3


If it is a large range that you want to concatenate then you need a bit of VBA code you can not do it with ranges such as A1:H1.

Try this link


http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
 
Thanks for the responses guys..

Goel, your solution is what I am currently suing, but the problem I have with it is that I cannot extend the range (i.e. add new issues) without changing the formulas...


If possible, was looking for a solution that would be based on ranges, and so automatically adjust if I add or delete columns. .I should have mentioned this in my original post...


Cheers,

ET
 
Hi ertraid,


I think you have not studied the post by Chandoo, mentioned in bobhc's post. Please see this:

[pre]
Code: 
x	x	x	x
a	b	c	d[/pre]

and the formula


[code]=IF(B1:E1="x",CONCAT(B2:E2,", "))


...give me


a, b, c, d[/code]


as a result. so have a go through that article, copy-paste the function CONCAT in a module in your workbook and use the above formula will solve your problem.


Regards,

Faseeh
 
Strange coincidence, I recently had to write a Concatenate-If UDF. Perhaps you can use it?

[pre]
Code: 
Function ConcIf(Check_Range As Range, Criteria As String, _
ConcRange As Range, Delim As String)

If Check_Range.Count <> ConcRange.Count Then
ConcIf = "#Unequal_Range"
Exit Function
End If

xCount = Check_Range.Count

For i = 1 To xCount
'Use cells(1,i) if horizontal, use cells(i,1) if vertical ranges
If UCase(Check_Range.Cells(1, i)) = UCase(Criteria) Then
ConcIf = ConcIf & ConcRange.Cells(1, i) & Delim
End If
Next
If Len(ConcIf) > 0 Then
ConcIf = Left(ConcIf, Len(ConcIf) - Len(Delim))
Else
ConcIf = "" 'No criteria matches
End If
End Function
[/pre]
 
Hi Luke M,


I'm bonking on how to use your UDF. It would seem to me =ConcIf(Check_Range) would do it, where "Check_Range" is a named range that I want to Conc.


Looks like it will do vert to horiz OR horiz to vert depending on the cells(1,i) or cells(i,1).


Thanks,

Howard
 
Hi, lhkittle!

A few days ago I wrote on a similar topic:

http://chandoo.org/forums/topic/combine-text-from-multiple-colummns-based-on-condition#post-71894

Regards!
 
Thanks for the support guys...LuceM's Concif does exactly what I need..

I was originally trying to solve it without VBA, but it sems there is no valid way.. So I'll play around with Luke's funtion.


Once again.. thank you very much.


ET
 
Hi ET,


The ConcIf function takes 4 arguments. The range to check criteria against, the criteria itself, the range to concatenate, and your delimiter (what goes between each concatenated object).

I think your setup would be something like:

=ConcIf(B2:E2,"x",B$1:E$1,", ")
 
You must log in or register to reply here.
Back
Top