Posts Tagged ‘Management’

Excel Tip: Compare two lists (Method 3) July 19th, 2013

Vinod Kumar

This is in continuation to our series where we want to compare two lists and find the difference. In previous posts we discussed the use of MATCH() and then VLOOKUP() functions. In this post, I will use the other popular method of PIVOT TABLES. Just to reiterate, we have two lists “List A” and “List B” and we want to know how many values in List B is not available in “List A”.

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function.

Method 3: Using PIVOT TABLE.

For PIVOT Table, I am going to prepare the above two lists into one mega list. I have added a column to denote Year and merged both these lists like below.

Now that our data is prepared, our next step is to initialize the PivotTable. Goto INSERT Tabe –> PivotTable.

The Create PivotTable dialog comes up. Under Select a table or Range, select our complete table. Under PivotTable Placement-> Select a cell in our same worksheet. I have shown a typical example below.

To aggregate Drag the corresponding columns to get the result I have shown.

Columns –> Year

Rows –> Company

Values –> Revenues

Voila, our task is complete. Now in one shot we have got, the red empty boxes are the new values in 2012 that are not available in 2011. While the blue box below represents, values in 2011 that are missing in 2012 year.

Thanks for reading this far. I hope this was useful and you learnt something new. Do let me know if you want me to write some other topic.

Continue reading...


Excel Tip: Compare two lists (Method 2) July 18th, 2013

Vinod Kumar

This is in continuation to my previous post. Just to recap the problem statement – we want to find from our “List B” which is not in “List A”. The initial dataset can be found below first.

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function.

Method 3: Using PivotTable.

As mentioned, in this blog post we will use VLOOKUP() function instead of MATCH() function. Now in F3 cell, let us start typing “=VLOOKUP(“ and press CTRL+A. This will bring the function arguments dialog.

Lookup_value: D3

Table_array: $A$3:$A$10 (this is the range to make comparison, remember F4 key after selecting the range, it will automatically lock the range using the $ synbols)

Col_index_num: 1 (the column ordinal based on range, we have just one column and hence it is 1)

Range_Lookup: FALSE (for exact match)

Press Enter. Now if a match is found you will see the value of column repeated. Next is to find the cross arrow from right corner cell and double click.

Now whereever we get an “#N/A” Error, it means we couldn’t find a match from other list.

We saw in our previous post how to get rid of the Error. We will use the “=IFERROR” function to add an value “Missing” and we will double click our right cross arrow.

Voila, now that is the second method of finding missing values in comparison to another range.

Continue reading...


Excel Tip: Compare two lists (Method 1) July 17th, 2013

Vinod Kumar

One of my friend recently had this requirement where he wanted to compare two lists and find out which were the values from second list that are not in first list. This was an interesting requirement and we kept playing with Excel to come up with close to 3 different methods. After that task was complete, I thought it is worth every bit to bring all these methods over my blog for future reference. So have fun !!!

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function

Method 3: Using PIVOTTABLE.

Let us look at the dataset in hand. It is a list of companies with some random revenue numbers listed for two years. Interestingly there are differences in both the dataset. Now the task is simple. We want to check companies in “List B” which are not in “List A”. So let us get started.

As mentioned earlier, the Method 1 is to use MATCH() function. Here is a neat trick, when you type “=MATCH(“ –> Press “CTRL+A”.

Voila, this is the magic dialog we get. This will guide us for various parameters for that given function. This trick can be done with any function which has parameters values.

Lookup Value => in our data set it is the first cell D3 from List B.

Lookup array => in our data set it will be A3:A10. Please note that we will use the same range and as we move through the list of companies. Hence press F4 to get $A$3:$A$10. This is an very important step.

Match_Type => In our example we will have 0 for exact match.

Press “OK” now.

Now we will use the cross arrow trick we used before. Move to right corner of this cell till you get the cross arrow as shown below. Now double click. This will fill the rest of the rows till the end is reached.

I didn’t mention the significance of the numbers earlier. Match() function is returning the relative position of the values based on comparision to first list. So here you can see “Johnson n Johnson” is in the 1st ordinal in our array. So is the case for others too.

The irritating factor here is the ERROR “#N/A”. The simple method is to prefix our function with the IFERROR() function. In our case, if we get n error we are going to print the text “Missing”.

Now use the cross arrow trick again. And now the final output looks like below.

So I plan to write the next two methods tomorrow. Want to guess them? If they are correct, I will approve the comments after making the posts :) … Do let me know if you want me to write on anything.

Continue reading...


Excel Tip: Remove All blanks from a Excel table July 16th, 2013

Vinod Kumar

This is one of the simplest tips among those I have shared over this blog till date. Let me explain you the scenario. We have the following table / range / data set inside Excel and the problem with these dataset is that, we have spaces in between and this is causing us problems for filter, moving between cells and many more. So our objective is to remove the rows having space. I have seen people use “CTRL+Down Arrow” to goto a row with space, delete that row – press CRTL+Down Arrow and repeat this process till you reach the last row.

This for sure is manual and intensive repetitive activity. In this blog post we will do the same operation efficiently in few clicks. Quite an handy option to know.

When you are at in this screen, Select the range on “B column” just like shown below. And now hit the “CTRL+G”. This bring the dialog as below. Select “Special…

In the following dialog select “Blanks” and press “OK”.

Voila, now you can see that from our dataset the Blank rows are highlighted as shown below.

Just right Click and select “Delete” –> “Table Rows”. Now that is as simple as it gets.

We have successfully deleted all the rows with blanks and our final dataset looks like:

Excel is an awesome tool and there are many such functions that make it interesting. Hope you will use this shortcut in your day-today work and hope you found this trick useful. Do let me know if you want me to write on anything specific and I would love to explore and show you few tricks.

Continue reading...


SQL Server Enhanced FORCESEEK and FORCESCAN Query hint July 11th, 2013

Vinod Kumar

I wrote a number of posts around SQL Server hints earlier. Some are: SQL Server–Plan Freezing, SQL Server FORCESEEK Hint, SQL Server TABLE HINT. In this blog post, let me revisit some of them and give a spin of the enhancements to these hints from an implementation perspective.

FORCESEEK was first introduced in SQL Server 2008 version. It allows a user to specify on a specific table such that SQL Server Optimizer will only use seeks for this table. However, we cannot specify which index to use and which column to seek on.

The enhanced version of FORCESEEK with SQL Server 2012 will allow us to specify index hint together which columns to seek on. This will give users tighter control on which index and which column to seek on. The syntax is that you need to specify both index name and columns names (forceseek (x (y1, y2)) where x is index name and y1 and y2 are column names.

Forceseek example

Here is a typical example.

CREATE TABLE t_forceseek (col1 INT, col2 INT)


CREATE INDEX IDX_t_forceseek ON t_forceseek(col1, col2)


SELECT * FROM t_forceseek WITH (forceseek (IDX_t_forceseek(col1, col2)))

WHERE col1 = 0 and col2=1

In XML plan, we will see ForceSeek="1" for the particular operator that does the seek. Additionally, NColsForceSeek will indicate how many columns are being seeked on.

<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="1" NColsForceSeek="2" ForceScan="0" NoExpandHint="0" Storage="RowStore">

FORCESCAN Enhancements

It is not always efficient to do seeks. In situations where underestimate occurs, SQL Optimizer will most likely choose a seek plan which may run slower. FORCESCAN was introduced to force a scan on an index or table.

Below is a typical usage:


When used with an table, it would look like:

SELECT * FROM t_forcescan WITH (INDEX(IDX_t_forcescan), FORCESCAN) WHERE col1 LIKE ‘vinod%’

XML plan will have ForceScan="1" for the index scan operator.

<IndexScan Ordered="0" ForcedIndex="1" ForceSeek="0" ForceScan="1" NoExpandHint="0" Storage="RowStore">

Other Fine prints

1. We can use index 0 which means scanning base table.

2. We cannot specify on multiple indexes.

3. Partition elimination will work the same way as normal. In other words, the scan will not scan all partitions. It will still skip the partitions that have been eliminated by the plan either at runtime or at execution time.

4. We cannot use this with Spatial index, XML index or Distributed queries.

Just out of curiosity, how many of you use hints of this sort. Do let me know your scenario and it would be great learning for all readers.

Continue reading...