Mike and Bill have a question from Dandy: "Is there a Formula that would pull values over 500,000 to a New Range?" Bill addresses the solution using VBA with a few modifications and Mike takes the Formula route. Follow along with Mike and Bill to see how to get the Range in Episode #1672.
Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #110...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Bill: Hey, alright welcome back it's another dueling Excel podcast.
I am Bill Jelen from MrExcel and I will be joined by Mike Girvin from Excel Is Fun, this is our episode 117: Pull numbers greater than 500,000 to a new range with a formula.
Alright, our question today is sent in by Dandy.
“Is their formula that would pull values > 500,000 to a new range?” Oh my gosh, all right I can't wait to see Mike's array formula.
This is just like last week I'm going to switch over to VBA and we create this new function but this function is going to return an array, isn't that cool?
So we're going to pass two things.
We're going to pass my range and then a limit so that way it doesn't have to be 500,000 we can pass that over and I am going to store the results in this temporary array called “GetOverValues()”.
I did mention that but I don't know how big it's going to be so I have to figure out from “MyRange.Rows.Count,” how many rows and how many columns then I multiply those together and then read in this temporary array from “(1 To NewSize).” That way I have enough spaces like if they ask for everything over zero well it will all be there and I want to have enough space to hold all of those and then three lines of code here for I = 1 To NewSize: initialize that position of the array with “” next i.
I bet there's a faster way to do this but I if anyone knows just shoot me a note in the YouTube comments.
There must be one line that we could do that.
Counter is equal to 0, then for each look at everything in my range if this cell value is greater than the limit increment the counter by 1.
Get over values, our temporary array is equals cell dot value and if next cell.
So that builds the link, the list of things over 500,000 The Formula: Function GetOver(MyRange As Range, Limit) Dim GetOverValues() NewSize = MyRange.Rows.Count * MyRange.Columns.Count ReDim GetOverValues(1 To NewSize) For I = 1 To NewSize: GetOverValues(i) = “”: Next i Ctr = 0 For Each cell In MyRange If cell.value >= Limit Then Ctr = Ctr + 1 GetOverValues(Ctr) = cell.Value End If Next cell GetOver = Application.Worksheetfunction.Transpose(GetOverValues) End Function The thing that drove me absolutely nuts is that it kept returning the answers going across.
All right, so if I created the array formula here it worked creating the rate formula here it did not work I had to wrap that in to transpose.
I got an idea from Chip Pearson that I could actually put application that Application.Worksheetfunction.Transpose right here in the UDF.
So two shouts to Chip Pearson, Excel MVP and also Bill Manville Excel MVP.
I found great posts from both of them that helped me to build this function here.
Now let's try it out, we'll go back and I have to select a range that's large enough to hold enough values so =getover(A1:A15,500000).
Now I can't just press ENTER as this is an array formula an it's going to return an array so I hold down Ctrl + Shift and press Enter and there we go.
It worked out perfectly.
Did I have to specify this many cell?
No I didn't but you got to be careful because if you don't specify enough cells then it's just like all other Excel array formulas.
They just truncate those results so if you don't specify enough it's going to give you the first results but then you lose the others.
So make sure that you choose a large enough range in my case I chose enough to encase every single item was a match.
All right Mike let's see what you have.
Mike: Thanks MrExcel.
Man I love that VBA.
One of these days I’ve got to catch up to you and learn some VBA.
So of course I'm stuck with the format.
Now I don't think I need an array formula here I'm going set up the hurdle in a cell and I'm going count.
So I'm going use the countif function actually, I'm going to name this range, A1:A16.
I'm going highlight field name at the top, data below and use Ctrl+Shift+F3.
That creates names from selection.
It's going use the top row and now that range right there is named sales.
So in C4 we say =COUNTIF(Sales,”>”&C2).
Then Ctrl+Enter, so it looks like there's 6.
All right.
Now, no array formula, I think I can just use a sorting formula.
Now sorting formulas for numbers are pretty straightforward.
Any other type of data, those are brutal formulas so I'm going to just use the large—whoops, if only I could type.
So the formula is =LARGE(Sales,ROWS(C$7:C7) I'm going to use a number incrementer inside the formulas because I want to extract as I copy down that K. I want the first largest, second largest, third largest so I'll just use Rows.
Rows, I'm sitting in C7 so I'm going type C$7:C7.
Notice Rows counts how many rows there are so seven to seven there is 1 row because that's locked and that's not as we go down it will increment one, two, three, four, five so Ctrl+Enter and I'm just going copy this down.
Whoops I have speak cells on enter.
Now notice when it come down here that expandable range oh so nice that Rows gives us number 4.
Now I need to just turn this off so I'm going use IF function and then I'm going to use that same number incrementer; if only I could type fast.
I can't so I'm going cheat and copy and paste Ctrl+ V. Anytime that is greater than (>) that means the count of rows is greater than that one right there F4 then what do I want?
comma that's the logical test it comes out true false comma, then what do I want a null text ring double quote double quote that will show nothing.
That's the value false, value of true I mean.
Value of false that will be our sorting numbers formula Ctrl+Enter double click and send it down.
The Formula =IF(ROWS(C$7:C7)>C$4,””,LARGE(Sales,ROWS(C$7:C7))) All right so now if I change this to 25,000 or 90,000.
A way to said we don't have any 90,000 so I'll say 55,000.
If only I could type.
All right I'll throw it back to MrExcel Bill: ah Mike, excellent what an amazing trick just using =LARGE and it even sorts the array which mine didn't do definitely a point to you.
Why did I have to go through that whole UDF?
Beautiful solution.
All right, well, hey I want to thank you for stopping by.
I’ll see you next time for another duelling Excel podcast from MrExcel and ExcelIsFun.
I am Bill Jelen from MrExcel and I will be joined by Mike Girvin from Excel Is Fun, this is our episode 117: Pull numbers greater than 500,000 to a new range with a formula.
Alright, our question today is sent in by Dandy.
“Is their formula that would pull values > 500,000 to a new range?” Oh my gosh, all right I can't wait to see Mike's array formula.
This is just like last week I'm going to switch over to VBA and we create this new function but this function is going to return an array, isn't that cool?
So we're going to pass two things.
We're going to pass my range and then a limit so that way it doesn't have to be 500,000 we can pass that over and I am going to store the results in this temporary array called “GetOverValues()”.
I did mention that but I don't know how big it's going to be so I have to figure out from “MyRange.Rows.Count,” how many rows and how many columns then I multiply those together and then read in this temporary array from “(1 To NewSize).” That way I have enough spaces like if they ask for everything over zero well it will all be there and I want to have enough space to hold all of those and then three lines of code here for I = 1 To NewSize: initialize that position of the array with “” next i.
I bet there's a faster way to do this but I if anyone knows just shoot me a note in the YouTube comments.
There must be one line that we could do that.
Counter is equal to 0, then for each look at everything in my range if this cell value is greater than the limit increment the counter by 1.
Get over values, our temporary array is equals cell dot value and if next cell.
So that builds the link, the list of things over 500,000 The Formula: Function GetOver(MyRange As Range, Limit) Dim GetOverValues() NewSize = MyRange.Rows.Count * MyRange.Columns.Count ReDim GetOverValues(1 To NewSize) For I = 1 To NewSize: GetOverValues(i) = “”: Next i Ctr = 0 For Each cell In MyRange If cell.value >= Limit Then Ctr = Ctr + 1 GetOverValues(Ctr) = cell.Value End If Next cell GetOver = Application.Worksheetfunction.Transpose(GetOverValues) End Function The thing that drove me absolutely nuts is that it kept returning the answers going across.
All right, so if I created the array formula here it worked creating the rate formula here it did not work I had to wrap that in to transpose.
I got an idea from Chip Pearson that I could actually put application that Application.Worksheetfunction.Transpose right here in the UDF.
So two shouts to Chip Pearson, Excel MVP and also Bill Manville Excel MVP.
I found great posts from both of them that helped me to build this function here.
Now let's try it out, we'll go back and I have to select a range that's large enough to hold enough values so =getover(A1:A15,500000).
Now I can't just press ENTER as this is an array formula an it's going to return an array so I hold down Ctrl + Shift and press Enter and there we go.
It worked out perfectly.
Did I have to specify this many cell?
No I didn't but you got to be careful because if you don't specify enough cells then it's just like all other Excel array formulas.
They just truncate those results so if you don't specify enough it's going to give you the first results but then you lose the others.
So make sure that you choose a large enough range in my case I chose enough to encase every single item was a match.
All right Mike let's see what you have.
Mike: Thanks MrExcel.
Man I love that VBA.
One of these days I’ve got to catch up to you and learn some VBA.
So of course I'm stuck with the format.
Now I don't think I need an array formula here I'm going set up the hurdle in a cell and I'm going count.
So I'm going use the countif function actually, I'm going to name this range, A1:A16.
I'm going highlight field name at the top, data below and use Ctrl+Shift+F3.
That creates names from selection.
It's going use the top row and now that range right there is named sales.
So in C4 we say =COUNTIF(Sales,”>”&C2).
Then Ctrl+Enter, so it looks like there's 6.
All right.
Now, no array formula, I think I can just use a sorting formula.
Now sorting formulas for numbers are pretty straightforward.
Any other type of data, those are brutal formulas so I'm going to just use the large—whoops, if only I could type.
So the formula is =LARGE(Sales,ROWS(C$7:C7) I'm going to use a number incrementer inside the formulas because I want to extract as I copy down that K. I want the first largest, second largest, third largest so I'll just use Rows.
Rows, I'm sitting in C7 so I'm going type C$7:C7.
Notice Rows counts how many rows there are so seven to seven there is 1 row because that's locked and that's not as we go down it will increment one, two, three, four, five so Ctrl+Enter and I'm just going copy this down.
Whoops I have speak cells on enter.
Now notice when it come down here that expandable range oh so nice that Rows gives us number 4.
Now I need to just turn this off so I'm going use IF function and then I'm going to use that same number incrementer; if only I could type fast.
I can't so I'm going cheat and copy and paste Ctrl+ V. Anytime that is greater than (>) that means the count of rows is greater than that one right there F4 then what do I want?
comma that's the logical test it comes out true false comma, then what do I want a null text ring double quote double quote that will show nothing.
That's the value false, value of true I mean.
Value of false that will be our sorting numbers formula Ctrl+Enter double click and send it down.
The Formula =IF(ROWS(C$7:C7)>C$4,””,LARGE(Sales,ROWS(C$7:C7))) All right so now if I change this to 25,000 or 90,000.
A way to said we don't have any 90,000 so I'll say 55,000.
If only I could type.
All right I'll throw it back to MrExcel Bill: ah Mike, excellent what an amazing trick just using =LARGE and it even sorts the array which mine didn't do definitely a point to you.
Why did I have to go through that whole UDF?
Beautiful solution.
All right, well, hey I want to thank you for stopping by.
I’ll see you next time for another duelling Excel podcast from MrExcel and ExcelIsFun.
