If List Contains a Value which is not in another List

[VladoR]

Hello,

I have one report in which is one column in which all the values should be picked from another list. I would like to make a check field, which would show e.g. 1 (or sum of 1s) if in the column is a value, which is not in another list. Here is an example.

Column1 (unique values)
A
B
C

Column2 (consolidated inputs)
A
B
A
B
A
B
C
D

I would like to display 1 if in Column2 is a character that is not listed in Column1 or zero if all characters in Column2 are in Column1 as well.

Do you have any idea how to do it, please?

 

[GraH]

Do you mean something like below?

Book1
	A	B	C	D	E	F
1	Column1 (unique values)				Column2 (consolidated inputs)
2	A				A	0
3	B				B	0
4	C				A	0
5					B	0
6					A	0
7					B	0
8					C	0
9					D	1
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=1*NOT(ISNUMBER(XMATCH(E2:E9,A2:A4)))
Dynamic array formulas.

 

[VladoR]

Something like that, but I need to have all the values of column F in a single cell. Is that possible?

 

[GraH]

Wrap it inside a sum function. You won't know which one is missing like this.

Book1
	A	B	C	D	E	F	G	H
1	Column1 (unique values)				Column2 (consolidated inputs)
2	A				A	1
3	B				B
4	C				A
5					B
6					A
7					B
8					C
9					D
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(1*NOT(ISNUMBER(XMATCH(E2:E9,A2:A4))))

 

[kvsrinivasamurthy]

Column1 A2:A4

Column2 A10:A18

=IF(SUMPRODUCT(COUNTIF(A2:A4,A10:A18))=ROWS(A10:A18),0,1)

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C
1	Column1 (unique values)	Column2 (consolidated inputs)
2	A	A	1
3	B	B
4	C	A
5		B
6		A
7		B
8		C
9		D
10
Lists

Cell Formulas
Range	Formula
C2	C2	=COUNT(FILTER(ROW(B2:B9),ISNA(MATCH(B2:B9,A2:A4,0))))

 

[VladoR]

Hello all,

many thanks for your replies. All option works for me well.

Regarding this point "You won't know which one is missing like this." it's fine for me. I want a value different from zero and set up conditional formatting to display the text "Please review your inputs" which is originally white.

Just one more point please, is it somehow possible to omit blank cells? I extended the range in column1 with one more blank cell, but if the range in column2 has some blank cells, they are counted as different compared to column1. I hope you understand what I mean. If not, I will try to rearrange my question

Thank you

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	Column1 (unique values)	Column2 (consolidated inputs)
2	A	A	1
3	B	B
4	C	A
5		B
6		A
7		B
8		C
9		D
10
11
12
Main

Cell Formulas
Range	Formula
C2	C2	=COUNT(FILTER(ROW(B2:B20),(B2:B20<>"")*(ISNA(MATCH(B2:B20,A2:A10,0)))))

 

[VladoR]

Great! Thanks a lot.

 

[VladoR]

And hopefully last question. Is it somehow possible to adjust the formula to work also in a case in which column2 can contain various values which can't be defined by a list? In my case, the correctly entered value starts with FIN001-, so if there is something different (e.g. FIN002) it will display a different value from zero? I tried to use in column1 value "FIN001*", but it doesn't work.