Textjoin with wildcard (Search,Unique)

[hwong8848]

Hello all!

I have a question regarding looking up similar companies.

For example, in my database, there is AXA Asia, AXA Group or AXA Company Limited. I will look up AXA with search function and filter them out.

My formula is like this

=IFERROR(UNIQUE(FILTER(Uni_Comp_list,ISNUMBER(SEARCH(A2,Uni_Comp_list)))),"N/A")

where A2 is AXA and Uni_Comp_list contain all the different company name.

However the formula will spill since there are more than one match, and I will have to copy and paste it everytime.

I tried using textjoin(",",,True,A2:A16) coupled with the formula above but have no luck.

Any help is appreciated.

 

[Peter_SSs]

My formula is like this

=IFERROR(UNIQUE(FILTER(Uni_Comp_list,ISNUMBER(SEARCH(A2,Uni_Comp_list)))),"N/A")

That formula does not correspond with your profile information.

Does you your profile need to be updated already?

Any chance that we could have a small set of sample data and the expected results, preferably with XL2BB?

 

[hwong8848]

Template.xlsx
	A	B	C	D	E	F
1	Peer Group 1	Current Output and setup			Desired Output	Uni_Comp_list
2	AIA	AIA		AIA	AIA	Aegon
3	China Life	China Life (Taiwan)		China Life	China Life (Taiwan)	Ageas
4		China Life		China Pacific	China Life	AXA
5	China Pacific	China Pacific		China Tai ping	China Pacific	AIA
6	China Tai ping	China Tai Ping		DBS	China Tai Ping	BNPP Cardif
7	DBS	DBS		FWD	DBS	Aon
8	FWD	FWD		Great Eastern	FWD	Generali
9	Great Eastern	Great Eastern		Hang Seng	Great Eastern	Chubb
10	Hang Seng	Hang Seng		Manulife	Hang Seng	Chubb Life
11	Manulife	Manulife		Metlife	Manulife	Cigna
12		Manulife Financial (Canada)		New China Life	Manulife Financial (Canada)	FWD
13		Manulife Financial		Ping An	Manulife Financial	Great Eastern
14	Metlife	MetLife		Standard Chartered	MetLife	Hannover Re
15		MetLife, Inc.		United Overseas	MetLife, Inc.	Liberty
16	New China Life	N/A		UOB	N/A	Manulife
17	Ping An	Ping An			Ping An	MetLife
18	Standard Chartered	Standard Chartered Bank			Standard Chartered Bank	MunichRe
19		Standard Chartered			Standard Chartered	Peak Re
20	United Overseas	N/A			N/A	PCA
21	UOB	UOB			UOB	Prudential Financial
22						QBE
23						RGA
24						Sun Life
25						Swiss Re
26						Zurich
27						Sompo
28						Allianz
29						SCOR
30						Tokio Marine
31						MSIG
32						Huatai
33						Marsh
34						Ping An
35						China Pacific
36						Cathay
37						AIG
38						PartnerRe
39						Fubon
40						FTLife
41						HSBC
42						Transatlantic
43						MCIS
44						Etiqa
45						General Re
46						Muang Thai
47						Ocean Life
48						NanShan
49						TransGlobe
50						Shin Kong
51						China Life (Taiwan)
52						AFLAC
53						Travelers
54						Hartford Financial Services Group
55						Allianz Life Insurance
56						Citigroup
57						Manulife Financial (Canada)
58						Manulife Financial
59						Legal & General America
60						HSBC Bank
61						Sun Life Financial
62						Zurich North America
63						Barclays
64						Aflac Incorporated
65						MetLife, Inc.
66						Morgan Stanley
67						The Hartford Financial Services Group, Inc.
68						Allianz SE
69						Standard Chartered Bank
70						Aviva
71						BNP Paribas
72						AXA Group
73						Allianz Worldwide Partners
74						AEGON Group
75						ING Bank
76						Zurich Insurance Group
77						Swiss Reinsurance
78						Legal & General Group
79						Sun Life Financial Inc.
80						American International Group, Inc.
81						Prudential Financial, Inc.
82						Aegon N.V.
83						Assicurazioni Generali S.p.A.
84						Zurich Insurance Group AG
85						China Tai Ping
86						PingAn
87						China Life
88						Standard Chartered
89						UOB
90						Hang Seng
91						DBS
Peer Group

Cell Formulas
Range	Formula
B2	B2	=IFERROR(UNIQUE(FILTER(F2:F91,ISNUMBER(SEARCH(A2,F2:F91)))),"N/A")
B3:B21	B3	=IFERROR(UNIQUE(FILTER(Uni_Comp_list,ISNUMBER(SEARCH(A3,Uni_Comp_list)))),"N/A")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Uni_Comp_list	=Misc!$E$2:$E$91	B5:B11, B16:B18, B20:B21, B3, B14

 

[kvsrinivasamurthy]

I did not follow what is actually required. You have shown the current output and desired output are exactly same.
On guess. Try this

=IFERROR(textjoin(" ",true,(UNIQUE(FILTER(Uni_Comp_list,ISNUMBER(SEARCH(A2,Uni_Comp_list))))),"N/A")

 

[Peter_SSs]

I don't know why your desired output has "N/A" in it twice when that is not one of the entries in Uni_Comp_List that you seem to want to filter?

Could this be what you are after?

hwong8848.xlsm
	A	B	C		E	F
1	Peer Group 1	Current Output and setup			Desired Output	Uni_Comp_list
2	AIA	AIA			AIA	Aegon
3	China Life	China Life			China Life (Taiwan)	Ageas
4		China Life (Taiwan)			China Life	AXA
5	China Pacific	China Pacific			China Pacific	AIA
6	China Tai ping	China Tai Ping			China Tai Ping	BNPP Cardif
7	DBS	DBS			DBS	Aon
8	FWD	FWD			FWD	Generali
9	Great Eastern	Great Eastern			Great Eastern	Chubb
10	Hang Seng	Hang Seng			Hang Seng	Chubb Life
11	Manulife	Manulife			Manulife	Cigna
12		Manulife Financial			Manulife Financial (Canada)	FWD
13		Manulife Financial (Canada)			Manulife Financial	Great Eastern
14	Metlife	MetLife			MetLife	Hannover Re
15		MetLife, Inc.			MetLife, Inc.	Liberty
16	New China Life	Ping An			N/A	Manulife
17	Ping An	Standard Chartered			Ping An	MetLife
18	Standard Chartered	Standard Chartered Bank			Standard Chartered Bank	MunichRe
19		UOB			Standard Chartered	Peak Re
20	United Overseas				N/A	PCA
21	UOB				UOB	Prudential Financial
22						QBE
23						RGA
24						Sun Life
25						Swiss Re
26						Zurich
27						Sompo
28						Allianz
29						SCOR
30						Tokio Marine
31						MSIG
32						Huatai
33						Marsh
34						Ping An
35						China Pacific
36						Cathay
37						AIG
38						PartnerRe
39						Fubon
40						FTLife
41						HSBC
42						Transatlantic
43						MCIS
44						Etiqa
45						General Re
46						Muang Thai
47						Ocean Life
48						NanShan
49						TransGlobe
50						Shin Kong
51						China Life (Taiwan)
52						AFLAC
53						Travelers
54						Hartford Financial Services Group
55						Allianz Life Insurance
56						Citigroup
57						Manulife Financial (Canada)
58						Manulife Financial
59						Legal & General America
60						HSBC Bank
61						Sun Life Financial
62						Zurich North America
63						Barclays
64						Aflac Incorporated
65						MetLife, Inc.
66						Morgan Stanley
67						The Hartford Financial Services Group, Inc.
68						Allianz SE
69						Standard Chartered Bank
70						Aviva
71						BNP Paribas
72						AXA Group
73						Allianz Worldwide Partners
74						AEGON Group
75						ING Bank
76						Zurich Insurance Group
77						Swiss Reinsurance
78						Legal & General Group
79						Sun Life Financial Inc.
80						American International Group, Inc.
81						Prudential Financial, Inc.
82						Aegon N.V.
83						Assicurazioni Generali S.p.A.
84						Zurich Insurance Group AG
85						China Tai Ping
86						PingAn
87						China Life
88						Standard Chartered
89						UOB
90						Hang Seng
91						DBS
Sheet1

Cell Formulas
Range	Formula
B2:B19	B2	=SORT(FILTER(Uni_Comp_List,BYROW(Uni_Comp_List,LAMBDA(rw,IFERROR(AGGREGATE(14,6,SEARCH(FILTER(A2:A21,A2:A21<>""),rw),1),0)>0))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Uni_Comp_List	=Sheet1!$F$2:$F$91	B2

 

[Peter_SSs]

@hwong8848
You have not addressed my point about your profile showing Excel 2016 but you are using functions that are not available in Excel 2016. Please re-visit that point.

You have shown the current output and desired output are exactly same.

Current Output is made up of many formulas in various cells, some of which spill and some don't.

@hwong8848
If those empty rows in column A were only put there to get that 'manufactured' result with many formulas, then my suggestion could be shortened a bit.

hwong8848.xlsm
	A	B	C			F
1	Peer Group 1	Current Output and setup				Uni_Comp_list
2	AIA	AIA				Aegon
3	China Life	China Life				Ageas
4	China Pacific	China Life (Taiwan)				AXA
5	China Tai ping	China Pacific				AIA
6	DBS	China Tai Ping				BNPP Cardif
7	FWD	DBS				Aon
8	Great Eastern	FWD				Generali
9	Hang Seng	Great Eastern				Chubb
10	Manulife	Hang Seng				Chubb Life
11	Metlife	Manulife				Cigna
12	New China Life	Manulife Financial				FWD
13	Ping An	Manulife Financial (Canada)				Great Eastern
14	Standard Chartered	MetLife				Hannover Re
15	United Overseas	MetLife, Inc.				Liberty
16	UOB	Ping An				Manulife
17		Standard Chartered				MetLife
18		Standard Chartered Bank				MunichRe
19		UOB				Peak Re
20						PCA
21						Prudential Financial
22						QBE
23						RGA
24						Sun Life
25						Swiss Re
26						Zurich
27						Sompo
28						Allianz
29						SCOR
30						Tokio Marine
31						MSIG
32						Huatai
33						Marsh
34						Ping An
35						China Pacific
36						Cathay
37						AIG
38						PartnerRe
39						Fubon
40						FTLife
41						HSBC
42						Transatlantic
43						MCIS
44						Etiqa
45						General Re
46						Muang Thai
47						Ocean Life
48						NanShan
49						TransGlobe
50						Shin Kong
51						China Life (Taiwan)
52						AFLAC
53						Travelers
54						Hartford Financial Services Group
55						Allianz Life Insurance
56						Citigroup
57						Manulife Financial (Canada)
58						Manulife Financial
59						Legal & General America
60						HSBC Bank
61						Sun Life Financial
62						Zurich North America
63						Barclays
64						Aflac Incorporated
65						MetLife, Inc.
66						Morgan Stanley
67						The Hartford Financial Services Group, Inc.
68						Allianz SE
69						Standard Chartered Bank
70						Aviva
71						BNP Paribas
72						AXA Group
73						Allianz Worldwide Partners
74						AEGON Group
75						ING Bank
76						Zurich Insurance Group
77						Swiss Reinsurance
78						Legal & General Group
79						Sun Life Financial Inc.
80						American International Group, Inc.
81						Prudential Financial, Inc.
82						Aegon N.V.
83						Assicurazioni Generali S.p.A.
84						Zurich Insurance Group AG
85						China Tai Ping
86						PingAn
87						China Life
88						Standard Chartered
89						UOB
90						Hang Seng
91						DBS
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B19	B2	=SORT(FILTER(Uni_Comp_List,BYROW(Uni_Comp_List,LAMBDA(rw,IFERROR(AGGREGATE(14,6,SEARCH(A2:A16,rw),1),0)>0))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Sheet1 (2)'!Uni_Comp_List	='Sheet1 (2)'!$F$2:$F$91	B2

 

[hwong8848]

That formula does not correspond with your profile information.
View attachment 80259
Does you your profile need to be updated already?

Any chance that we could have a small set of sample data and the expected results, preferably with XL2B

Ah yes - the office version of my profile indeed need updating.

 

[hwong8848]

I have

@hwong8848
You have not addressed my point about your profile showing Excel 2016 but you are using functions that are not available in Excel 2016. Please re-visit that point.



Current Output is made up of many formulas in various cells, some of which spill and some don't.

View attachment 80263

@hwong8848
If those empty rows in column A were only put there to get that 'manufactured' result with many formulas, then my suggestion could be shortened a bit.

hwong8848.xlsm
	A	B	C			F
1	Peer Group 1	Current Output and setup				Uni_Comp_list
2	AIA	AIA				Aegon
3	China Life	China Life				Ageas
4	China Pacific	China Life (Taiwan)				AXA
5	China Tai ping	China Pacific				AIA
6	DBS	China Tai Ping				BNPP Cardif
7	FWD	DBS				Aon
8	Great Eastern	FWD				Generali
9	Hang Seng	Great Eastern				Chubb
10	Manulife	Hang Seng				Chubb Life
11	Metlife	Manulife				Cigna
12	New China Life	Manulife Financial				FWD
13	Ping An	Manulife Financial (Canada)				Great Eastern
14	Standard Chartered	MetLife				Hannover Re
15	United Overseas	MetLife, Inc.				Liberty
16	UOB	Ping An				Manulife
17		Standard Chartered				MetLife
18		Standard Chartered Bank				MunichRe
19		UOB				Peak Re
20						PCA
21						Prudential Financial
22						QBE
23						RGA
24						Sun Life
25						Swiss Re
26						Zurich
27						Sompo
28						Allianz
29						SCOR
30						Tokio Marine
31						MSIG
32						Huatai
33						Marsh
34						Ping An
35						China Pacific
36						Cathay
37						AIG
38						PartnerRe
39						Fubon
40						FTLife
41						HSBC
42						Transatlantic
43						MCIS
44						Etiqa
45						General Re
46						Muang Thai
47						Ocean Life
48						NanShan
49						TransGlobe
50						Shin Kong
51						China Life (Taiwan)
52						AFLAC
53						Travelers
54						Hartford Financial Services Group
55						Allianz Life Insurance
56						Citigroup
57						Manulife Financial (Canada)
58						Manulife Financial
59						Legal & General America
60						HSBC Bank
61						Sun Life Financial
62						Zurich North America
63						Barclays
64						Aflac Incorporated
65						MetLife, Inc.
66						Morgan Stanley
67						The Hartford Financial Services Group, Inc.
68						Allianz SE
69						Standard Chartered Bank
70						Aviva
71						BNP Paribas
72						AXA Group
73						Allianz Worldwide Partners
74						AEGON Group
75						ING Bank
76						Zurich Insurance Group
77						Swiss Reinsurance
78						Legal & General Group
79						Sun Life Financial Inc.
80						American International Group, Inc.
81						Prudential Financial, Inc.
82						Aegon N.V.
83						Assicurazioni Generali S.p.A.
84						Zurich Insurance Group AG
85						China Tai Ping
86						PingAn
87						China Life
88						Standard Chartered
89						UOB
90						Hang Seng
91						DBS
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B19	B2	=SORT(FILTER(Uni_Comp_List,BYROW(Uni_Comp_List,LAMBDA(rw,IFERROR(AGGREGATE(14,6,SEARCH(A2:A16,rw),1),0)>0))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'Sheet1 (2)'!Uni_Comp_List	='Sheet1 (2)'!$F$2:$F$91	B2

I have updated my profile details - after checking I have Microsoft 365.


I have tried using your formula and it return #Name? , I suspect that I can't use the function byrow and Lambda, as it does not show any description when I enter them.

Is the a way to do this without these two equations?

Template.xlsx
	A	B	C	D	E	F
1	Peer Group 1	Current Output and setup			Desired Output	Uni_Comp_list
2	AIA	#NAME?			AIA	Aegon
3	China Life				China Life (Taiwan)	Ageas
4	China Pacific				China Life	AXA
5	China Tai ping				China Pacific	AIA
6	DBS				China Tai Ping	BNPP Cardif
7	FWD				DBS	Aon
8	Great Eastern				FWD	Generali
9	Hang Seng				Great Eastern	Chubb
10	Manulife				Hang Seng	Chubb Life
11	Metlife				Manulife	Cigna
12	New China Life				Manulife Financial (Canada)	FWD
13	Ping An				Manulife Financial	Great Eastern
14	Standard Chartered				MetLife	Hannover Re
15	United Overseas				MetLife, Inc.	Liberty
16	UOB				N/A	Manulife
17					Ping An	MetLife
18					Standard Chartered Bank	MunichRe
19					Standard Chartered	Peak Re
20					N/A	PCA
21					UOB	Prudential Financial
22						QBE
23						RGA
24						Sun Life
25						Swiss Re
26						Zurich
27						Sompo
28						Allianz
29						SCOR
30						Tokio Marine
31						MSIG
32						Huatai
33						Marsh
34						Ping An
35						China Pacific
36						Cathay
37						AIG
38						PartnerRe
39						Fubon
40						FTLife
41						HSBC
42						Transatlantic
43						MCIS
44						Etiqa
45						General Re
46						Muang Thai
47						Ocean Life
48						NanShan
49						TransGlobe
50						Shin Kong
51						China Life (Taiwan)
52						AFLAC
53						Travelers
54						Hartford Financial Services Group
55						Allianz Life Insurance
56						Citigroup
57						Manulife Financial (Canada)
58						Manulife Financial
59						Legal & General America
60						HSBC Bank
61						Sun Life Financial
62						Zurich North America
63						Barclays
64						Aflac Incorporated
65						MetLife, Inc.
66						Morgan Stanley
67						The Hartford Financial Services Group, Inc.
68						Allianz SE
69						Standard Chartered Bank
70						Aviva
71						BNP Paribas
72						AXA Group
73						Allianz Worldwide Partners
74						AEGON Group
75						ING Bank
76						Zurich Insurance Group
77						Swiss Reinsurance
78						Legal & General Group
79						Sun Life Financial Inc.
80						American International Group, Inc.
81						Prudential Financial, Inc.
82						Aegon N.V.
83						Assicurazioni Generali S.p.A.
84						Zurich Insurance Group AG
85						China Tai Ping
86						PingAn
87						China Life
88						Standard Chartered
89						UOB
90						Hang Seng
91						DBS
Peer Group (2)

Cell Formulas
Range	Formula
B2	B2	=SORT(FILTER(Uni_Comp_list,BYROW(Uni_Comp_list,LAMBDA(rw,IFERROR(AGGREGATE(14,6,SEARCH(A2:A16,rw),1),0)>0))))

Named Ranges
Name	Refers To	Cells
Uni_Comp_list	=Misc!$E$2:$E$91	B2

 

[Peter_SSs]

I have updated my profile details - after checking I have Microsoft 365.

Thanks.

I have tried using your formula and it return #Name? , I suspect that I can't use the function byrow and Lambda, as it does not show any description when I enter them.

My understanding is that they should now be available to all 365 subscribers. I would first suggest under File - Account - Update Options - Update Now and see if the functions are then available.

 

[hwong8848]

Hello - although I am using Microsoft 365, however corporate limit my ability to update it
Is there any work around it so that I can recreate what you did? Could the function Let be a substitute of lambda?