Mr. and Mrs.Excel are House shopping in Florida. And how does Bill prefer to look over the details? In Microsoft Excel, of course. Today, in Episode #1683, Bill Looks at using his Data Set with a variety of means to get to the Location of the home, as well as a look back to an older Podcast from 2011 [Episode #1362
] that contains VBA Code for Hyperlink Extraction. And... be sure to tune in tomorrow, because once we have our Formulas set up and our Hyperlinks extracted, Bill will be showing us how to get the Hyperlink Data for each of those links!
This mini-series is a really cool overview of some of our earlier Podcasts and how to integrate a variety of Formulas and Functions to evaluate Real Estate.
...This blog is the video podcast companion to the book, 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
And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Amazon.com: Excel 2013 In Depth (9780789748577): Jelen, Bill: Books
This mini-series is a really cool overview of some of our earlier Podcasts and how to integrate a variety of Formulas and Functions to evaluate Real Estate.
...This blog is the video podcast companion to the book, 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
And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Amazon.com: Excel 2013 In Depth (9780789748577): Jelen, Bill: Books
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL. Learn Excel from MrExcel podcast episode 1683: ISODD Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Well hey, Mrs. Excel and Mr. Excel are shopping for a house and so of course this is time for some good data analysis here and we're interested-- I saw a house for sale on this street here Via de la Reina and I want to compare this house to other sales recently.
So I'm going to go do some data mining but one thing about Via de la Reina is homes on this side of the street are on the canal and homes on this side of the street are not on the canal.
So I pulled some data from the property appraisers website but I need just the evens from Via de la Reina and also comfortable over here on Via Havarre, I don't know, I need just the odds.
So I need evens and odds for which normally you would think well hey this is a great chance to use MOD right?
But I'm not going to use MOD today I'm going use to kind of odd functions.
First I'm going get the street number.
We're going use Flash Fill here in Excel 2013 so I set 103, 109.
All right, now normally Flash Fill would have kicked in there--but it's not going to do it because it doesn't want to kick in with numbers but that's okay can we go to Data, Flash Fill and it'll say okay since you asked me to do it I'll go ahead and give you the numbers and then the question is we want to know if it is even or odd.
So up here on Via de la Reina I need ISEVEN so I’m going to use =ISEVEN(D2) of this number.
Seems really crazy because we could easily do this with the MOD function but hey it works so let's call this “Keep” and then I'm just going to cruise down here to the other street where I want the odds and I'm going to change that function to =ISODD(D65).
Double click to copy that down from there and then we will copy and paste these values.
Let's see I'm going to use the right-click + v to paste these values.
There we go and now sort descending and we get just the values that are on the canal side not the values that are across the street from the canal.
Hard to put your kayak in the water when you're just across the street from the canal especially if you're not friends with a guy across the street from you and who knows how that would go.
Now the next problem we have is that we have these account numbers over here and I need to get the hyperlinks.
I'm going to get data from each of these pages but I don't want to have to click on each one that would take way too long so I want to get the hyperlinks out so we're going to go back and grab some code that we used, I don't know a few hundred podcasts ago.
Hang on.
Okay yeah, here we go here's the code we're going to enter; Dim Hl As Hypelink Ctr = 0 For Each Hl In ActiveSheet.Hyperlinks MyRow = hl.Parent.Rox Cells(MyRow, 6) .Value = hl.Address Next hl End Sub Watch how fast this is.
Just BAM, right there and we come back and we now have all of the hyperlinks.
Tomorrow we'll take a look at how to go get data from each of those pages.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
I’m Bill Jelen.
Well hey, Mrs. Excel and Mr. Excel are shopping for a house and so of course this is time for some good data analysis here and we're interested-- I saw a house for sale on this street here Via de la Reina and I want to compare this house to other sales recently.
So I'm going to go do some data mining but one thing about Via de la Reina is homes on this side of the street are on the canal and homes on this side of the street are not on the canal.
So I pulled some data from the property appraisers website but I need just the evens from Via de la Reina and also comfortable over here on Via Havarre, I don't know, I need just the odds.
So I need evens and odds for which normally you would think well hey this is a great chance to use MOD right?
But I'm not going to use MOD today I'm going use to kind of odd functions.
First I'm going get the street number.
We're going use Flash Fill here in Excel 2013 so I set 103, 109.
All right, now normally Flash Fill would have kicked in there--but it's not going to do it because it doesn't want to kick in with numbers but that's okay can we go to Data, Flash Fill and it'll say okay since you asked me to do it I'll go ahead and give you the numbers and then the question is we want to know if it is even or odd.
So up here on Via de la Reina I need ISEVEN so I’m going to use =ISEVEN(D2) of this number.
Seems really crazy because we could easily do this with the MOD function but hey it works so let's call this “Keep” and then I'm just going to cruise down here to the other street where I want the odds and I'm going to change that function to =ISODD(D65).
Double click to copy that down from there and then we will copy and paste these values.
Let's see I'm going to use the right-click + v to paste these values.
There we go and now sort descending and we get just the values that are on the canal side not the values that are across the street from the canal.
Hard to put your kayak in the water when you're just across the street from the canal especially if you're not friends with a guy across the street from you and who knows how that would go.
Now the next problem we have is that we have these account numbers over here and I need to get the hyperlinks.
I'm going to get data from each of these pages but I don't want to have to click on each one that would take way too long so I want to get the hyperlinks out so we're going to go back and grab some code that we used, I don't know a few hundred podcasts ago.
Hang on.
Okay yeah, here we go here's the code we're going to enter; Dim Hl As Hypelink Ctr = 0 For Each Hl In ActiveSheet.Hyperlinks MyRow = hl.Parent.Rox Cells(MyRow, 6) .Value = hl.Address Next hl End Sub Watch how fast this is.
Just BAM, right there and we come back and we now have all of the hyperlinks.
Tomorrow we'll take a look at how to go get data from each of those pages.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
