[In late 2013]

Me: I want to buy a new phone

She: Do you want Nexus 5 or Galaxy S5 or iPhone 5s?

*Its late 2014 and I am not done comparing.*

So today, let’s talk about an interesting comparison scenario.

Imagine you are looking at 2 lists like this and you want to know where items differ. Not **which items, **but where.

That means, you want to know which letters or words in each line are different.

Unfortunately, none of the standard features of Excel (formulas, conditional formatting, pivot tables etc.) can help us with this situation. But we don’t have to give up. We can use a simple VBA macro to instantly compare 2 lists and highlight mismatched letters or words.

[Related: How to compare 2 lists in Excel, a quick round up of techniques]

**A quick demo of our comparison macro:**

When you set out to create macros like this, the first step is to define basic algorithm (logical steps in plain English). To compare 2 sets of data, we need to do below:

- For each item in list 1
- Get corresponding item in list 2
- If they don’t match
- For word match
- For each word in first text
- Get corresponding word in second text
- Compare
- If not matched, highlight in red color
- Repeat for other words

- For letter match
- Find the first mismatched letter
- Highlight all the letters from that point in second text

- Repeat for next item in list 1
- Done

Once you write down this logic, we simply go ahead and implement it in VBA code.

The exact workings of the macro are somewhat complex. So I made a video explaining how the code works & what it can do. Please watch it below.

[see this video on our YouTube Channel]

**Click here to download the comparison macro workbook**. Examine the code to understand how it is constructed. Feel free to extend it to suit your work needs.

Every now and then, I end up having a situation where I need to compare by letter or word. I find VBA macro based solution to be perfect for this.

**What about you? **Do you compare lists? Where do you struggle with such comparisons? How would you use this macro? **Please share your thoughts & tips in comments.**

While VBA is pretty powerful & awesome, not many venture beyond the basic recorded macros. You can transform the work, career & skills by learning VBA. It is not at all difficult and anyone can learn it. Start with below links.

- Introduction to VBA & 5 part crash course
- What is a macro and how to get started with VBA
- 40+ Example VBA macros
- Course:
**Online VBA Classes from Chandoo**

]]>

**August 29, 1994**. A day that changed my life forever. Football World Cup? Russia and China de-targeting nuclear weapons against each other? Anniversary of the Woodstock festival?

No, much bigger: Two Undertakers show up at WWE Summerslam for an epic battle. Needless to say: MIND() = BLOWN().

And thus begun one boy’s journey into understanding the **phenomenon of Multiple Occurrences**.

My journey continued, when just a few years later my grandfather handed me down a precious family heirloom: A few columns of meaningless data that I could take away and analyze in Excel. You may laugh but in the 90’s, every boy only wanted two things 1) Lists of pointless data and 2) To be as bad ass as this guy:

Ohhh yeah.

All good but how best to deal with multiple occurrences? Well, it broadly involves a cunning collusion of **SMALL, LARGE, IF** and our good friend the Array formula. To explain, let’s have a look at one of granddad’s prized pointless lists:

All kinds of repetition of names exist here, so how, for example, can we look up the pointless things about ‘Das Hoff’?

A typical VLOOKUP or INDEX/MATCH combo will give us the first entry (‘Talented’), but what about the rest? The following ARRAY formula will saves us:

`SMALL(IF(Lookup Range = Lookup Value, Row(Lookup Range),Row ()-# of rows below start row of Lookup Range)`

Entered with Ctrl + Shift + Enter because it’s an Array formula

In this case:

`SMALL(IF($A$1:$A$20=$E$2,ROW($A$1:$A$20)),ROW()-2)`

Bear in mind this will give us **the position numbers of the multiple occurrences** in our main list. That’s a good start. Now we drag this formula down so we end up with another list since our need to find multiple occurrences will necessitate creating another shorter subset of the main list, even if there are just two entries. How far do we drag it down? It doesn’t matter too much but enough to capture the likely number of multiple occurrences. we’ll come back to this point in a bit.

I just want to bring your attention to the last part of our SMALL formula, in this case ROW()-2. This creates a rank; think of it as 1^{st} occurrence, 2^{nd} occurrence…as you are dragging the formula down.

* Why did I put Row()-2? *Well I placed it in a cell which is in the 3

Let’s see what happens when we put the formula in E3, search for ‘Michael Bluth’ and drag down to E7:

We can visually see there are just two entries in the main list and their position numbers have come through nicely (4 and 7). Beyond that we are met with the #NUM! error. So from here, we need to do two things

- Utilize the position number to give us value or related value from the list (i.e. do what the lookup is supposed to do!)
- Conceal the errors.

To accomplish (1) we can just put this whole thing into an INDEX formula, define an array size (same vertical dimensions as our main table), use our SMALL formula to provide the row number, then define whatever column number we want, in this case we want column 2:

INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$2,ROW($A$1:$A$20)),ROW()-2),1)

Which yields:

Now, the final bit involves wrapping all this in our trusted friend IFERROR for some easy tidying up:

IFERROR(INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$2,ROW($A$1:$A$20)),ROW()-2),1),"")

Ta da! Let’s have a quick recap of how we evolved the formula.

Let’s extend this bad boy formula and make it really work for us. Here are some select ways I have extended the Multiple Occurrence formula to help extract from challenging text data.

**Please download the workbook**, since it contains the examples for your learning pleasure.

Note: Temporarily for this next section, I am going to ignore the IFERROR and the INDEX parts purely to make the formula slighter shorter and thus a bit easier to read. Instead, what we will get are the position numbers (which are good enough to demonstrate how the formulas work). Relax, in the final section, I’ll bring them back in!

Okay, not very exciting, but if we wanted our list to be in a descending order, we simply switch the SMALL with LARGE!

`LARGE(IF($A$1:$A$20=$E$2,ROW($A$1:$A$20)),ROW()-2)`

What if just want to look for part of the text? Easy!

`SMALL(IF(IFERROR(SEARCH($G$2,$A$1:$A$20)>0,FALSE),ROW($A$1:$A$20)),ROW()-2)`

The urge to use a wildcard just won’t work due to the mechanism of an Array. Arrays require like for like comparisons and a partial text won’t correspond to a range. So we need to create TRUE and FALSE outputs, which is what wrapping the SEARCH(…)>0 in an IFERROR does.

Let’s say we are looking for a first name in a cell with a full name, we can do:

`SMALL(IF(LEFT($A$1:$A$20,LEN($I$2))=$I$2,ROW($A$1:$A$20)),ROW()-2)`

Some of you are thinking, well this can be achieved with a partial text search and most of the time you are right. But I routinely deal with tens of thousands of rows of data with varying text and used to fall foul of not preparing for every permutation or combination. It’s subtle but it can be very useful.

‘Now you’re just being silly Sohail! Who needs this?’ I’ll stand by what I said, when you work with lots of data and need to extract all kinds of things, this sort of formula soon finds a place! Unfortunately I can’t reproduce data that I’ve worked with to show you the reality of needing something like this. It’s not often but once in a while it comes and it’s quicker then VBAing!

`SMALL(IF(IFERROR(SEARCH($K$2,RIGHT($A$1:$A$20,LEN($A$1:$A$20)-SEARCH(" ",$A$1:$A$20)))>0,FALSE),ROW($A$1:$A$20)),ROW()-2)`

So we’re just searching for things past the first space, this sort of thing would need to be extended as more spaces crop up but you get the point.

What?! This is more confusing than making Time Traveling Flux Capacitors.

Okay, to make this work, let’s increase our data set, I’m going to throw in a region column for all the patriots in da house.

So now things are getting interesting. ‘Das Hoff’ is a great example; we can see from a visual inspection he covers two regions (discussing the dual German and US citizenship of the Hoff is out of the scope of this article, but just know how awesome he is!). How can we lookup the two different occurrences of ‘Das Hoff’?

Easy, but first if we harken back to the ultimate VLOOKUP trick I suggested the use of CHOOSE in an array to create ‘virtual’ helper columns, the good news is since we are in an Array format, its pretty straightforward do this without messing with VLOOKUP or CHOOSE. So we simply concatenate the Person and Region ranges and we concatenate the Person and Region lookup cells:

`=SMALL(IF($A$1:$A$20&$B$1:$B$20=$E$2&$F$2,ROW($A$1:$A$20)),ROW()-2)`

So now if we look up ‘Das Hoff’ in ‘Germany’ and ‘US’ we get:

*Das ist gut, nein? Ja, Über gut.*

Let’s go a step further; what if we wanted to separately lookup the First and Last names? Easy, same concatenation but also concatenate a space in between, like so:

`=SMALL(IF($A$1:$A$20=$K$2&" "&$L$2,ROW($A$1:$A$20)),ROW()-2)`

So if we are searching for the first name ‘Thom’ and surname ‘Morello’ we get:

There you have it. Multiple Occurrences WITH Multiple Lookups, take that to the bank!

So, now we have seen the power of what can be done with Multiple Occurrences, how else might we use this in our work? Well, in the Chandoo tradition of creating awesome dashboards let’s build a bit of interactivity in a dashboard. Now I’m not going to build a dashboard, the web’s finest materials on dashboards can already be found on Chandoo.org! No point me recreating. What if we want to create a makeshift Autofilter in the middle of a dashboard/report? We can use everything we’ve learned about Multiple Occurrences and with a bit of conditional formatting we can cook up something pretty decent.

How about we poach the multiple criteria technique from the previous section: First Name, Surname and also Region as drop downs (by using simple data validation lists) to control a table of formulas:

Let’s just look at the formula in each column of the table:

**Column 1: Person**

`IFERROR(INDEX($A$1:$C$20, SMALL(IF($A$1:$A$20&$B$1:$B$20=$F$3&" "&$F$4&$F$5, ROW($A$1:$A$20)),ROW()-2),1),"")`

**Column 2: Region**

`IFERROR(INDEX($A$1:$C$20, SMALL(IF($A$1:$A$20&$B$1:$B$20=$F$3&" "&$F$4&$F$5, ROW($A$1:$A$20)),ROW()-2),2),"")`

** Column 3: Pointless Thing**

`IFERROR(INDEX($A$1:$C$20, SMALL(IF($A$1:$A$20&$B$1:$B$20=$F$3&" "&$F$4&$F$5, ROW($A$1:$A$20)),ROW()-2),3),"")`

The only difference between these is the Column number in the INDEX formulas. Now, I am fully aware of the absurdity of having your search criteria (Name and Region) appear in the results table but it’s cool, I’m just illustrating with minimal pointless made up data. Let’s try using this:

Selecting Thom, Yorke and UK gives us a nice chunky result. And how did we get it looking so slick with expanding/contracting borders and alternating colored rows?! Easy, let’s take a closer look at the conditional formatting:

Pay close attention to the order of the conditions, it won’t work properly otherwise. The formulas used are:

For the first condition, I have selected ‘No Color’ for fill:

For the second condition, the formula is:

`=NOT(MOD(ROW(),2))`

– Choose a white fill AND complete Border around the cell.

For the last condition, the formula is:

– Choose a colored fill (I’ve gone with blue) AND complete Border around the cell.

=AND(MOD(ROW(),2)=1,H3<>"")

The last thing is to **turn the grid-lines off** or at least paint the cells in and around the table white. Have a look in the workbook if it doesn’t make sense.

**Click here to download Multiple Occurrences workbook**. It contains all the examples. Play with the formulas to learn more.

So there you go. I hope you have taken away a number of things about the value of extracting multiple occurrences from a list and a technique for enhancing interactive reporting. If there is one thing I really wanted to convey during this article, its how much I love the Hoff and we can never have enough occurrences of this Germanic demigod. If you enjoyed this article then please share it and let’s get a discussion going in the comments to see what other multiple occurrence madness we can come up with!

Thank you so much Sohail for another wonderful, intelligent & useful article. I had loads of fun reading & learning from it.

* If you enjoyed this*, please say thanks to Sohail in the comments section.

Check out Formula Forensics & Array Formula pages.

**About the author: Sohail Anwar **is a Londoner who has spent over 10,000 hours applying Excel in his professional life and earns well over 6 figures as a result. Now he is on a mission to teach professionals how to massively increase their earnings by learning and applying Excel like never before. Find out more about Sohail on Earnwithexcel and connect with him on LinkedIn.

]]>

- Excel School + Dashboards program ($30 discount)
- Excel School + VBA + Dashboards ($50 discount)
- Excel School + VBA + Power Pivot + Dashboards ($100 discount)

**I have an announcement for you.**

It is *almost *holiday time*. *Every year, Chandoo.org celebrates holidays with a 3 day sale on our most popular product – Excel School training program. The sale for this year will be,

**10th December to 12th December (Wednesday to Friday)**

You will be able to save $30, $50 or $100 on our most popular courses

- Excel School + Dashboards program ($30 discount)
- Excel School + VBA + Dashboards ($50 discount)
- Excel School + VBA + Power Pivot + Dashboards ($100 discount)

More details & links for Sale page will be available on 10th of December. Stay tuned.

It has been a while since we had a round of Excel links. Here are some cool tips & links for you.

Mike at Bacon Bits wondered how to measure the skewness of data. He shares, not one but 6 different ways to measure the skewness. Statistics is a boring subject (at least for me), but Mike makes it entertaining with statements like, “all that math talk has made me hungry. I’m off to get to get a breakfast sandwich.” Check it out (not the sandwich, but the skewness measuring techniques).

**Got numbers with different decimal point symbols? Here is a fix**

Often, I download statements from my bank account. Excel things all these numbers are text, because my bank uses , & . seperators in the number. I used to write a SUBSTITUTE formula to replace the comma with nothing so that Excel can treat the value as number. But Debra shares with us a better technique, using Text to columns feature of Excel. Read it.

**Add a line to your bubble chart to show the trend**

Krisztina at e90e50fx shares an interesting chart that combines bubble chart with a line to show trend information. Very useful when you want to tell the story of how certain thing (like market share or customer satisfaction) has varied over time.

**Going beyond Excel’s default charts**

My good friend & fellow blogger Robert had a chance to present at PASS SQL Saturday event in London recently. He talked about “Going beyond Excel’s chart gallery”. You can download the slides & workbooks from the above link. Check it out for learning several cool tips & gaining ideas on creating custom charts using Excel.

**An awesome pie chart**

I saw this doing rounds on social media. A perfect pie chart that explains why they are irrelevant & silly for most cases. (hat tip – flowing data)

So that is all for now. I will see you tomorrow with something awesome about Excel.

]]>- Why there is a gap between last & this podcast session
- About Excel operators
- Arithmetic operators
- Text operators
- Reference operators
- Comparison operators
- Closing thoughts

I am talking about Excel operators, you silly.

Do you know Excel has more than 25 operators? That is right. There are a variety of operators beyond the simple + – * and /.

In this podcast, let’s understand all about these operators and how to use them. You will learn,

- Why there is a gap between last & this podcast session
- About Excel operators
- Arithmetic operators
- Text operators
- Reference operators
- Comparison operators
- Closing thoughts

Click here to listen if the player is not showing up.

- Using wild cards in Excel formulas
- SPACE operator for finding values at intersection
- Top 10 formulas for aspiring analysts

**Excel Dashboard Templates
**

Download this podcast transcript [PDF].

My favorites are % ? * and []

**What about you?** What are your favorite operators and why? Please share your thoughts in comments.

Here is what we are going to create:

PS: this chart is inspired from an email from *Brian Coetzee*.

If you have never heard about in-cell charts, read this quick re-cap section.

* In-cell charts are light weight charts generated to fit inside a single cell. *Example in-cell charts are

- sparklines
- conditional formatting data bars
**bar charts generated with REPT formula.**

First 2 options are very straight forward. It is (3) that is exciting because it opens up a lot of possibilities for us. See below, an introduction to in-cell charts.

**For more on in-cell charts, refer to resources section at the end of this article.**

Adding a marker (like average or target or last year value) can enhance your charts greatly and provide more context. Lets understand how to add marker symbols to in-cell charts.

For simplicity sake, assume that,

- A1 has data value
- B1 has average value

Now, the marker can be in 2 places.

- Inside the bar
- Outside the bar

The basic formula for generating an in-cell bar with markers is,

=IF(A1<B1, REPT("|", A1) & REPT(" ", B1-A1) & ".", REPT("|", B1) & "." & REPT("|", A1-B1))

**How does this formula work?**

First we check if we should print the marker outside the bar or inside the bar with `IF(A1<B1`

portion.

Then, if we need to print the marker outside,

`REPT("|", A1) & REPT(" ", B1-A1) & "."`

- Print | symbol A1 times
- Print SPACE (B1-A1) times
- Print the marker symbol

Else

`REPT("|", B1) & "." & REPT("|", A1-B1)`

- Print | symbol B1 times
- Print marker
- Print | symbol A1-B1 times

**Click here to download example workbook**. It contains in-cell charts with markers. Play with the formulas to learn more.

Don’t keep your cells empty and boring. Load them with impressive analysis & charts. Learn from below resources.

- In-cell bar charts, revisited
- Use playbill to make better in-cell charts
- Murders vs. Suicides – Interactive chart
- In-cell charts + pivot tables
- Survey results – in-cell dotplot
- In-cell sales funnel chart

In-cell charts are one of my favorite charting techniques in Excel. I use them often in my reports or dashboards, when I want something quick & light-weight. They are easy to make & can look super awesome when you sprinkle a bit of conditional formatting on top.

**What about you? **Do you create in-cell charts? What are your favorite tips & techniques for working with them. Share your thoughts in comments.

Recently my iPhone 4 crashed. It is 3.5 years old. And just like any other 3 year old, it started acting weird & crazy one night. The next morning it went silent. It won’t go beyond the Apple logo whenever I start it. Since I couldn’t wait for the phone to start, I took out the SIM card (the phone is unlocked, if you are wondering) and placed it in my old Nokia phone. But alas, none of my contacts are on the SIM. They are in “cloud”.

After a day of answering phone calls from everyone including my mom as “Chandoo here”, I’ve decided to get my contacts back. So I logged in to iCloud to download a backup. And the backup was a .VCF file. It has my phone numbers in this format:

Since I wanted to have all my contact numbers in a spreadsheet, I did what any Excel nerd would do. I built a template for that.

As a first step, **download the template**.

This template can,

- Export iPhone contacts to Excel
- Create iPhone import format from a list of names & phone numbers in Excel

To export the contacts from your iPhone to Excel, follow below steps

- First back up all the contacts on your phone to iCloud
- Now, visit iCloud and select
**all**of your contacts. - Using the settings gear icon at the bottom, export your contacts to a .VCF file.
- Open the vcf file in notepad & copy everything.
- Paste the data in Data column of “export” tab of the download file.
- Names & phone numbers will be extracted in column D:J
- Filter the table so no blanks are shown in Name column
- Copy the values from Name & phone number columns and paste in a separate sheet or file
- Save.

To copy all your spreadsheet contacts to iPhone,

- Go to “Import” tab of the download file.
- Type or paste your contact information in the columns B,C & D.
- Select “VCF to copy” range (from H4 to last cell)
- Copy
- Open notepad and paste.
- Save the notepad file as contacts.vcf
- Import the VCF file to your iCloud
- Done

Since the process of exporting or importing contacts thru iCloud can be a little confusing, I made a small video explaining how the template works. See it below:

(click here to see the video on our YouTube channel)

The vCard format files are simple text files. So when pasted in Excel, all we need to do is figure out where the contact name & phone numbers are and extract them using, *what else… Excel formulas.*

**Exporting VCF to Excel:**

- This uses MATCH formula to find the line in VCF data that has the information we want.
- Then OFFSET formula to extract the corresponding line of VCF data
- And then SUBSTITUTE, MID, LEFT & TRIM formulas to extract the text portions

You can examine all these formulas by **unhiding columns C & K:Q** in the export tab of the template.

**Importing Excel data to VCF:**

- This uses INDEX formula to get a name & phone number from entered data.
- Then uses CHOOSE & SUBSTITUTE formulas to create the corresponding VCF lines
- Finally TODAY & NOW formulas to create the timestamp element of the VCF

You can examine these formulas in **columns F,G & H in the import tab**.

It was fun building something useful & immediate like this in Excel. Although, soon after I created the template, my iPhone magically sprung back to life, I will be ready next time I need to look at my contacts or load them to another phone.

**How do you like this template? **Would you use this or some other app to export / import your contacts? Please share your thoughts and tips using comments.

Neither did I until recently.

But better than that is that you don’t need to go to the freezer to get the Hotdogs, you can do it all in Excel.

I recently wrote a post detailing how to develop a model to calculate Pi by simulating throwing hotdogs using Excel.

You can read about the Excel techniques used to make this simulation here: http://www.excelhero.com/blog/2014/11/calculate-pi-by-throwing-hotdogs.html

]]>