Lookup list within cell and Compare to multiple columns

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am looking for a formula that will do the following.
I have a column (let's call it column B) that has a list of businesses separated by semicolons. I want to look at every single one of these businesses within the cell and check to see if it is either in column C or D (Primary Business or Secondary Business) of that row. If the business is found in either columns C or D, then say YES. But if there are any businesses missing, then say NO and notate which business is missing. Also, the other way around. Is the Primary and Secondary Businesses listed in the Grouped Businesses. If Yes, then say Yes. If not, then say Yes, but {name of business} is not in Grouped Businesses.
See sample below.

Is it possible to have a formula get the results shown in Column E?

Account#Grouped BusinessesPrimary BusinessSecondary BusinessAre Grouped Businesses in either the Primary Business or the Secondary Business Columns & vice versa (Y/N)
123456789​
Technology; Compliance; Operations; AMLOperationsTechnology/Compliance/AMLYes
987654321​
TechnologyTechnologyYes
234567890​
Technology; Compliance; Operations; AMLTechnologyComplianceNo, Missing Operations and AML
987654320​
TechnologyAMLNo, Missing Technology
345678901​
Technology; Compliance; Operations; AMLFinanceTechnology/Operations/AML/ComplianceYes, Finance is not in Grouped Businesses
109876543​
TechnologyAMLFinanceYes, Finance is not in Grouped Businesses


Thank you
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi Dear,
There are multiple approaches for such, none is direct, though.

Are these the only possibilities in your data set (Technology; Compliance; Operations; AML)? or additional names may be added?
Are you fine with using a helper cell? (additional cell so the formal can refer to)

Regards
 
Upvote 0
mamady said:
Hi Dear,
There are multiple approaches for such, none is direct, though.

Are these the only possibilities in your data set (Technology; Compliance; Operations; AML)? or additional names may be added?
Are you fine with using a helper cell? (additional cell so the formal can refer to)

Regards
Click to expand...
Hello,

The names may change and there will be other possibilities. I already have a column for these lines of business that we can use.
I have no issues with using a helper cell.


Thank you
 
Upvote 0
Would a row like this be possible and if so, what should the result be?

Also, do you have the LET function in your version of Excel 365?

Would a solution that required vba be acceptable?

MHamid.xlsm
BCD
10Technology; Compliance; Operations; AMLFinanceTechnology/AML/Compliance
Sheet1
 
Upvote 0
Peter_SSs said:
Would a row like this be possible and if so, what should the result be?

Also, do you have the LET function in your version of Excel 365?

Would a solution that required vba be acceptable?

MHamid.xlsm
BCD
10Technology; Compliance; Operations; AMLFinanceTechnology/AML/Compliance
Sheet1
Click to expand...
It could be possible. The result would be No, missing Operations. No, missing Finance in Grouped Businesses.
Yes, I have the LET function.
If you’re thinking of vba, then a UDF would be my preference here, but if that’s not possible then I’m still open to any form of vba.

Formula is my primary preference, then a UDF, the. Just a regular vba code button to click.
 
Upvote 0
MHamid said:
Formula is my primary preference
Click to expand...
Although I think it would technically be possible, it would be a huge formula. Here is what I came up with just to get the initial Yes/No - and I think that would be the easy bit! :eek:

MHamid.xlsm
BCDE
1Grouped BusinessesPrimary BusinessSecondary Business
2Technology; Compliance; Operations; AMLOperationsTechnology/Compliance/AMLYes
3TechnologyTechnologyYes
4Technology; Compliance; Operations; AMLTechnologyComplianceNo
5TechnologyAMLNo
6Technology; Compliance; Operations; AMLFinanceTechnology/Operations/AML/ComplianceYes
7TechnologyAMLFinanceNo
8Technology; Compliance; Operations; AMLFinanceTechnology/AML/ComplianceNo
Sheet2
Cell Formulas
RangeFormula
E2:E8E2=LET(n,LEN(B2)-LEN(SUBSTITUTE(B2,";",""))+1,seq,SEQUENCE(n),txt,"/"&SUBSTITUTE(B2,"; ","/")&"/",ary,REPLACE(LEFT(txt,FIND("#",SUBSTITUTE(txt,"/","#",seq+1))),1,FIND("#",SUBSTITUTE(txt,"/","#",seq))-1,""),IF(COUNT(SEARCH(ary,"/"&C2&"/"&D2&"/"))=ROWS(ary),"Yes","No"))



MHamid said:
then a UDF
Click to expand...
There seems to be some inconsistency about the exact wording of the expected results and I don't understand your expected result for the last example in post #1. However, see if this would be any use.

VBA Code: 
Function CheckBusinesses(sGrouped As String, sPrimary As String, sSecondary As String) As String
  Dim Itm As Variant, aBus As Variant
  Dim sPsS As String, sMissing As String, tmp As String
  Dim k As Long
  
  sPsS = "/" & sPrimary & "/" & sSecondary & "/"
  sGrouped = "/" & Replace(sGrouped, "; ", "/;/") & "/"
  aBus = Split(sGrouped, ";")
  For Each Itm In aBus
    If InStr(sPsS, Itm) = 0 Then
      sMissing = sMissing & " and " & Replace(Itm, "/", "")
    End If
  Next Itm
  If sMissing = vbNullString Then
    tmp = "Yes"
  Else
    tmp = "No, Missing " & Mid(sMissing, 6)
  End If
  CheckBusinesses = tmp
  
  sPsS = "/" & sPrimary & "/"
  If Len(sSecondary) > 0 Then sPsS = sPsS & ";/" & Replace(sSecondary, "/", "/;/") & "/"
  aBus = Split(sPsS, ";")
  sMissing = vbNullString
  tmp = vbNullString
  For Each Itm In aBus
    If InStr(sGrouped, Itm) = 0 Then
      sMissing = sMissing & " and " & Replace(Itm, "/", "")
    End If
  Next Itm
  If sMissing = vbNullString Then
    tmp = "Yes"
  Else
    tmp = "No, Missing " & Mid(sMissing, 6)
  End If
  
  CheckBusinesses = CheckBusinesses & " | " & tmp
End Function

MHamid.xlsm
BCDE
1Grouped BusinessesPrimary BusinessSecondary Business
2Technology; Compliance; Operations; AMLOperationsTechnology/Compliance/AMLYes | Yes
3TechnologyTechnologyYes | Yes
4Technology; Compliance; Operations; AMLTechnologyComplianceNo, Missing Operations and AML | Yes
5TechnologyAMLNo, Missing Technology | No, Missing AML
6Technology; Compliance; Operations; AMLFinanceTechnology/Operations/AML/ComplianceYes | No, Missing Finance
7TechnologyAMLFinanceNo, Missing Technology | No, Missing AML and Finance
8Technology; Compliance; Operations; AMLFinanceTechnology/AML/ComplianceNo, Missing Operations | No, Missing Finance
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=CheckBusinesses(B2,C2,D2)
 
Upvote 0
Thank you, Peter! I will check them out this morning and will let you know how they each work out.
 
Upvote 0
Peter_SSs said:
Although I think it would technically be possible, it would be a huge formula. Here is what I came up with just to get the initial Yes/No - and I think that would be the easy bit! :eek:

MHamid.xlsm
BCDE
1Grouped BusinessesPrimary BusinessSecondary Business
2Technology; Compliance; Operations; AMLOperationsTechnology/Compliance/AMLYes
3TechnologyTechnologyYes
4Technology; Compliance; Operations; AMLTechnologyComplianceNo
5TechnologyAMLNo
6Technology; Compliance; Operations; AMLFinanceTechnology/Operations/AML/ComplianceYes
7TechnologyAMLFinanceNo
8Technology; Compliance; Operations; AMLFinanceTechnology/AML/ComplianceNo
Sheet2
Cell Formulas
RangeFormula
E2:E8E2=LET(n,LEN(B2)-LEN(SUBSTITUTE(B2,";",""))+1,seq,SEQUENCE(n),txt,"/"&SUBSTITUTE(B2,"; ","/")&"/",ary,REPLACE(LEFT(txt,FIND("#",SUBSTITUTE(txt,"/","#",seq+1))),1,FIND("#",SUBSTITUTE(txt,"/","#",seq))-1,""),IF(COUNT(SEARCH(ary,"/"&C2&"/"&D2&"/"))=ROWS(ary),"Yes","No"))




There seems to be some inconsistency about the exact wording of the expected results and I don't understand your expected result for the last example in post #1. However, see if this would be any use.

VBA Code: 
Function CheckBusinesses(sGrouped As String, sPrimary As String, sSecondary As String) As String
  Dim Itm As Variant, aBus As Variant
  Dim sPsS As String, sMissing As String, tmp As String
  Dim k As Long

  sPsS = "/" & sPrimary & "/" & sSecondary & "/"
  sGrouped = "/" & Replace(sGrouped, "; ", "/;/") & "/"
  aBus = Split(sGrouped, ";")
  For Each Itm In aBus
    If InStr(sPsS, Itm) = 0 Then
      sMissing = sMissing & " and " & Replace(Itm, "/", "")
    End If
  Next Itm
  If sMissing = vbNullString Then
    tmp = "Yes"
  Else
    tmp = "No, Missing " & Mid(sMissing, 6)
  End If
  CheckBusinesses = tmp

  sPsS = "/" & sPrimary & "/"
  If Len(sSecondary) > 0 Then sPsS = sPsS & ";/" & Replace(sSecondary, "/", "/;/") & "/"
  aBus = Split(sPsS, ";")
  sMissing = vbNullString
  tmp = vbNullString
  For Each Itm In aBus
    If InStr(sGrouped, Itm) = 0 Then
      sMissing = sMissing & " and " & Replace(Itm, "/", "")
    End If
  Next Itm
  If sMissing = vbNullString Then
    tmp = "Yes"
  Else
    tmp = "No, Missing " & Mid(sMissing, 6)
  End If

  CheckBusinesses = CheckBusinesses & " | " & tmp
End Function

MHamid.xlsm
BCDE
1Grouped BusinessesPrimary BusinessSecondary Business
2Technology; Compliance; Operations; AMLOperationsTechnology/Compliance/AMLYes | Yes
3TechnologyTechnologyYes | Yes
4Technology; Compliance; Operations; AMLTechnologyComplianceNo, Missing Operations and AML | Yes
5TechnologyAMLNo, Missing Technology | No, Missing AML
6Technology; Compliance; Operations; AMLFinanceTechnology/Operations/AML/ComplianceYes | No, Missing Finance
7TechnologyAMLFinanceNo, Missing Technology | No, Missing AML and Finance
8Technology; Compliance; Operations; AMLFinanceTechnology/AML/ComplianceNo, Missing Operations | No, Missing Finance
Sheet1
Cell Formulas
RangeFormula
E2:E8E2=CheckBusinesses(B2,C2,D2)
Click to expand...
Hi Peter,

I like the UDF. Is it possible to ignore blanks in columns C and D and ignore a specific business like "Multi Business"?


Thank you,
Miriam
 
Upvote 0
Hello,

Does anyone have any advise on how to tweak the UDF Code to ignore blanks in columns C and D and ignore business name of "Multi Business"?


Thank you,
 
Upvote 0
MHamid said:
Is it possible to ignore blanks in columns C and D and ignore a specific business like "Multi Business"?
Click to expand...
Could we have some sample data and expected results that include these new requirements?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Cell contains several of many text strings then return multiple lookup values
2 3
Replies
26
Views
1K
MHamid
M
M
  • Question
Macro to lookup data within multiple columns
Replies
9
Views
425
MHamid
M
M
  • Solved
Comparing cell value list separated by / but exclude a text within search using Formula
Replies
5
Views
417
Peter_SSs
Peter_SSs
G
  • Solved
How to filter a table with two email columns against a list of emails
Replies
7
Views
350
dreid1011
dreid1011
M
  • Question
Formula - Search multiple values within cell value against a range of values
Replies
1
Views
408
MHamid
M

Forum statistics

Threads
1,215,054
Messages
6,122,893
Members
449,097
Latest member
dbomb1414

Share this page

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