Tag Archives: vlookup

VLOOKUP based text grouping

You must read this article first: Grouping Text in Pivot Tables. This article explains how to combine multiple text items into a single group. This is used for classifying or for correcting spelling mistakes. If new items appear, they remain ungrouped. That way, you have to keep doing the Grouping update repetitively. An alternative is to use VLOOKUP based text grouping. Read on to find out how this is done.

Continue reading VLOOKUP based text grouping

Knowledge Pack – Data Grouping (Text, Numbers and Dates)

Analytics is all about summarizing and grouping. Numbers are grouped into bins, text is categorized and dates are compressed into months, quarters and years. This series covers various quick and powerful methods to perform sophisticated grouping in seconds.

 data Grouping in Excel Knowledge Pack

Estimated reading time 10 min

Continue reading Knowledge Pack – Data Grouping (Text, Numbers and Dates)

International Phonetic Converter using VLOOKUP

The problem

Have you ever tried any of these?

  • Calling an airline to change the booking
  • Calling a helpdesk for some troubleshooting
  • Calling your bank for some transaction
  • (or similar situations…)

In each of these cases, you have to tell lot of information accurately… PNR number, mother’s maiden name, your name, your email id …

The operator never understands your spellings correctly… and then we go into phonetic innovation…

“e for eerie … sorry.. e for eon, f for (not the first word which comes to your mind wlemoticon-winkingsmile.png) … f for for… n for Nitin (your own name always comes to your mind first), s for psychiatry … “

it is hilarious Winking smile

But it can be frustrating as well as detrimental if wrong information goes to the other side. We desperately need a better way to communicate accurate spellings to the other party.

The solution – International Phonetic Converter

To avoid this, there is an accepted international phonetic convention – which all operators know. Ships, Airlines, Armed forces – everyone uses it so that there is no confusion and mishaps.

We can also use it… but you many not have it with you when you are calling the helpdesk.

So I created a small phonetic converter tool to solve this problem. It is a simple Excel file.

Click here to open it (http://bit.ly/nspipc3 )

Just type your text and it will tell you the equivalent words.

International phonetic converter using Excel Vlookup

This will NOT work on some mobile phone browsers.
Mobile friendly version coming soon…

How does it work?

It is a simple VLOOKUP based worksheet.

You can download the Excel file from here

The link given above is showing the web editable version of this workbook using SharePoint.

Mobile friendly version coming soon…

This is one example where applied knowledge of Office can help you in many other aspects of life.  Try it out and let me know your feedback.  Thanks!

Use PowerPivot instead of VLOOKUP

This time I wanted to try a new style of writing.

I created a presentation instead of the usual document like style.

PowerPivot vs Vlookup

Download presentation and view

Download the presentation (300kb) from here

Download sample file with data and Pivot from here

View on SlideShare

Tell me if you like this style…