textjoin with exact match

[david763]

Hi all, I am stuck on a problem which I think is simple - just can't get my head around it. I also think there is a much better, more obvious solution that I am also missing completely.
I am open to solution for either...

I have two tabs:
1) is a master list of records
2) is a list of activities which link to one-or-more of the records

At present, the user manually enters the Records id(s) in the right most column on 2nd tab
I need to pick up and display the activities which match the record on the 1st tab (in red)

Problem:
I created this formula....
=TEXTJOIN(",",TRUE,IF(TEXT([@[R'#]],0)=activity[R'#],activity[A'#],""))
...which works perfectly, EXCEPT....

Instead of returning the results "101 102 106 109" against record "1", it returns ALL activities with a "1" in the Records number, ie it also includes records "104 107 111" as it also recognises 11, 12, 21 as they all contain a "1".

I need a way of only pulling through EXACT matches with the record number, OR an inspiring different methodology...

PS I could modify the layout of these tabs slightly, but no major changes, nor VBA if possible...

Many thanks

 

[HongRu]

Try this. The formula is not created by me. I get from another FB group.

Excel班級座號-1.xlsx
	A	B	C	D	E	F
1	Records			Activity
2	R#	result		A#		R#
3	1	101 102 106 109		101		1,2
4	2	101 103		102		1
5	3	103 106		103		2,3
6	4			104		11
7	5	110		105
8	6	110		106		3,1
9	7	111		107		12
10	8			108		22,23
11	9			109		21,22,1
12	10			110		5,6
13	11	104 111		111		7,11
14	12	107
15	13
16	14
17	15
18	16
19	17
20	18
21	19
22	20
23	21	109
24	22	108 109
25	23	108
工作表1

Cell Formulas
Range	Formula
B3:B25	B3	=TEXTJOIN(" ",,IFERROR(IF(FIND(","&A3&",",","&F$3:F$13&","),D$3:D$13),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[david763]

Try this. The formula is not created by me. I get from another FB group.
=TEXTJOIN(" ",,IFERROR(IF(FIND(","&A3&",",","&F$3:F$13&","),D$3:D$13),""))

worked perfectly, thank you HongRu

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have dynamic array capability in your version, then a slight variation on the above would allow to to produce all those results with a formula in B3 only, without copying it down the column.

22 11 30.xlsm
	A	B	C	D	E	F
1	Records			Activity
2	R#	result		A#		R#
3	1	101 102 106 109		101		1,2
4	2	101 103		102		1
5	3	103 106		103		2,3
6	4			104		11
7	5	110		105
8	6	110		106		3,1
9	7	111		107		12
10	8			108		22,23
11	9			109		21,22,1
12	10			110		5,6
13	11	104 111		111		7,11
14	12	107
15	13
16	14
17	15
18	16
19	17
20	18
21	19
22	20
23	21	109
24	22	108 109
25	23	108
Spill Results

Cell Formulas
Range	Formula
B3:B25	B3	=BYROW(A3:A25,LAMBDA(rw,TEXTJOIN(" ",,IFERROR(IF(FIND(","&rw&",",","&F$3:F$13&","),D$3:D$13),""))))
Dynamic array formulas.

 

[david763]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have dynamic array capability in your version, then a slight variation on the above would allow to to produce all those results with a formula in B3 only, without copying it down the column.

Thank you for the solution Peter, and heads-up about account details - wasn't aware of that.

Kind regards

 

[Peter_SSs]

You're welcome.

.. and thanks for updating your details.

 

[jsw2020]

worked perfectly, thank you HongRu

What do you do it the numbers are not perfectly spaced 101 102 103, but instead 101,102103, etc?

 

[Peter_SSs]

What do you do it the numbers are not perfectly spaced 101 102 103, but instead 101,102103, etc?

Welcome to the MrExcel board!

I suggest that you start a new thread of your own. You can put a link to this one if you want. In your new thread I also suggest that you provide a small set of sample data and the expected results with XL2BB so that we can see your data, layout and what/where the results should be & we can also easily copy your sample data for testing.