Find Missing Item between two lists

[DougStroud]

I have two groups of items which one is the master set and the second set is the subset. The subset group is missing one or more items from the master group. I need some help in writing a formula to identify the missing items.

Column A has all of the items in the list; (A, B, C, CR, CRN, P, etc...), Column M is the subset which tallies the values. I need to identify the missing item(s) in Column M.

 

[crazydragon84]

copy column A into a separate sheet, remove duplicate. Now do a vlookup on this to column M. Anything that returns as error is missing in column M. Does that help?

 

[Eric W]

Another way:

	A	B	M	N
1	A		B	A
2	B		C	CR
3	C		P	CRN
4	CR		ZZ	Q
5	CRN			CDEF
6	P
7	Q
8	ZZ
9	CDEF

<tbody>
</tbody>

Array Formulas Cell Formula N1 {=IFERROR(INDEX($A$1:$A$10,SMALL(IF( $A$1:$A$10<>"",IF(ISERROR(MATCH($A$1:$A$10,$M$1:$M$10,0)),ROW($A$1:$A$10)-ROW($A$1)+1)),ROWS($N$1:$N1))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[DougStroud]

Hi Eric,

I am a little lost. I needed to adjust the formula related to the actual columns. This is reflected below. However I am not getting an error when I CTRL/SHIFT/ENTER. Would you have a look and see if you can find it.

 

[DougStroud]

Another way:

	A	B	M	N
1	A		B	A
2	B		C	CR
3	C		P	CRN
4	CR		ZZ	Q
5	CRN			CDEF
6	P
7	Q
8	ZZ
9	CDEF

<tbody>
</tbody>

Array Formulas Cell Formula N1 {=IFERROR(INDEX($A$1:$A$10,SMALL(IF( $A$1:$A$10<>"",IF(ISERROR(MATCH($A$1:$A$10,$M$1:$M$10,0)),ROW($A$1:$A$10)-ROW($A$1)+1)),ROWS($N$1:$N1))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hi Eric,
I needed to adjust the formula to reflect the proper rows and cell ranges. The formula is returning an error- do you see where the error is? I did enter the formula with the Ctrl/Shift/Enter

=IFERROR(INDEX($b$2:$b$323,SMALL(IF($b$2:$b$323<>"",IF(ISERROR(MATCH($b$2:$b$323,$M$2:$M$38,0)),ROW$b$2:$b$323)-ROW($b$2)+1)),ROWS($m$2:$m38))),"")

 

[Eric W]

Hi Eric,
I needed to adjust the formula to reflect the proper rows and cell ranges. The formula is returning an error- do you see where the error is? I did enter the formula with the Ctrl/Shift/Enter

=IFERROR(INDEX($b$2:$b$323,SMALL(IF($b$2:$b$323<>"",IF(ISERROR(MATCH($b$2:$b$323,$M$2:$M$38,0)),ROW$b$2:$b$323)-ROW($b$2)+1)),ROWS($m$2:$m38))),"")

Assuming you're putting the formula in M2, then try this:

=IFERROR(INDEX($b$2:$b$323,SMALL(IF($b$2:$b$323<>"",IF(ISERROR(MATCH($b$2:$b$323,$M$2:$M$38,0)),ROW$b$2:$b$323)-ROW($b$2)+1)),ROWS($m$2:$m2))),"")

 

[DougStroud]

Hi Eric,

Sorry, had to handle some things last night. Still not getting this to work. I am getting an error. Ctl+Shift+Enter does not add the curly braces around the formula. Enter gives me a small yellow box with a message, ( I would upload a screen shot, but I am not permitted to attach any files), "If(logical_test, [value_if_true], [value_if_false])" appears. I am quite certain this is not relevant b/c I am using "Enter" and not the Ctl+Shift+Enter key combo to activate the formula.

My values begin in B4. I have edited the formula to reflect this-
=IFERROR(INDEX($b$4:$b$323,SMALL(IF($b$4:$b$323<>"",IF(ISERROR(MATCH($b$4:$b$323,$M$2:$M$38,0)),ROW$b$4:$b$323)-ROW($b$4)+1)),ROWS($m$2:$m2))),"")

Thanks

Doug

 

[Eric W]

Somehow a parenthesis got lost:

=IFERROR(INDEX($b$4:$b$323,SMALL(IF($b$4:$b$323<>"",IF(ISERROR(MATCH($b$4:$b$323,$M$2:$M$38,0)),ROW($b$4:$b$323)-ROW($b$4)+1)),ROWS($m$2:$m2))),"")

with Control+Shift+Enter. Also make sure that the range in blue matches your subset range.

 

[markmzz]

I have two groups of items which one is the master set and the second set is the subset. The subset group is missing one or more items from the master group. I need some help in writing a formula to identify the missing items.

Column A has all of the items in the list; (A, B, C, CR, CRN, P, etc...), Column M is the subset which tallies the values. I need to identify the missing item(s) in Column M.

Hi!

Try the formula below too.

In C4 and copy down

=IFERROR(INDEX(B$4:B$323,MATCH(0,INDEX(COUNTIF(M$4:M$38,B$4:B$323)+COUNTIF(C$3:C3,B$4:B$323)+(B$4:B$323=""),),0)),"")

	A	B	C	D	L	M	N
1
2
3		List1	Not in List2			List2
4		A	A			B
5		B	CR			C
6		C	CRN			P
7		CR	Q			ZZ
8		CRN	CDEF
9		P
10		Q
11		ZZ
12		CDEF
13
***	**	*****	***********	**	**	*******	**

<tbody>
</tbody>

Markmzz

 

[DougStroud]

Hi!

Try the formula below too.

In C4 and copy down

=IFERROR(INDEX(B$4:B$323,MATCH(0,INDEX(COUNTIF(M$4:M$38,B$4:B$323)+COUNTIF(C$3:C3,B$4:B$323)+(B$4:B$323=""),),0)),"")

	A	B	C	D	L	M	N
1
2
3		List1	Not in List2			List2
4		A	A			B
5		B	CR			C
6		C	CRN			P
7		CR	Q			ZZ
8		CRN	CDEF
9		P
10		Q
11		ZZ
12		CDEF
13
***	**	*****	***********	**	**	*******	**

<tbody>
</tbody>

Markmzz

Hi Mark,
Thanks for your effort here in finding a solution. The area has increased slightly. Column B contains the set which ranges from B4-B349 now. Column M contains the sub-set and ranges from M4-M40. I made the adjustments in your formula to what I think they should be and placed it in Column Q: beginning at Q4. I am getting "0" in Q4-Q40. B34 and B35 I know are missing "x" and "tl" from the subset. Here is the adjusted formula- =IFERROR(INDEX(B$4:B$349,MATCH(0,INDEX(COUNTIF(M$4:M$40,B$4:B$349)+COUNTIF(Q$4:Q5,B$4:B$349)+(B$4:B$349=""),),0)),""). But it is not revealing the omitted codes after running the formula.