VBA / Efficient way to find concatenated values from List of values

nyounaki

New Member
Joined
Nov 9, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
HI,
I am looking to create VBA/Macro function to find concatenated values in cell from List of values was in other worksheet
In Excell :
in WorkSheet 1 "MainData" in Column B there are the following values :
A B C
1 FRA,USA,CANADA False
2 UK,FRA,GERMANY True

in WorkSheet 2 "Countries List" there is a list like the following
A
FRA
GERMANY
CANADA
USA
I am looking the statements to find FRA from WorkSheet 2 , USA from Worksheet 2 and so on ....
so when call the VBA function C1 will be False as B1 has UK which is not in the country list
can you help tell me the Statement !
Regards
Nahed
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,655
Welcome to the forum!

Are you looking for an actual VBA function, or is a native Excel function ok? With Sheet2 like this:

Book3
A
1FRA
2GERMANY
3CANADA
4USA
5
Sheet2


You can set up Sheet1 like this:

Book3
ABC
11FRA,USA,CANADATRUE
22UK,FRA,GERMANYFALSE
Sheet1
Cell Formulas
RangeFormula
C1:C2C1=AND(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))*99-98,99)),Sheet2!$A$1:$A$4,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

nyounaki

New Member
Joined
Nov 9, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum!

Are you looking for an actual VBA function, or is a native Excel function ok? With Sheet2 like this:

Book3
A
1FRA
2GERMANY
3CANADA
4USA
5
Sheet2


You can set up Sheet1 like this:

Book3
ABC
11FRA,USA,CANADATRUE
22UK,FRA,GERMANYFALSE
Sheet1
Cell Formulas
RangeFormula
C1:C2C1=AND(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))*99-98,99)),Sheet2!$A$1:$A$4,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Thanks Eric ,
This is very useful to me if I put in the sheet itself ,I am looking to validate this formula in VBA function like between If and Then and display message box or change the background color of invalid cell, something like this :

if AND(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))*99-98,99)),Sheet2!$A$1:$A$4,0))) then
Msgbox ("Some values are not selected from the List ") ;
end if
Thanks
 

nyounaki

New Member
Joined
Nov 9, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum!

Are you looking for an actual VBA function, or is a native Excel function ok? With Sheet2 like this:

Book3
A
1FRA
2GERMANY
3CANADA
4USA
5
Sheet2


You can set up Sheet1 like this:

Book3
ABC
11FRA,USA,CANADATRUE
22UK,FRA,GERMANYFALSE
Sheet1
Cell Formulas
RangeFormula
C1:C2C1=AND(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(B1,",",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))*99-98,99)),Sheet2!$A$1:$A$4,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Hi Eric ,
I am looking for an actual VBA Function please ? ,
as trying by myself using your formula: In Worksheet itself is working fine , but I tried to use in Macro using Application.Evaluate but they are always return False could you please let me know what is wrong , or if you have other way in Actual VBA function you can provide that will be perfect
Thanks
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,655
If you're using VBA, I'd recommend using native VBA functions rather than using EVALUATE on an awkward formula. Try this UDF:

VBA Code:
Public Function UnknownValue(value1 As String, range1 As Range)
Dim i As Long, v As Variant, r As Variant, dic As Object

    UnknownValue = False
    v = Split(value1, ",")
    r = range1.Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(r)
        dic(r(i, 1)) = 1
    Next i
    For i = 0 To UBound(v)
        If Not dic.exists(v(i)) Then
            UnknownValue = True
            Exit Function
        End If
    Next i
            
End Function

You would then call it on your sheet like this:

Book1
AB
1FRA,USA,CANADAFALSE
2UK,FRA,GERMANYTRUE
Sheet1
Cell Formulas
RangeFormula
B1:B2B1=unknownvalue(A1,Sheet2!$A$1:$A$4)
 
Solution

Forum statistics

Threads
1,147,451
Messages
5,741,202
Members
423,648
Latest member
steel1968

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
Top