Dueling Excel - Which Rows Contain Every Number - Duel 167

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Oct 31, 2014.
Check to see which rows in Excel contain every integer from 1 to 99. This Dueling Excel podcast features six different solutions, including some array formulas and a VBA function.
maxresdefault.jpg


Transcript of the video:
Hey, welcome back, it's time for another Dueling Excel podcast, I'm Bill Jelen from MrExcel I'll be joined by Mike Girvin from Excels Is Fun.
This is our episode 167.
Which Arrays Have Every Integer from 1 to 99?
Bill: All right, here's a question that was sent in.
It was actually saying: “Hey, which of your books would have a formula, that would explain this?”.
And I said “Well, clearly have all the books that I've written or that I published, this would have to be Mike Girvin's book”.
And the question is: I have a whole bunch of data sets.
Each row is a data set, there are, I think, about 300 data points going across numbers from 1 to 99.
And we want to identify which rows have every possible number from 1 to 99.
We want a formula to identify those rows and so, you know, I thought a VBA, but I said, before I try VBA, let me see if any of Mike's book has sunk in enough that I can write an array formula, that would do this.
And so, what I thought about, is using =SUMIFS.
No, =COUNTIFS(C4:KP4,ROW($1:$99), and the criteria range is we want to look through all of those values in this row, so comma, and then what do I want to look up?
I want to look up every number from 1 to 99.
And so I'm going to create an array here, I'm going to coerce an array out of this.
I'm going to say the ROW, Dollar Sign, 1, Colon, Dollar Sign, 99.
Should pop out an array from 1 to 99.
And what I expected, I would press Ctrl+Shift+Enter here, is I'm going to get the count 99 different counts, how many times did the number 1 appear, how many times did the number 2 appear, how many times did the number 3 appear and so on.
And if every number is there at least once, then the minimum of this would be one.
Alright, so I'm going to wrap that whole thing in a wrapper function, so right after the Equal Sign I put my wrapper function: =MIN(COUNTIFS(C4:KP4,ROW($1:$99)), open parenthesis, out here close parenthesis, Ctrl+Shift+Enter, alright.
So that one has a match.
Let's double-click to shoot that down, it looks like we have a measure of row 4 row 5, row 11, row 15, all right.
Let's see how this is actually working, when we look at it in formulas Evaluate Formula.
All right, so yeah, that's perfect.
The first thing it does and this is what I never know, I mean when I press Ctrl+Shift+Enter, how does Excel know, if this is the array, or if that's the array.
But somehow, here, I'm just going to call it just pure magic, right.
It figured out that that's the array.
So when I evaluate that, sure enough it turns it into array of the numbers 1 through 99, and then when it does the COUNTIFS, it's going to tell me how many times each of those numbers appear.
So the number 1 appears four times, two times, three times, three times, all right.
And there are no zeros, so every number is represented at least once.
Sometimes more than once, but at least once.
Send that whole thing through to the MIN, and we get a 1.
But we'll come down here to the exact same thing, evaluate formula, Evaluate, Evaluate, and you can see somewhere, like right about there, somewhere in the middle, also right there, there is a digit that did not manage to show up.
So, then the MIN of that is 0.
All right, so just an easy, easy way, using MIN and COUNTIFS to say, if each number is there.
Mike, let's see what you have.
Mike: Thanks, MrExcel.
Hey, that's a great array formula, I'm going to do some VBA.
No, No, I'm just kidding, I'm not going to do any VBA, I don't even know how to do a VBA.
MrExcel, that is an awesome formula.
How beautiful is that.
It does exactly what we want.
1, they're all there.
0, hey, I found a 0, so at least one of them is not there.
Now, that's a beautiful formula.
You asked how does the formula know when there's an array.
Hey, I'm just going to look at the argument, it says range, right.
So then of course it's not an array.
Actually, that is kind of true, there's a bunch of arguments that say range.
But really, what's happening is, COUNTIFS is an aggregate function and it gives you a single answer, right.
We give it that there and it knows it’s a range.
But here's how it knows when you're using functions.
And you have a function argument that's expecting a single item.
We give it a bunch of our items instead of a single cell or an empty argument.
We gave it like 16000 columns times 99 rows.
That's a lot of stuff there.
So what happens, when you give an argument, more than one item in, and expecting a single item.
Boom, when we evaluate it, it spits out multiple items.
So the key to an array operation is, you're doing an operation on multiple items, in this case it's a function argument array operation, and when you calculate it, it spits out a bunch of answers, a resultant array.
Now, the cool thing about this format, is not only is the row doing and array operation, but COUNTIFS is also, that criteria argument right there.
In fact, if you give criteria1, criteria1, if you give one of them more than one criteria, it does OR criteria.
So it's literally saying, is there a 1, is there a 2.
Or its counting.
And how many 1s are there.
Or how many 2s are there, or how many 3s there are.
So check that out, that criteria argument right there, we gave it 1 to 99, so now the COUNTIFS is instructed, criteria1 is expecting a single answer.
We give it multiple items, so when COUNTIFS evaluates.
It also spits out a resultant array, which then the MIN looks at.
In our case, it finds the smallest is 1, so we know they're all there.
Alright, so I'm going to escape and go over to the sheet.
Here, now I'm going to actually do just about the same formula except I'm going to change it up a little bit.
=MIN(COUNTIF(C4:KP4,ROW(INDIRECT(“1:99”)).
So I'll go Arrow, Arrow, Ctrl+Shift, Right Arrow, Ctrl+Backspace, Comma and then this row right here.
I'll kind of like this one, Colon, 99, F4, except for, it kind of makes me nervous to highlight 16000 or way more.
That many cells and I don't think we'll get in trouble here with it, but it certainly makes me nervous.
So I'm going to try the another alternative for generating an array of sequential numbers.
Will use ROW and then inside ROW we use INDIRECT.
And then INDIRECT can take a text string, that represents a reference and converts it back to a reference.
And then ROW will look at that and convert it to rows 1 to 99.
So, If you hit F9, you can see it's right there.
Now, the INDIRECT is a row volatile function, so it might be just as bad as highlighting all those cells.
Hey, but I'm going to close parenthesis on the main closed parenthesis and if I don't want to use Ctrl+Shift+Enter, if I evaluate either MrExcels rows, or this, or type it out, you'd never want to type it out.
You just do this and then hit F9.
Hey, I'm just going to hard code it in there, as an array, right.
If you don't need to look at it, or even if, for whatever reason, I kind of like this.
Most situations, when you actually hard code your array in like this, called an array constant, the formula won't require Ctrl+Shift+Enter.
So I'm going to Ctrl+Enter, double-click and send it down.
And it will work the same.
All right, throwback to MrExcel.
I have a sneaking suspicion there are not just two ways to do it but probably a bunch of cool ways.
Bill: Ok, how sweet is that.
An explanation from the author of the book, about how it figures out which the array is.
So you know another way that I thought to do this again, not with VBA, but just with an array formula, would be to do a MATCH.
=MATCH(ROW($1:$99),C4:KP4,0).
And I want to do a MATCH of everything from 1 to 99, like your F4 trick there, and I want to do a match into that array, Comma, 0, because we wanted an exact match.
And what's that going to do.
That's going to say, hey, if the number 1 is found, it's going to tell me where it is.
But if the number 1 is not found, it's going to return an #N/A error.
And the thing about #N/A errors is, if you add up five hundred cells and they're all numeric, except there's one #N/A, then the answer is going to be #N/A. So if I throw that MATCH into a SUM function, put a parenthesis at the end.
And I'm going to try Mike's trick here, where I take this ROW 1 to 99 and press F9 to embed an array constant in there.
=SUM(MATCH(ROW($1:$99),C4:KP4,0)).
Now, I should just be able to press Enter, if Mike is correct.
Alright, so because I got a number there, that means that they're all found, so anytime I get a number, it doesn't matter what the number is.
Those items are all found, when I have an #N/A, that means there's at least one item missing.
Mike, back to you.
Mike: Bill, MrExcel, Array-Formula-Master Jelen.
That's your new name.
Look at that.
That's another amazing function argument array operation.
Oh man, I'm going to come back over here and I'm going to use the same.
Actually, you know, I really wanted to use =FREQUENY(D4:KQ4,ROW(INDIRECT(“1:99”))).
Now, FREQUENCY would do something similar, right.
We give it the data array and here's the bins_array that's 1:99.
It would count between these bins here and give us the same numbers account, if except for one thing.
The bins, if you give it all these categories here, it always creates one more, not greater than 99, so in some cases will get the wrong answer.
Oh man, so I'm going to stick with this COUNTIFS here and instead of using MIN or converting this to an array concept, I'm going to use the AGGREGATE.
Pretty much whenever you're doing MIN or MAX with an array calculation, you can use AGGREGATE and avoid the special keystroke Ctrl+Shift+Enter.
Now, functions 1 to 13 do not allow array operations, but look at that.
14 and 19 do, and we want 15.
That small Comma, in this case we want to Ignore nothing, so we put a 4, Comma.
There's one of only four functions that have a magic argument that can handle array operations without Ctrl+Shift+Enter.
So I'm going to come to the end, Comma and the k for small, which means we just put 1, close parenthesis and Ctrl+Enter.
I don't see any curly brackets up there, double-click and send it down.
All right, throw it back to MrExcel.
Bill: Hey, all right, AGGREGATE, that is a great trick.
You know, I always wonder, why the first 11 or 12 or 13, they don't allow arrays.
That would have been cool, they only do it on the new ones, the things that weren't in subtotals.
Oh, hey, you know, enough of these array formulas.
Let's just go back to the thing that I know and love, and that would be a little bit of VBA.
So, I wrote a function called AllThere.
AllThere, where I can specify those values.
And if everything is there, it returns the text AllThere.
But if it's not, if it's not, then it shows you, which items are missing.
Let's take a look at the code I used to do this.
So, function, I call the function AllThere, and I'm passing it a range and then I created a variable with the numbers I'm looking for.
1 to 99 as an integer.
I loop through each cell in the range and if there, so let's say, the first cell has the number 27.
So it's setting the 27th value of the their array equal to 1.
Why the On Error Resume Next.
Well, actually, realize that there are some data out here that is outside of the range, like right there, Q5.
Somehow we have data that's not in the 1 to 99 range and that was me, when I created this, I must have done a bad RANDBETWEEN, I have no idea how I did that.
So just in case, we get an illegal of value.
Now, we're going to deal with that and then I build my answer.
So AllThere starts out with the word Missing.
I go from the numbers 1 to 99 and look in the array called There.
If it's not equal to 1, then I append whatever was in all there before, with that number.
And then a Comma, Space, alright.
Do that whole loop, all 99, and then, you know, I look to see, that's possible that all of them were found.
And if all of them were found, then the length of AllThere is going to be these nine characters: 4, 5, 6, 7, 8, 9.
So if it's nine, then I know there was nothing found and I change the answer from Missing to just simply say AllThere.
Now, if there were some items and we, you know, concatenated together a few different things, like here.
There's going to be a Comma, Space after the 76.
And Comma, Space after the 92.
So I want to take the answer that's stored in the AllThere, variable take the left of it.
The entire length of AllThere, minus 2 to get rid of that, final Comma, Space at the end.
And simple enough.
To build this formula =AllThere(C16:KP16), Ctrl+Shif+Right Arrow, closed parenthesis, Enter and it figures out the answer.
Mike, back to you.
Mike: Oh, My heavens.
Talk about reigning supreme.
I'm going to give you three points.
You did the COUNTIF and, man, the MATCH-some and now.
This, now, there might be a formula that could do that, but you'll have to do, you know, use the MOD, concatenate, ADDIN function, or something to join these multiple items.
So I'm not going to even do that.
That's amazing, VBA wins here, of course.
If we were going to do something with an IF, I could use “this is given 1 or 0” IF, the logical test understands 0 is FALSE, and 1 or any non-0 number as TRUE.
So if that's a non-0 number, I'm going to say “All There”.
Otherwise “I don't know VBA”.
That's just a joke there, you put something better there.
Ctrl+Enter, double-click and send it down.
Lots of “I don't know VBAs”.
All right, throwback to MrExcel.
Bill: Alright, Mike, that was great.
What a fun duel, going back and forth, and of course this duel is really a sales pitch for your book.
Ctrl+Shift+Enter, Mastering Excel Array Formulas.
Because, you know, I picked up that book, I read that book, and while I always could do a couple of simple little nominal array formulas, I've really started to learn enough from that book to be able to pull out a few good things.
And of course, if you want to go the VBA way, then my book, VBA and Macros for Microsoft Excel 2013.
So hopefully everyone picked up a few good tricks here and the great news: the person who wrote to me last weekend said: hey, if I buy a Mike's book, will I be able to do this type of problem solving?
Absolutely, the answer is yes.
All right, well hey I want to thank everyone for stopping by, we'll see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
 

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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