Find Missing Item between two lists

D

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,976
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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?
 
Upvote 0
Another way:


ABMN
ABA
BCCR
CPCRN
CRZZQ
CRNCDEF
P
Q
ZZ
CDEF

<tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

[TD="align: right"][/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]

</tbody>
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N1[/TH]
[TD="align: left"]{=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))),"")}[/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]
 
Upvote 0
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.
 
Upvote 0
Eric W said:
Another way:


ABMN
ABA
BCCR
CPCRN
CRZZQ
CRNCDEF
P
Q
ZZ
CDEF

<tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

[TD="align: right"][/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]

</tbody>
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]N1[/TH]
[TD="align: left"]{=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))),"")}[/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]
Click to expand...

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))),"")
 
Upvote 0
DougStroud said:
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))),"")
Click to expand...

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))),"")
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
DougStroud said:
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.
Click to expand...

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)),"")


[TABLE="class: grid, width: 286"]
<tbody>[TR]
[TD][/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]
[/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]
[/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]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]List1[/TD]
[TD="align: center"]Not in List2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]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"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]CR[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]CRN[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CR[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]ZZ[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CRN[/TD]
[TD="align: center"]CDEF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Upvote 0
markmzz said:
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)),"")


[TABLE="class: grid, width: 286"]
<tbody>[TR]
[TD][/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]
[/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]
[/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]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]List1[/TD]
[TD="align: center"]Not in List2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]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"]A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]CR[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]CRN[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]P[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CR[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]ZZ[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CRN[/TD]
[TD="align: center"]CDEF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/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]
[/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]
[/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]
[/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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]


Markmzz
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Comparing Columns of names
Replies
5
Views
306
Zot
Z
T
  • Solved
How to return comma separated data (already set up) and return data into separately (matched) cells
Replies
3
Views
189
Fluff
Fluff
N
  • Question Question
EXCEL VBA COMBO BOX/DROP DOWN LIST
Replies
1
Views
206
Peter_SSs
Peter_SSs
G
  • Solved
SUMIFS between two number values
Replies
2
Views
212
geckoman
G
S
  • Solved
Compare tables & get the missing items
Replies
6
Views
113
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top