I started a new consulting gig with NZ Ministry of Business (aside: when I told my daughter about this, she widened her eyes and said, “ministry of MAGIC!!!”). On my first day, while having lunch in breakout area, I chatted with the gentleman sitting opposite me. We got talking about this and that and eventually the topic turned to What I do at MB. So I told him that I am helping the HR with some data analysis and reporting using Excel & SQL Server. He asks me, “So you must be familiar with Excel object model”. I said, “oh, why yes”. He then asks me, “I have this problem that is bothering me for years. You see, I get a lot of data. And I use Find (Ctrl+F) to find all the cells that contain certain code. But the results are all over the place. I want to know how to extract all the finds to a target worksheet – value & address format.”
I explained him how to do this while chewing mouthfuls of rice & veggies.
But once I am home, I thought, “hey, maybe there are others out in the world who want to do this”.
So here we go.
How to find and extract all matching values
Let’s say you have some data in a range like this.
And you want to find all cells with comp in them. If the values are all in one column, you could use auto-filter to quickly filter cells with comp in them and copy paste them to a target range.You can even automate the steps a bit with advanced filter.
But what if the data can be in any column?
We can use Find (Ctrl+F) to find the values and click on “Find all” to see all results in the find box. But to extract them, we must take the red pill and escape the limitations of Excel to enter in to the exciting world of VBA.
Here is a quick demo of what our find and extract macro does.
Here is the code:
Sub findAll()
Dim findWhat As String, address As String
Dim fsr As Range, rs As Range, fCount As Long
findWhat = InputBox("Enter what you want to find?", "Find what...")
If Len(findWhat) > 0 Then
clearFinds
Set frs = Range("b4").CurrentRegion
Set rs = frs.Find(What:=findWhat)
If Not rs Is Nothing Then
address = rs.address
Do
Range("I5").Offset(fCount).Value = rs.Value
Range("J5").Offset(fCount).Value = rs.address
Set rs = frs.FindNext(rs)
fCount = fCount + 1
Loop While Not rs Is Nothing And rs.address <> address
End If
End If
End Sub
How does it work?
The code is inspired from Bill Jelen’s excellent example on Find method on MSDN.
The logic goes like this.
- We start by asking the user what they want to find and store this in findWhat string variable.
- If the string to find is not empty,
- We clear any previous find results
- We grab the current region for cell B4 (change this to the top-left of your find range)
- We look for findWhat in this range using range.Find method
- As long as Find result is not empty and not same as the first result
- We copy the value & address to I5 (change this to target range as per your workbook setup)
Download the Find and Extract workbook
Click here to download the example workbook. Play with the macro to learn its inner workings.
The rabbit hole is deep, don’t stop just here…
If you enjoyed this little macro, you are going to love VBA. Check out our free starter tutorial or extensive VBA section for more.
How would you find and extract results?
I thought the Find method approach would be slow, but I am surprised to see that on a medium sized dataset (12000 values), the macro produced results almost instantly. So I would be using it more often to iterate thru a range to find a value.
What about you? Do you have such problems at work? Do you use VBA to solve them or just ask colleagues during lunch break and hope for a miracle? Please share your approach in the comments.
21 Responses to “Red vs. Blue – 35 Cool Visualizations on 2008 US Presidential Election”
[...] Read the rest of this great post here [...]
[...] post by WP-AutoBlog Import var AdBrite_Title_Color = '0000FF'; var AdBrite_Text_Color = '000000'; var [...]
Impressive list, though a few of these clearly qualify as junk (the second one with the hairy circle segments, for example).
Also, that McCain vs. Obama tax plan comparison is wildly distorted, for a debunking and redesign see here: http://chartjunk.karmanaut.com/taxplans/
Holy information/data overload. There are some great visualizations here, but also that are not so good. This list may have been better in small chunks.
[...] Haired Dilbert has some pretty cool visualizations for the ‘08 Election featured on his blog. I really liked this [...]
Cool list!
I know another widget that might have your interest.
It shows the progression of polls and uses data from electoral-vote.com.
I think you might like it:-)
http://www.youcalc.com/apps/1221747067033
... and its easy to put on your blog and fits in your sidebar!
Make a difference, keep on voting!
@Robert .. Agree, few of the charts are not really great. thanks for link, I have updated the post with the link.
@Tony ... That was the point. I wanted to compile a huge list with all the visualizations worth a look.
@Michael .. Welcome to PHD blog 🙂 thanks for sharing that link.
[...] Check the rest out here. [...]
[...] has progressed. With one look you can see on what issues candidates debated most. Also see these 35 different visualizations on 2008 US Elections [via Information [...]
[...] Red vs. Blue - 35 Cool Visualizations on 2008 US Presidential Election Perspctv - another Election Tracking Site. Presidential Watch - what various websites are saying. The Economist’s pole - Economists prefer Obama over McCain. NYTimes - Poll Tracker. Gallup poll tracker… Google Maps Projections Tracker … [...]
[...] Aqui pode igualmente encontrar uma compilação de 35 [...]
[...] 35 Cool Visualizations on 2008 US Presidential Election - Obama vs. McCain [...]
[...] Also see these 35 visualizations from on Obama vs. McCain in US Polls. [...]
First let me say that I love this blog. I have been scouring the Internet and more than likely overlooking the obvious. Can someone lead me to the OFFICIAL source of elections results? I am looking for voter data by county or even town if possible.
The reason I ask is because on Boston.com, they listed the results by town, and have to assume that there is an offical source.
Anyway, any help you can provide will be greatly appreciated!
@Brock: Thank you so much. I guess fec.gov should put up the results as soon as all counties report the results officially. I dont know but I guess it should take a few days before the data is compiled and released to public.
Alternatively did you see what nytimes.com has to offer? They have a county level breakup of results and majority figures in visualization form.
Thanks for your help!
[...] 35 Cool Visualizations on 2008 US Presidential Election - Obama vs…. [...]
@Brock: You can get the data from USAToday site : http://www.usatoday.com/news/politics/election2008/president.htm
just scroll down and select the state name to see its county results in tabular form.
Thanks again. I also stumbled upon this. http://general-election-2008-data.googlecode.com/svn/trunk/json/votes/2008/. It appears as if there was a Google project with the data. I do not know a web programming language, but I am sure there is an easy way to catch the data and put it into a database.
[...] to give a deeper insight into the elections. A top 35 of those visualizations are listed in the Chandoo.org website. B. Shneiderman’s very interesting network analysis of the Senatorial voting patterns is [...]
ts not cool or notx