Change short macro to a function (UDF)

[ChristineJ]

Sample cell contents

G5 contains 10
G6 contains 20
G7 contains 30
G8 contains 40

AA15 contains G5, G6, G7, G8

AB15 contains 10, 15, 20, 35, 40

I've been using the following macro to compare the numbers in cell AB15 to those in cells G5, G6, G7, and G8 --- and to return any where there is not a match into cell AC15. In this example, the result in AC15 would be 15, 35.

Sub FindBadNums()
Dim A, S, K, T&, Ta&, Op$

S = Split(Range("AA15"), ", ")
A = Split(Range("AB15"), ", ")

For T = 0 To UBound(A)
K = A(T) + 0
    For Ta = 0 To UBound(S)
    If Range(Trim(S(Ta))) = K Then K = "": Exit For
    Next Ta
If K <> "" Then Op = Op & ", " & K
Next T

If Op <> "" Then Range("AC15") = Mid(Op, 2)
End Sub

Is there a way to convert this to a function that could be entered into cell AC15, such as something like =FindBadNums(AB15)?

Thanks!

 

[Snakehips]

@ChristineJ Give this a try.
Copy the code to a Code Module

Function FindBadNums(GoodNums, CheckNums) As String
Dim A, S, K, T&, Ta&, Op$
S = Split(GoodNums, ", ")
A = Split(CheckNums, ", ")

For T = 0 To UBound(A)
K = A(T) + 0
    For Ta = 0 To UBound(S)
    If Range(Trim(S(Ta))) = K Then K = "": Exit For
    Next Ta
If K <> "" Then Op = Op & ", " & K
Next T
If Op <> "" Then FindBadNums = Mid(Op, 2)

End Function

Book1
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
5	10
6	20
7	30
8	40
9
10
11
12
13
14
15																					G5, G6, G7, G8	10, 15, 20, 35, 40	15, 35
16
Sheet6

Cell Formulas
Range	Formula
AC15	AC15	=FindBadNums(AA15,AB15)

HTH

 

[Vogateer]

I think you can just make it a public function and then use "FindBadNums" in a formula.

Public Function FindBadNums(rng, nums) As String

Dim A, S, K, T&, Ta&, Op$

S = Split(rng, ", ")
A = Split(nums, ", ")

For T = 0 To UBound(A)
K = A(T) + 0
    For Ta = 0 To UBound(S)
    If Range(Trim(S(Ta))) = K Then K = "": Exit For
    Next Ta
If K <> "" Then Op = Op & ", " & K
Next T


If Op <> "" Then FindBadNums = Mid(Op, 2)

End Function

 

[ChristineJ]

Thanks so much! Works perfectly.

 

[Dave Patton]

With Excel 365, try a formula like the following

T202405.xlsm
	A	B	C	D	E
1
2				10, 15, 20, 35, 40
3
4
5		10
6		20
7		30
8		40
9
10			15	35
2h

Cell Formulas
Range	Formula
C10:D10	C10	=LET(a,--TEXTSPLIT(D2,","),FILTER(a, NOT(COUNTIF(B5:B8, a))))
Dynamic array formulas.

 

[Dave Patton]

With Excel 365, try the following. I do not know if you want the results in one cell or separate cells; consequently several different versions.

T202405.xlsm
	A	B	C	D	E	F	G
1
2				10, 15, 20, 35, 40
3				15, 35		15, 35
4				15	35	15	35
5		10
6		20
7		30
8		40
9
2h

Cell Formulas
Range	Formula
D3	D3	=LET(r_1,D2,r_2,B5:B8,a,--TEXTSPLIT(r_1,","),ARRAYTOTEXT(FILTER(a, NOT(COUNTIF(r_2, a)))))
D4:E4	D4	=LET(r_1,D2,r_2,B5:B8,a,--TEXTSPLIT(r_1,","),FILTER(a, NOT(COUNTIF(r_2, a))))
F3	F3	=MissingNums(D2,B5:B8)
F4:G4	F4	=Distinct_a(D2,B5:B8)
Dynamic array formulas.

Lambda Functions
Name	Formula
Distinct_a	=LAMBDA(r_1,r_2, LET(a, --TEXTSPLIT(r_1, ","), FILTER(a, NOT(COUNTIF(r_2, a))) ) )
MissingNums	=LAMBDA(r_1,r_2,LET(a, --TEXTSPLIT(r_1, ","),ARRAYTOTEXT(FILTER(a, NOT(COUNTIF(r_2, a)))) ))