Return matches into one cell

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,786
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I don't know if this is what you're after. In the future, if you could supply some sample data and desired output we could more easily address your needs.

This solution requires Excel2016 because it uses function TEXTJOIN, which is new. Cell D10 is formatted with WrapText.

ABCD
1nametrnincomplete
2abetrn11
3bobtrn2
4caltrn31
5debtrn4
6evetrn5
7fattrn61
8gobtrn7
9
10abe trn1
cal trn3
fat trn6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Array Formulas
CellFormula
D10{=TEXTJOIN(CHAR(10),1,IF(C2:C8=1,A2:A8&" "&B2:B8,""))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
DRSteele, Thank you!!, and my apologies on not providing the excel sheet, this new computer and don't have the sheet maker on it....but I believe this what I'm looking for. On way to work and will try it in about an hour...standby for more in few...
 
Upvote 0
DRSteele, well having trouble with HTML maker so going to try to build the table here...

Training Table
Trn1Trn2Trn3Trn4Trn5
Name11
Name21
Name311
Name4111
Would like to lreturn the data in ths fashion…
Trn Items Due
Name1Trn1
Name2Trn2
Name3Trn2
Trn3
Name4Trn2
Trn3
Trn4

<tbody>
</tbody>
 
Upvote 0
Turns out I have Excel 2013 but found this UDF and have it sort of working, the problem is I need to return the training item (TRN1, TRN2 etc) if the Name and 1 match. "1" indicates the training item is due... still working on it...

'https://stackoverflow.com/questions/45843881/textjoin-udf-for-excel-2013

FunctionTEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0

If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2,1)
For d = LBound(arr2, 1) ToUBound(arr2, 2)
If arr2(c, d) <>"" Or Not skipblank Then
TEXTJOIN = TEXTJOIN &arr2(c, d) & delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> "" OrNot skipblank Then
TEXTJOIN = TEXTJOIN &arr2(c) & delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) -Len(delim))
End Function
 
Upvote 0
I think I got it with this and CSE:

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

Copied down a range and it worked, just have to make sure the names stay in order. I had thought about a helper column but haven't worked that out yet...

Now next issue is seeing if there is a way to put CHAR(10) after three training items...so the row heights don't end up being to height because of the number of items returned in each cell...
 
Upvote 0
Any reason this UDF should not work if it is on a separate sheet?
 
Upvote 0
The problem was there were no matches, I guess I will have to use ISERROR to return nothing if there is nothing to return...???
 
Upvote 0
I don't have enough knowledge of VBA to be of much more help here...sorry.
 
Upvote 0
Can you use this formula?
=substitute(concatenate(transpose(if($C$2:$C$8<>"",$A$2:$A$8&" "&$B$2:$B$8&char(10)))),"FALSE","") Before you enter, you need to use your mouse to highlight the transpose part of your formula. Highlight the tranpose(.......all the to the 3rd ) after char(10). Hit F9 and remove both { and } in the formula. Hit enter. Then edit format for wrap text.
NameTrnInc.
abetrn11
bobtrn2
caltrn31
debtrn4
evetrn5
fattrn61
gobtrn7
abe trn1
cal trn3
fat trn6

<colgroup><col span="3"><col></colgroup><tbody>
</tbody>




<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,696
Members
448,293
Latest member
jin kazuya

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