Sat 16 Jul 2016

Article 41 - Findings from the VLOOKUP and INDEX/MATCH shootout

By |Saturday, July 16th, 2016|Categories: Articles|Tags: , , , , , , , , , |0 Comments

Many articles by different experts laud the superiority of INDEX/MATCH over VLOOKUP. I decided to do a shootout myself and see whether it really makes sense to use INDEX/MATCH in place of VLOOKUP purely from the perspective of speed / time taken. I am not considering any other aspect but speed / time.

Following is the methodology to test -

1. The worksheet 100000 contains 100001 records. Column A is Numbers and column B is Text. Column C is alpha-numeric which is the result field.

(more…)

Sat 21 May 2016

Article 39 - Sorting in Excel

By |Saturday, May 21st, 2016|Categories: Articles|Tags: , , , , , , , , , , |0 Comments

First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort).

The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values > Blanks

(more…)

Sat 30 May 2015

Tips & Tricks 93 - Sorting from Left to Right

By |Saturday, May 30th, 2015|Categories: Tips and Tricks|Tags: , , |0 Comments

Generally, we do sorting in a column from top to bottom. But, what happens if the data is in rows. Then you will have to sort from left to right. This is possible in Excel.

1. Select your data in the row.
2. Data tab> Sort
OR
Home tab > Sort & Filter
OR
ALT+DS

11

3. You will get following screen. Here, you may choose appropriate action.

11

4. Pressing OK brings us to Sort screen. There, you may choose Options to bring up Sort Options. Here, you can choose Sort left to right to affect sort.

11