Check list A for missing values in list B

**JoshNZ** · 04-23-2017, 03:52 AM

I have two lists, the first containing many repititions of many names, and the second which I have manually entered all of the names on. I want to create something that warns me if I have forgotten any of the names from the first list. i.e. below would warn me of a missing entry because it doesn't contain 'E'

A A
A B
A C
B
B
B
C
C
C
E
E
E

All I want is a single cell next to the second list to display 1 or 0, (or anything conditional) to warn me if there is a missing value from the first list.

I realise it is much easier to go the other way, and to MATCH each individual value from the first list to the second list as an array, and then check that column from the second list for errors, but that seems inefficient to me as I would need to drag the formula to the bottom of the worksheet.

Is this possible without using VBA?

Alternatively.... If it were easier. The first list contains dates next to the names, and the second list is a weekly summary (the relevant fields are SUMIF'd between two input dates). Would it be possible to produce the second list, from the first list, between the two dates, removing duplicates?

Many thanks in advance!
Josh

**BadlySpelledBuoy** · 04-23-2017, 03:57 AM

Would it not be easier to copy the first list and paste it where the second list is then use "Data / Remove Duplicates"?

Or use Advanced Filter to produce a unique list?

BSB

**BadlySpelledBuoy** · 04-23-2017, 04:01 AM

Alternatively, the link below has details on creating a unique list from a non-unique list using formulas.
You could possibly adapt that to take account of the time period of interest only.

http://www.get-digital-help.com/2009...om-one-column/

BSB

**JoshNZ** · 04-23-2017, 04:17 AM

Originally Posted by BadlySpelledBuoy

Would it not be easier to copy the first list and paste it where the second list is then use "Data / Remove Duplicates"?

Or use Advanced Filter to produce a unique list?

BSB

I did consider this but, I figured it would need to be done each time I want to check if the list is missing values right? Which again isn't efficient - but if I don't come up with something I probably will do this.

I will give that a good read through and let you know what I come up with - thanks!

**Glenn Kennedy** · 04-23-2017, 04:41 AM

uSE THIS ARRAY FORMULA, COPIED DOWN AS FAR AS NEEDED:

=IFERROR(INDEX(A:A, SMALL(IF(ISERROR(MATCH($A$2:$A$13, $B$2:$B$4, 0))*(COUNTIF(C$1:C1, $A$2:$A$13)=0), (ROW($A$2:$A$13))), 1)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

**JoshNZ** · 04-23-2017, 05:57 AM

Originally Posted by Glenn Kennedy

uSE THIS ARRAY FORMULA, COPIED DOWN AS FAR AS NEEDED:

=IFERROR(INDEX(A:A, SMALL(IF(ISERROR(MATCH($A$2:$A$13, $B$2:$B$4, 0))*(COUNTIF(C$1:C1, $A$2:$A$13)=0), (ROW($A$2:$A$13))), 1)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Nevermind I worked out my error there. Seems to work well - thanks. Similar to what BSB's page is saying if I'm not mistaken. I think I'll have to read through it 3 more times to figure out how it really works :S

**JoshNZ** · 04-23-2017, 08:17 AM

Just looking through the formula builder to get my head around the array contents, I see in the example below the first array in
INDEX(Array,row_num,col_num) has its elements listed out.
1.jpg

Later in the formula for the COUNTIF(range, criteria) value, the same array only has one element of 'a'). Shouldn't the COUNTIF function be looking for the range arrays elements in the entire list of criteria arrays elements?
2.jpg

And just so I'm sure I understand the array formula in this context, COUNTIF(A1:A10, B1:B2) searches for B1 or B2, first in A1, then iterates through to A10 and returns an array containing each result?

**Glenn Kennedy** · 04-23-2017, 01:05 PM

OK, so you sorted out the first issue. The original formula that I posted covered the range of ONLY those cells with values. You can, of course, extend both (say to row 20... or whatever). That will return a single 0.

To get round that, amend the formula to:

=IFERROR(INDEX(A:A, SMALL(IF(ISERROR(MATCH($A$2:$A$20, $B$2:$B$20, 0))*(COUNTIF(C$1:C1, $A$2:$A$20)=0)*($A$2:$A$20<>""), (ROW($A$2:$A$20))), 1)),"")

In regard to subsequent point(s). I don't know formula builder. But I don't see my entire formula in either of your screenshots... have you left an element out? but, fundamentally, the answer is NO. You are looking to see which values are present in column A, but not in column B. that's what MATCH is doing. So, all the results will come from column A. The countif part is there to return each value only once.

**JoshNZ** · 04-24-2017, 04:57 AM

I didn't end up using your formula, since the short formula in the page BSB provided works fine, and I was able to understand it for the most part.

My last point wasn't so much related to the problem as much as a point of interest on its own. It's difficult to understand the functional flow of Array entered formulas without any documentation. In any other language I would of expected something searching for elements B1:B2, within array A1:A10 to return a 2x10 matrix, rather than a 10 element array only.

**Glenn Kennedy** · 04-24-2017, 05:13 AM

I must have mis-read your requirement, as I didn't see how BSB's formula would deliver (what I thought) was your desired result..

**JoshNZ** · 04-24-2017, 09:33 AM

Originally Posted by Glenn Kennedy

I must have mis-read your requirement, as I didn't see how BSB's formula would deliver (what I thought) was your desired result..

Ah, the shorter formula is the solution to my alternative requirement. Either is fine really, the list won't change much over time, a name here and there each week, but I'd be in trouble if I added a single entry in the records and forgot to bring it forward to the summary, so a caution that I've forgotten or simply automating the list both work.

I'm hesitant to tackle getting my head around your formula and I'm reluctant to use one I don't understand haha. I am interested as to how it works, if you have time to explain, but I don't expect you to go to the trouble.

Thanks a lot for all your help in any case.

Check list A for missing values in list B

LinkBack

Thread Tools

Rate This Thread

Display

Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Re: Check list A for missing values in list B

Thread Information

Users Browsing this Thread

Similar Threads

Finding missing values of a list

Finding missing values from a list

Error with PivotTable Dropdown List - Jumbled up, Values Missing and Blank Values

Make dropdown list with check box ( like the autofilter list , a check boxes inside )

Find missing values - based on looking up a list, comparing against index values

macro to add missing values at end of list

list missing values in a sequence

Bookmarks

Bookmarks

Posting Permissions