String of numerical values based on values in other cells

[ChristineJ]

Trying to return a string of values using this basic example below:

Cell B2 contains a string of cell references separated by commas. (The actual cell references and the number of cell references can vary.)
Example: B12, K22, G9, F3, E10, A24, C11, M1, F6, J19, G12, H14
Each of these cells contains a numerical value.

Cell B4 contains a string of values separated by commas. (The amounts and the number of amounts can vary.)
Example: 4, 7, 9, 15, 18

Cell B6: Is there a formula (or VBA) so that cell B6 returns only the values from cell B4 that are NOT contained in one of the cells in B2?
Example: All of the cells listed in B2 contain a value over 20 except the following: G9 = 4; M1 = 3; H14 = 15
Result: Cell B6 shows 7, 9, 18

Thanks!

 

[KRice]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1													22		Results
2	Cells	B12, K13, G9, F3, E10, A14, C11, M1, F6, J15, G12, H14													7
3						66									18
4	Values	4, 7, 9, 15, 18
5
6						11
7
8
9							55
10					9
11			4
12		33					25
13											44
14	88							35
15										15
Sheet1

Cell Formulas
Range	Formula
O2:O3	O2	=LET(btwo,CELL("contents",INDIRECT(TEXTSPLIT(B2,,", ") ))+0,bfour, TEXTSPLIT(B4,,", ")+0,FILTER(bfour,ISNUMBER(MATCH(bfour,btwo,0))=FALSE))
Dynamic array formulas.

And if some error trapping is needed when everything in B4 matches something, so the list would be empty, or nothing is in B4, this version should handle those situations by returning a blank.

=LET(btwo,CELL("contents",INDIRECT(TEXTSPLIT(B2,,", ") ))+0,bfour, TEXTSPLIT(B4,,", ")+0,IFERROR(FILTER(bfour,ISNUMBER(MATCH(bfour,btwo,0))=FALSE,""),""))

 

[ChristineJ]

Unbelievable - works great! Thank you...that is not something I would ever have figured out on my own! C

 

[KRice]

You're welcome...happy to help.

 

[kvsrinivasamurthy]

VBA code:

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

S = Split(Range("B2"), ", ")
A = Split(Range("B4"), ",")

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("B6") = Mid(Op, 2)
End Sub

 

[ChristineJ]

View attachment 109647

VBA code:

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

S = Split(Range("B2"), ", ")
A = Split(Range("B4"), ",")

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("B6") = Mid(Op, 2)
End Sub

Amazing - this works very well! I like that it returns all the results horizontally in one cell. Thank you SO much. c

 

[KRice]

I didn't realize you preferred the list in a single cell. A formula mod to do that:

MrExcel_20240408.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1													22		Results
2	Cells	B12, K13, G9, F3, E10, A14, C11, M1, F6, J15, G12, H14													7, 23, 41
3						66
4	Values	4, 7, 9, 15, 18, 23, 41
5
6						11
7
8
9							55
10					9
11			4
12		77					18
13											44
14	88							35
15										15
Sheet1

Cell Formulas
Range	Formula
O2	O2	=LET(btwo,CELL("contents",INDIRECT(TEXTSPLIT(B2,,", ") ))+0,bfour, TEXTSPLIT(B4,,", ")+0,lst,IFERROR(FILTER(bfour,ISNUMBER(MATCH(bfour,btwo,0))=FALSE,""),""),TEXTJOIN(", ",,lst))

 

[KRice]

Another refinement to handle B4 inputs that might be comma or comma-space separated, and to sort the final outputs. Is there any chance that the B4 inputs might contain duplicate values? If so, what is the expectation?...that duplicates are considered as a singular value, or that a duplicate pair would need a corresponding duplicate pair from the B2 cell references to cancel them both? I don't know if that scenario is possible, but thought that I would ask.

MrExcel_20240408.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1													22
2	Cells	B12, K13, G9, F3, E10, A14, C11, M1, F6, J15, G12, H14
3						66
4	Values	4,23, 18, 37,13,12, 1,77, 66
5
6	Results	1, 12, 13, 23, 37				11
7
8
9							55
10					9
11			4
12		77					18
13											44
14	88							35
15										15
Sheet1

Cell Formulas
Range	Formula
B6	B6	=LET(btwo,CELL("contents",INDIRECT(TRIM(TEXTSPLIT(B2,,",")))),bfour,TEXTSPLIT(B4,,",")+0,TEXTJOIN(", ",,IFERROR(SORT(FILTER(bfour,ISNUMBER(MATCH(bfour,btwo,0))=FALSE,"")),"")))