Chess Move at YouTube asks how to search for 423 and count all cells that contain those three digits in any sequence. This episode of the Learn Excel podcast shows how to use a helper column to sort the three digits in sequence.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episode 1834.
Find Rows with Matching Digits In Any Sequence.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today question sent in by a Chess Move at YouTube, has random three-digit numbers.
For example, you know 423 and he wants to be able to find all the numbers that contain those digits in any order.
So, 432, 324, 342, 234, 243 and so on.
Now, this question is been coming in over the course of the last month and I've been thinking about the, the best way to do this and so, let's say that we have some numbers over here and I've just filled it with the numbers 0 to 999.
First thing I think that we have to do is do a TEXT function to take that number make sure that it's really a three-digit number.
So, I'm using the text of A2 with the 000 format to make sure I'm getting three digits and then I break that apart into the left.
So, the left of B2 comma 1 which is gonna give me text of course.
So, I add 0 and then the MID of B2 starting position to for length of 1 and the right.
Which will give me at the right most digit of this three digit number.
Now, it's not really interesting up here at 000.
So, let's Freeze Panes and we'll cruise down here a little bit more.
All right! So, 184 I'm breaking that 184 into a 1 & 8 & a 4 and then we need the final result is a column that takes those digits and sorts them into a sending sequence.
All right! So, we have the 184.
I'm looking for the lowest number of the 184.
So, to do that let's just focus on this bit right here, I asked for the small of those three numbers comma 1 which gets me the lowest number and I wanna put that in the 100's place.
So, that's why I multiply it by 100 and then next take the small comma 2 which gets me the second smallest number which in this case is going to be a 4 x 10 and then finally the small comma 3 which gets me the largest number that is the 8.
Okay so, using these 1, 2, 3, 4, 5 formulas going across here I've been able to take the original data and convert it to sorted data.
All right! Now, this is kind of a hassle to have to use these five formulas.
So, I'm going to go on to the next sheet where we have one actually two to formulas.
The first formula takes the text of A2 and puts it in that format and then I've created an array formula and that array formula is doing the MID of the three digit number and it's asking for it to start at the first second and third position for a length of one.
Okay now. check this out when we asked for this and I press the F9 key, it actually creates an array of the three digits 5, 7 and 4.
Now, I multiply those three texts digits by one to get the actual numbers 574 and then pass that to the small to get the smallest of the three digits which in this case will give us four multiply that by 100.
So, we know we have 400 and then again 574 as for the second smallest which will give us the five multiply 10.
So now, we have 450 and then the third smallest which is going to give us the seven and just add that in and we will get 457.
So, it's sorting those three digits, but it's doing it in one formula could I modify this formula again to instead of B575 to put the text of A575.
Well, yes yeah I could do that in the formula that gets even longer than we wouldn't even need column B here, but I'm gonna go back right now you have to press control+shift+enter here but I now, have something that work.
So, let's say that we're looking for 423.
I have the exact same formula here to convert that 423 to a sorted variety and then it's just a simple COUNTIF, go look through all of the numbers in C2 to C1000 to see if it's equal to G2, but it struck me that once I have this, once I have all 1000 possibilities already calculated here why don't I just freeze this right because this is going to be a slow calculation and over here just build a table.
So, the original number here's the sorted version again let's go down to.
So, 192 converts to 129 and now, that I have this table if we're looking for 423 we're looking for anything where the sorted version is 234.
So, I'm using an INDEX, here you can use VLOOKUP, whichever you would like and then a simple COUNTIF.
All right! So, in the course of looking for three-digit numbers that match these digits to me and I've been thinking about this for a couple of weeks it really seems like the the best way to go is to build this helper column it takes the original numbers and sorts them and then whatever I'm looking for the 423 sort that as well and try and find everything that matches.
Now, let's just take a look here.
So, we're looking for 423 that's sorted to 234.
So, if I would turn on the filter here, and we want to see everything that matches 234, click OK.
All right! So, we're getting 234, 243, 324, 342, 423 and 432 definitely a clunky way to go, but the question keeps coming in week after, week after, week and I finally realized I might as well just put it out here on the podcast because this is the best way that I have for doing this if you have something better course leave it down into YouTube comments.
All right! Well hey, I wanna thank Chess Move for sending that question in and wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast episode 1834.
Find Rows with Matching Digits In Any Sequence.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today question sent in by a Chess Move at YouTube, has random three-digit numbers.
For example, you know 423 and he wants to be able to find all the numbers that contain those digits in any order.
So, 432, 324, 342, 234, 243 and so on.
Now, this question is been coming in over the course of the last month and I've been thinking about the, the best way to do this and so, let's say that we have some numbers over here and I've just filled it with the numbers 0 to 999.
First thing I think that we have to do is do a TEXT function to take that number make sure that it's really a three-digit number.
So, I'm using the text of A2 with the 000 format to make sure I'm getting three digits and then I break that apart into the left.
So, the left of B2 comma 1 which is gonna give me text of course.
So, I add 0 and then the MID of B2 starting position to for length of 1 and the right.
Which will give me at the right most digit of this three digit number.
Now, it's not really interesting up here at 000.
So, let's Freeze Panes and we'll cruise down here a little bit more.
All right! So, 184 I'm breaking that 184 into a 1 & 8 & a 4 and then we need the final result is a column that takes those digits and sorts them into a sending sequence.
All right! So, we have the 184.
I'm looking for the lowest number of the 184.
So, to do that let's just focus on this bit right here, I asked for the small of those three numbers comma 1 which gets me the lowest number and I wanna put that in the 100's place.
So, that's why I multiply it by 100 and then next take the small comma 2 which gets me the second smallest number which in this case is going to be a 4 x 10 and then finally the small comma 3 which gets me the largest number that is the 8.
Okay so, using these 1, 2, 3, 4, 5 formulas going across here I've been able to take the original data and convert it to sorted data.
All right! Now, this is kind of a hassle to have to use these five formulas.
So, I'm going to go on to the next sheet where we have one actually two to formulas.
The first formula takes the text of A2 and puts it in that format and then I've created an array formula and that array formula is doing the MID of the three digit number and it's asking for it to start at the first second and third position for a length of one.
Okay now. check this out when we asked for this and I press the F9 key, it actually creates an array of the three digits 5, 7 and 4.
Now, I multiply those three texts digits by one to get the actual numbers 574 and then pass that to the small to get the smallest of the three digits which in this case will give us four multiply that by 100.
So, we know we have 400 and then again 574 as for the second smallest which will give us the five multiply 10.
So now, we have 450 and then the third smallest which is going to give us the seven and just add that in and we will get 457.
So, it's sorting those three digits, but it's doing it in one formula could I modify this formula again to instead of B575 to put the text of A575.
Well, yes yeah I could do that in the formula that gets even longer than we wouldn't even need column B here, but I'm gonna go back right now you have to press control+shift+enter here but I now, have something that work.
So, let's say that we're looking for 423.
I have the exact same formula here to convert that 423 to a sorted variety and then it's just a simple COUNTIF, go look through all of the numbers in C2 to C1000 to see if it's equal to G2, but it struck me that once I have this, once I have all 1000 possibilities already calculated here why don't I just freeze this right because this is going to be a slow calculation and over here just build a table.
So, the original number here's the sorted version again let's go down to.
So, 192 converts to 129 and now, that I have this table if we're looking for 423 we're looking for anything where the sorted version is 234.
So, I'm using an INDEX, here you can use VLOOKUP, whichever you would like and then a simple COUNTIF.
All right! So, in the course of looking for three-digit numbers that match these digits to me and I've been thinking about this for a couple of weeks it really seems like the the best way to go is to build this helper column it takes the original numbers and sorts them and then whatever I'm looking for the 423 sort that as well and try and find everything that matches.
Now, let's just take a look here.
So, we're looking for 423 that's sorted to 234.
So, if I would turn on the filter here, and we want to see everything that matches 234, click OK.
All right! So, we're getting 234, 243, 324, 342, 423 and 432 definitely a clunky way to go, but the question keeps coming in week after, week after, week and I finally realized I might as well just put it out here on the podcast because this is the best way that I have for doing this if you have something better course leave it down into YouTube comments.
All right! Well hey, I wanna thank Chess Move for sending that question in and wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
