Recently, my daughter, Sarah, broke her foot, and has had two surgeries, with orthopaedic specialists trying to put all the pieces back together. I stayed with her for a few days after the latest surgery, to help her out.
One night, just as I was dozing off, I got a message on my iPhone. It was my daughter, texting from the next room.
- "Are you still awake?"
- "Yes, what do you need?"
- "I need help with Excel."
That made me laugh, and I went in to see what help she needed.
Event Planning with Excel
Sarah is the event planner at Movember Canada, and is organizing launch parties for this year's Movember events, across Canada.
"During November each year, Movember is responsible for the sprouting of moustaches on thousands of men’s faces, in Canada and around the world. With their “Mo’s”, these men raise vital funds and awareness for men's health, specifically prostate cancer and male mental health initiatives."
Of course, event planning is more difficult when you're confined to your bed, but she's been going non-stop, despite her injuries. Apparently the surgical staff had to pry the phone from her hand in the last seconds before she was wheeled into the operating room. Now that's job dedication!
Sarah had a list of invitees to the Movember events, and was trying to match email addresses with a short list of people who had not responded to their invitations. The short list did not include the city name, and she wanted to pull that data from the original list.
Here, using some fake data that I created, is an example of the Excel worksheet, with the long list, and short list.
Solving the Problem With Index and Match
Like many other Excel problems, this one can be solved with a combination INDEX/MATCH formula. The MATCH function finds the email in the original list, and the INDEX function pulls the City from that email address' row.
In cell J4, I entered this formula:
The formula looks for the city in column D, in the row where the matching email address was found in column E.
Next, I copied the formula down to the last row in the short list, and all the cities showed up in the short list.
Finally, I copied the City cells in the short list, and pasted them as values, because the formulas weren't needed any more.
Back to Sleep
It only took a couple of minutes to fix my daughter's Excel problem, and she thanked me for coming to the rescue.
It warms a mother's heart to know that her children grew up to be productive adults, who use Excel every day. I'm sure that Sarah won't remember that formula, but that will give her a reason to call me the next time that she's stuck in Excel.
After helping out, I headed back to the guest room, and fell asleep. I'm not sure how much longer Sarah kept working, but probably way too long.
And if you're growing a moustache in support of Movember, please let me know!
Excel Dashboard Course Re-opens
There's good news if you missed the last session of this course -- for a limited time Mynda Treacy from My Online training Hub is opening her Excel Dashboard Course, and if you sign up by the 6th November you can get it for 20% off.
The course is video based, delivered online and is available 24/7. You also receive comprehensive workbooks and sample dashboards to keep. There’s even an option to download the videos.
The previous classes were very successful, and you can read the glowing reviews from the students, who loved all the techniques that they learned in the course, and are using them to impress their colleagues.
Click here to find out details of the course, read the student comments, and watch the 'behind the scenes' video that shows you what you'll receive as a member.
Remember, if you sign up by the 6th November you can get the dashboard course for 20% off.