casinos

Posts Tagged ‘Tips and Tricks’

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)

GO

CREATE INDEX IDX_t_forceseek ON t_forceseek(col1, col2)

GO

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:

FROM t_forcescan WITH (INDEX(x), FORCESCAN)

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...


 

Excel Tip: Largest values from Dataset (using Functions) July 8th, 2013

Vinod Kumar

If you haven’t read my post on “Excel Tip: SUM Top 5 values”, then this blog is an extension to that blog post. Do make sure to read it there because we will be using the same concept here in this tip.

Our dataset is simple as shown in figure below. We want to get the Max times for each city in a second list. How can we do this. The most easiest method I have seen is people resorting to PIVOT. If you want that implementation, do let me know and I will post that separately.

Just like in our previous post (Excel Tip: SUM Top 5 values), we used LARGE function there. Here we will use the MAX function with a conditional operator of IF. So in our example, we getting the MAX of values from the range B3:B8 where the value of D3 needs to be in the range A3:A8. Simple concept.

If you press a simple Enter you will get a value of 0. Remember the magic key (Ctrl+Shift+Enter) after you finish the formula and voila. You get exactly what you wanted. A typical output is below.

Now to copy this formula across E4 and E5. Find the cross arrow in E3 right hand- corner location. Now “Double Click” once you find the cross arrow.

Now that task automatically fills the range and you can see we have our desired output in less than a minute.

Hope you are enjoying the series of Excel Tips and tricks I am showing over the blogs. Do let me know of topics that will interest you and I will try to cover them here. Have a great day and learn something new every single day.

Continue reading...