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]

Could the function Let be a substitute of lambda?

No

corporate limit my ability to update it

1. Perhaps you could ask them to update it?
2. Failing that, will corporate allow you to use vba to solve the problem?

 

[hwong8848]

No

1. Perhaps you could ask them to update it?
2. Failing that, will corporate allow you to use vba to solve the problem?

They are quite reluctant to keeping office suite up to date.
VBA is definitely an option! Will you let me know how can I do it?

 

[Peter_SSs]

VBA is definitely an option!

Remove any formulas from column B and try this code.

Sub List_Companies()
  Dim d As Object
  Dim a As Variant, b As Variant, Results As Variant
  Dim i As Long, j As Long, k As Long
  Dim Co As String
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("Uni_Comp_List")
  ReDim Results(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    d(a(i, 1)) = 1
  Next i
  b = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For j = 1 To UBound(b)
    For i = d.Count To 1 Step -1
      Co = d.keys()(i - 1)
      If InStr(1, Co, b(j, 1), 1) > 0 Then
          k = k + 1
          Results(k, 1) = Co
          d.Remove Co
      End If
    Next i
  Next j
  With Range("B2").Resize(k)
    .Value = Results
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
  End With
End Sub

 

[hwong8848]

Remove any formulas from column B and try this code.

Sub List_Companies()
  Dim d As Object
  Dim a As Variant, b As Variant, Results As Variant
  Dim i As Long, j As Long, k As Long
  Dim Co As String
 
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("Uni_Comp_List")
  ReDim Results(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    d(a(i, 1)) = 1
  Next i
  b = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For j = 1 To UBound(b)
    For i = d.Count To 1 Step -1
      Co = d.keys()(i - 1)
      If InStr(1, Co, b(j, 1), 1) > 0 Then
          k = k + 1
          Results(k, 1) = Co
          d.Remove Co
      End If
    Next i
  Next j
  With Range("B2").Resize(k)
    .Value = Results
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
  End With
End Sub

Thank you so much! Do you mind explaining a bit with me? As I have 2 more lists to go through and hope I can edit myself rather than bugging this forum.

Template.xlsm
	A	B	C	D	E	F
1	Peer Group 1	Current Output and setup	Peer Group 2	Wildcard lookup	Peer Group 3	Wildcard lookup
2	AIA	AIA	AIA		Aegon
3	China Life	China Life	Allianz		Ageas
4	China Pacific	China Life (Taiwan)	AXA		Aflac
5	China Tai ping	China Pacific	China Tai ping		AIG
6	DBS	China Tai Ping	Great Eastern		Aviva
7	FWD	DBS	FWD		Barclay
8	Great Eastern	FWD	Generali		Bank of America
9	Hang Seng	Great Eastern	HSBC		BNP Paribas
10	Manulife	Hang Seng	Manulife		Chubb
11	Metlife	Manulife	Metlife		Cigna
12	New China Life	Manulife Financial	Ping An		Citi
13	Ping An	Manulife Financial (Canada)	Prudential Financial		DBS
14	Standard Chartered	MetLife	PCA		FTLife
15	United Overseas	MetLife, Inc.	Sun Life		Hartford
16	UOB	Ping An	Zurich Insurance		HSBC
17		Standard Chartered			ING Bank
18		Standard Chartered Bank			Legal & General
19		UOB			Morgan Stanley
20					Royal Bank of Canada
21					RBC
22					Swiss
23					Traveler
24
25
26
27
Peer Group

 

[Peter_SSs]

Do you mind explaining a bit with me?

Sure. I have altered the order of the code slightly to aid descriptions.

Sub List_Companies()
  Dim d As Object
  Dim a As Variant, b As Variant, Results As Variant
  Dim i As Long, j As Long, k As Long
  Dim Co As String
  
  'Create a dictionary (just a list in this case) of all the companies in Uni_Comp_List
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("Uni_Comp_List")
  For i = 1 To UBound(a)
    d(a(i, 1)) = 1
  Next i
  
  'Set up an array big enough just in case all Uni_Comp_List companies need to be returned
  ReDim Results(1 To UBound(a), 1 To 1)
  
  'Put the Peer Group 1 values into an array (for faster processing)
  b = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  
  'For each Peer Group value
  For j = 1 To UBound(b)
    
    'Work through the dictionary list
    For i = d.Count To 1 Step -1
      'Get the company name from the dictionary
      Co = d.keys()(i - 1)
      
      'If the Peer Group value is found in the company name then
      If InStr(1, Co, b(j, 1), 1) > 0 Then
          'Put that company name into the next row of the results array ..
          k = k + 1
          Results(k, 1) = Co
          '.. & remove that company from the dictionary as it does not need checking again
          'This will speed the future processing a little
          d.Remove Co
      End If
    Next i
  Next j
  
  'Put the results into the worksheet & sort them
  With Range("B2").Resize(k)
    .Value = Results
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
  End With
End Sub

 

[hwong8848]

Sub List_Companies()
' This macro extract similar companies from uni_Comp_list
  Dim d As Object
  Dim a As Variant, pg1 As Variant, pg2 As Variant, pg3 As Variant, Results As Variant
  Dim i As Long, j As Long, k As Long
  Dim Co As String
  
  'Create a dictionary (just a list in this case) of all the companies in Uni_Comp_List
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("Uni_Comp_List")
  For i = 1 To UBound(a)
    d(a(i, 1)) = 1
  Next i
  
  'Set up an array big enough just in case all Uni_Comp_List companies need to be returned
  ReDim Results(1 To UBound(a), 1 To 1)
  
  'Put the Peer Group values into an array (for faster processing)
  pg1 = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  pg2 = Range("C2", Range("c" & Rows.Count).End(xlUp)).Value
  pg3 = Range("E2", Range("E" & Rows.Count).End(xlUp)).Value
  
  'For each Peer Group value
  For j = 1 To UBound(pg1)
    
    'Work through the dictionary list
    For i = d.Count To 1 Step -1
      'Get the company name from the dictionary
      Co = d.keys()(i - 1)
      
      'If the Peer Group value is found in the company name then
      If InStr(1, Co, pg1(j, 1), 1) > 0 Then
          'Put that company name into the next row of the results array ..
          k = k + 1
          Results(k, 1) = Co
          '.. & remove that company from the dictionary as it does not need checking again
          'This will speed the future processing a little
          d.Remove Co
      End If
    Next i
  Next j
  
  'Put the results into the worksheet & sort them
  With Range("B2").Resize(k)
    .Value = Results
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
  End With
End Sub

I have tried and compile PG1 to PG3 as an array, but after that I am quite lost on where to edit to process these lists, a dumb way is to copy the pg1 "Ifs statement", however is there a way to do this as a loop? (Just in case there are more than 3 lists in the future)

 

[Peter_SSs]

If I have understood correctly, this is one way.

Sub List_Companies_v2()
  Dim d As Object
  Dim a As Variant, b As Variant, Results As Variant
  Dim i As Long, j As Long, k As Long, PG As Long
  Dim Co As String
  
  'Create a dictionary ready for all the companies in Uni_Comp_List
  Set d = CreateObject("Scripting.Dictionary")
  'Get the Uni_Comp_List into an array
  a = Range("Uni_Comp_List")

  'For each Peer Group
  For PG = 1 To 3
  
    'Clear dictionary & reload
    d.RemoveAll
    For i = 1 To UBound(a)
      d(a(i, 1)) = 1
    Next i
    
    'Set up an array big enough in case all Uni_Comp_List companies need to be returned
    ReDim Results(1 To UBound(a), 1 To 1)
    
    'Reset k
    k = 0
    
    'Put the Peer Group values into an array (PG * 2 - 1 will give columns A, C & E)
    b = Range(Cells(2, PG * 2 - 1), Cells(Rows.Count, PG * 2 - 1).End(xlUp)).Value
    
    'For each Peer Group value
    For j = 1 To UBound(b)
      
      'Work through the dictionary list
      For i = d.Count To 1 Step -1
        'Get the company name from the dictionary
        Co = d.keys()(i - 1)
        
        'If the Peer Group value is found in the company name then
        If InStr(1, Co, b(j, 1), 1) > 0 Then
            'Put that company name into the next row of the results array ..
            k = k + 1
            Results(k, 1) = Co
            '.. & remove that company from the dictionary as it does not need checking again
            'This will speed the future processing a little
            d.Remove Co
        End If
      Next i
    Next j
    
    'Put results into the worksheet & sort (PG * 2 will give columns B, D & F)
    With Cells(2, 2 * PG).Resize(k)
      .Value = Results
      .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
    End With
  Next PG
End Sub

Here are my results based on the previous Uni_Comp_List (which I now have in column I but not shown here)

hwong8848.xlsm
	A	B	C	D	E	F
1	Peer Group 1	Current Output and setup	Peer Group 2	Wildcard lookup	Peer Group 3	Wildcard lookup
2	AIA	AIA	AIA	AIA	Aegon	Aegon
3	China Life	China Life	Allianz	Allianz	Ageas	AEGON Group
4	China Pacific	China Life (Taiwan)	AXA	Allianz Life Insurance	Aflac	Aegon N.V.
5	China Tai ping	China Pacific	China Tai ping	Allianz SE	AIG	AFLAC
6	DBS	China Tai Ping	Great Eastern	Allianz Worldwide Partners	Aviva	Aflac Incorporated
7	FWD	DBS	FWD	Assicurazioni Generali S.p.A.	Barclay	Ageas
8	Great Eastern	FWD	Generali	AXA	Bank of America	AIG
9	Hang Seng	Great Eastern	HSBC	AXA Group	BNP Paribas	Aviva
10	Manulife	Hang Seng	Manulife	China Tai Ping	Chubb	Barclays
11	Metlife	Manulife	Metlife	FWD	Cigna	BNP Paribas
12	New China Life	Manulife Financial	Ping An	Generali	Citi	Chubb
13	Ping An	Manulife Financial (Canada)	Prudential Financial	Great Eastern	DBS	Chubb Life
14	Standard Chartered	MetLife	PCA	HSBC	FTLife	Cigna
15	United Overseas	MetLife, Inc.	Sun Life	HSBC Bank	Hartford	Citigroup
16	UOB	Ping An	Zurich Insurance	Manulife	HSBC	DBS
17		Standard Chartered		Manulife Financial	ING Bank	FTLife
18		Standard Chartered Bank		Manulife Financial (Canada)	Legal & General	Hartford Financial Services Group
19		UOB		MetLife	Morgan Stanley	HSBC
20				MetLife, Inc.	Royal Bank of Canada	HSBC Bank
21				PCA	RBC	ING Bank
22				Ping An	Swiss	Legal & General America
23				Prudential Financial	Traveler	Legal & General Group
24				Prudential Financial, Inc.		Morgan Stanley
25				Sun Life		Swiss Re
26				Sun Life Financial		Swiss Reinsurance
27				Sun Life Financial Inc.		The Hartford Financial Services Group, Inc.
28				Zurich Insurance Group		Travelers
29				Zurich Insurance Group AG
Sheet3

 

[kvsrinivasamurthy]

Try this in K2

=LET(a,FILTER(B2:B25,B2:B25<>""),b,FILTER(G2:G95,G2:G95<>""),c,IF(TRANSPOSE(MMULT(1*(TRANSPOSE(a)>0),1*ISNUMBER(SEARCH(a,TRANSPOSE(b)))))>0,b,""),d,FILTER(c,c<>""),SORT(d))

Peer Group 1	Current Output and setup				Desired Output	Uni_Comp_list		FORMULA
2	AIA	AIA			AIA	Aegon		AIA
3	China Life	China Life			China Life (Taiwan)	Ageas		China Life
4		China Life (Taiwan)			China Life	AXA		China Life (Taiwan)
5	China Pacific	China Pacific			China Pacific	AIA		China Pacific
6	China Tai ping	China Tai Ping		TRUE	China Tai Ping	BNPP Cardif		China Tai Ping
7	DBS	DBS			DBS	Aon		DBS
8	FWD	FWD			FWD	Generali		FWD
9	Great Eastern	Great Eastern			Great Eastern	Chubb		Great Eastern
10	Hang Seng	Hang Seng			Hang Seng	Chubb Life		Hang Seng
11	Manulife	Manulife			Manulife	Cigna		Manulife
12		Manulife Financial			Manulife Financial (Canada)	FWD		Manulife Financial
13		Manulife Financial (Canada)			Manulife Financial	Great Eastern		Manulife Financial (Canada)
14	Metlife	MetLife			MetLife	Hannover Re		MetLife
15		MetLife, Inc.			MetLife, Inc.	Liberty		MetLife, Inc.
16	New China Life	Ping An			N/A	Manulife		Ping An
17	Ping An	Standard Chartered			Ping An	MetLife		Standard Chartered
18	Standard Chartered	Standard Chartered Bank			Standard Chartered Bank	MunichRe		Standard Chartered Bank
19		UOB			Standard Chartered	Peak Re		UOB
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

 

[hwong8848]

V

If I have understood correctly, this is one way.

Sub List_Companies_v2()
  Dim d As Object
  Dim a As Variant, b As Variant, Results As Variant
  Dim i As Long, j As Long, k As Long, PG As Long
  Dim Co As String
 
  'Create a dictionary ready for all the companies in Uni_Comp_List
  Set d = CreateObject("Scripting.Dictionary")
  'Get the Uni_Comp_List into an array
  a = Range("Uni_Comp_List")

  'For each Peer Group
  For PG = 1 To 3
 
    'Clear dictionary & reload
    d.RemoveAll
    For i = 1 To UBound(a)
      d(a(i, 1)) = 1
    Next i
   
    'Set up an array big enough in case all Uni_Comp_List companies need to be returned
    ReDim Results(1 To UBound(a), 1 To 1)
   
    'Reset k
    k = 0
   
    'Put the Peer Group values into an array (PG * 2 - 1 will give columns A, C & E)
    b = Range(Cells(2, PG * 2 - 1), Cells(Rows.Count, PG * 2 - 1).End(xlUp)).Value
   
    'For each Peer Group value
    For j = 1 To UBound(b)
     
      'Work through the dictionary list
      For i = d.Count To 1 Step -1
        'Get the company name from the dictionary
        Co = d.keys()(i - 1)
       
        'If the Peer Group value is found in the company name then
        If InStr(1, Co, b(j, 1), 1) > 0 Then
            'Put that company name into the next row of the results array ..
            k = k + 1
            Results(k, 1) = Co
            '.. & remove that company from the dictionary as it does not need checking again
            'This will speed the future processing a little
            d.Remove Co
        End If
      Next i
    Next j
   
    'Put results into the worksheet & sort (PG * 2 will give columns B, D & F)
    With Cells(2, 2 * PG).Resize(k)
      .Value = Results
      .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
    End With
  Next PG
End Sub

Here are my results based on the previous Uni_Comp_List (which I now have in column I but not shown here)

hwong8848.xlsm
	A	B	C	D	E	F
1	Peer Group 1	Current Output and setup	Peer Group 2	Wildcard lookup	Peer Group 3	Wildcard lookup
2	AIA	AIA	AIA	AIA	Aegon	Aegon
3	China Life	China Life	Allianz	Allianz	Ageas	AEGON Group
4	China Pacific	China Life (Taiwan)	AXA	Allianz Life Insurance	Aflac	Aegon N.V.
5	China Tai ping	China Pacific	China Tai ping	Allianz SE	AIG	AFLAC
6	DBS	China Tai Ping	Great Eastern	Allianz Worldwide Partners	Aviva	Aflac Incorporated
7	FWD	DBS	FWD	Assicurazioni Generali S.p.A.	Barclay	Ageas
8	Great Eastern	FWD	Generali	AXA	Bank of America	AIG
9	Hang Seng	Great Eastern	HSBC	AXA Group	BNP Paribas	Aviva
10	Manulife	Hang Seng	Manulife	China Tai Ping	Chubb	Barclays
11	Metlife	Manulife	Metlife	FWD	Cigna	BNP Paribas
12	New China Life	Manulife Financial	Ping An	Generali	Citi	Chubb
13	Ping An	Manulife Financial (Canada)	Prudential Financial	Great Eastern	DBS	Chubb Life
14	Standard Chartered	MetLife	PCA	HSBC	FTLife	Cigna
15	United Overseas	MetLife, Inc.	Sun Life	HSBC Bank	Hartford	Citigroup
16	UOB	Ping An	Zurich Insurance	Manulife	HSBC	DBS
17		Standard Chartered		Manulife Financial	ING Bank	FTLife
18		Standard Chartered Bank		Manulife Financial (Canada)	Legal & General	Hartford Financial Services Group
19		UOB		MetLife	Morgan Stanley	HSBC
20				MetLife, Inc.	Royal Bank of Canada	HSBC Bank
21				PCA	RBC	ING Bank
22				Ping An	Swiss	Legal & General America
23				Prudential Financial	Traveler	Legal & General Group
24				Prudential Financial, Inc.		Morgan Stanley
25				Sun Life		Swiss Re
26				Sun Life Financial		Swiss Reinsurance
27				Sun Life Financial Inc.		The Hartford Financial Services Group, Inc.
28				Zurich Insurance Group		Travelers
29				Zurich Insurance Group AG
Sheet3

Thank you very much, works like a charm!

 

[Peter_SSs]

Cheers. Glad it worked for you. Thanks for the confirmation.