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.

 

[DougStroud]

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.

Hi Eric,

I have updated the values in my spreadsheet and now the active range is 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 P4; =IFERROR(INDEX($B$4:$B$349,SMALL(IF($B$4:$B$349<>"",IF(ISERROR(MATCH($B$4:$B$349,$M$2:$M$38,0)),ROW($B$4:$B$349)-ROW($B$4)+1)),ROWS($M$2:$M2))),"").

But it is not revealing the omitted codes after running the formula. I am getting a zero, "0" in P4. Ctl+Shift+Enter is working though, fyi.

 

[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.

Try this in Q4 and copy down:

=IFERROR(INDEX(B$4:B$349,MATCH(0,INDEX(COUNTIF(M$4:M$40,B$4:B$349)+COUNTIF(Q$3:Q3,B$4:B$349)+(B$4:B$349=""),),0)),"")


[TABLE="class: grid, width: 370"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]List1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]List2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Not in List2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]CR[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]CRN[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CR[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]ZZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CRN[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]CDEF[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]ZZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CDEF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]*****[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]*******[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]***********[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[Eric W]

=IFERROR(INDEX($b$4:$b$349,SMALL(IF($b$4:$b$349<>"",IF(ISERROR(MATCH($b$4:$b$349,$M$4:$M$40,0)),ROW($b$4:$b$349)-ROW($b$4)+1)),ROWS($p$4:$p4))),"")

with control+Shift+Enter.

The ranges in red should be the set. The range in blue is the subset. The range in green is the cell where you place this formula.

Excel 2012

	B	L	M	P
a	c	a
b	e	d
c	b	f
d
e
f

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P4[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$4:$B$349,SMALL(IF($B$4:$B$349<>"",IF(ISERROR(MATCH($B$4:$B$349,$M$4:$M$40,0)),ROW($B$4:$B$349)-ROW($B$4)+1)),ROWS($P$4:$P4))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]