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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
240
Zot
Z
T
  • Solved
How to return comma separated data (already set up) and return data into separately (matched) cells
Replies
3
Views
127
Fluff
Fluff
G
  • Solved
SUMIFS between two number values
Replies
2
Views
151
geckoman
G
F
  • Solved
Find size data from Item Title column to create a Size column
3 4 5
Replies
40
Views
1K
Akuini
Akuini
B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
337
etaf
etaf

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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