EXCEL how to judge whether a cell contains another character in the column, and the output meets the-CodePudding

A table has two columns, as shown in the

Problem: to find whether A column contains the company's name in the column B, and C in column B output column corresponding values, if contain more than one cell, the output is the highest value
For example: A7 and contains columns B B2, B3, B4, B19, the value of output in the C7 B2

Actual situation two columns are more than 100 lines, is there a big can make the formula or VBA in excel

CodePudding user response:

 Sub pickup () 
Dim (a) As String, (b) As String 
Dim the value As a String, the row As an Integer, the index As an Integer 

The row=0: index=1 

For I=1 To Sheet1. UsedRange. Rows. Count 
Value=https://bbs.csdn.net/topics/Cells (I, 2) 
If the value & lt;> "" Then 
The index=index + 1 
ReDim Preserve b (index) As String 
B=value (index) 
End the If 
If Cells (I, 1) & lt;> "" Then row=row + 1 
Next 
For I=1 To row 
Value=https://bbs.csdn.net/topics/Cells (I, 1) 
For j=0 To index 
If InStr (value, b (j)) & gt; 0 Then 
Cells (I, 3)=b (j) 
The Exit For 
End the If 
Next 
Next 
End Sub