Return matches into one cell

[itr674]

How can I return numerous matches into one cell and separate by CHAR(10)?

In B1:K1 I have: Trn1, Trn2 thru Trn10

In A2:A11 I have: Name1, Name2 thru Name10

There is a 1 in in each cell where the person has NOT completed a Trn item.

I want to return ALL the training items not completed into one cell for each Name and use CHAR(10) to force a carriage return after each not trained item...

Hope that make sense.

 

[itr674]

Mike Szczesny, that really did not work because the Trn1, Trn2, Trn 3 are heading and the 1's represent person not trained, but have a bigger issue I just realized...

 

[itr674]

textjoin(CHAR(10),1,IF($B2:$F2=1,$B$1:$F$1,"")

Just realized that this UDF is using another sheet to get the data and if I short the other sheet or add/delete rows it throws everything off, so could I use INDIRECT to put the persons row number in the formula?

I could use MATCH in a helper cell to get the persons row number and then maybe use INDIRECT?

Lets say MATCH returned a 2 in A1 could I do something like

textjoin(CHAR(10),1,IF(indirect("$B"&A1&":$F"&A1)=1,$B$1:$F$1,""))​

 

[Aladin Akyurek]

Add the following code as a module to your workbook using Alt+F11...



Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function​

Training Table
	Trn1	Trn2	Trn3	Trn4	Trn5
Name1	1					Trn1
Name2		1				Trn2
Name3		1	1			Trn2 Trn3
Name4		1	1	1		Trn2 Trn3 Trn4

<tbody>
</tbody>

In G3 control+shift+enter, not just enter, and copy down:

=REPLACE(aconcat(IF($B3:$F3=1,CHAR(10)&$B$2:$F$2,"")),1,1,"")

Select the formulas and apply 'wrap text' formatting.

 

[itr674]

Aladin, thank you, that pretty nice. But, what if the table was on one sheet with the names and training items but on another sheet was just the names and the combined column, would there be a way to sort the first sheet and the combined training items would be in the right cell on the second sheet when that sheet has the name in the original order? if all that makes sense...

I did a test and they would not align, that is why I was running another test using MATCH to return the row of the name on original sheet and then using that with INDIRECT to find the correct row on the original sheet...seems like I'm digging a deep hole...

 

[Aladin Akyurek]

@ itr674

I am not clear. It should be not a problem to have the output in a sheet other than the sheet which houses the data.