TekBytz

Archive for September, 2008

Comparing two columns in excel spreedsheet

by on Sep.22, 2008, under Office

If you in a software service based industry you may get many questions in Microsoft Excel. One such question is “How to compare two columns in the excel if the data is in thousands of rows”. Here is a solution for it.

Imagine you have two columns A and B. Column A has the data which needs to be compared against column B. Here I would show you 3 excel formulas to archive this.

  1. MATCH
  2. COUNTIF
  3. VLOOKUP

Here is the syntax

  1. =Match( value, array, match_type )
  2. =CountIf( range, criteria )
  3. =VLookup( value, table_array, index_number, not_exact_match )

Here is a example usage :

  1. The below syntax can be used if we want to find out a match and mark the result as ‘Y’ or ‘N’ and consolidate it.
    =IF(ISNA(MATCH(A1,$B$1:$B$100,FALSE)),"N","Y")
  2. The below syntax is used to find the count of the matches in the resultant column. If the count is 0, then there is no match. If the count is 1 then there is 1 match. If the count is 2 then the matched data count is 2 and so on.
    COUNTIF($B$1:$B$100,A1)
  3. The usage below is similar to the first option where there is change in the function idiom
    IF(ISNA(VLOOKUP(A1,$B$1:$B$100,1,FALSE)),"N","Y")

Where to use what?

Now the question is, of all of them which performs the same function and now where to use what function. The MATCH/VLOOKUP can be used to find out the existence of the data in the column but COUNTIF can be used to find out the existence of data and to find the number of instances the data exists.

Which one is faster?

MATCH/VLOOKUP is more faster than the COUNTIF, since COUNTIF has to look up the entire array to throw out the count. But MATCH/VLOOKUP terminates it loop once it find the data in the array.

There are more functions too in excel for comparison of data. I shall explain it when I come across it.

4 Comments :, , , more...

Disconnecting a hidden remote connection

by on Sep.21, 2008, under Windows

One fine morning I was surprised to see the error “Multiple connections to a server or shared resource by the same user using more than one user name are not allowed” when I was trying to connect a network drive. I  verified for network connection in  windows explorer but I could not find any connections. Here is a way to solve it.

1. Goto command prompt.
2. Type “NET USE” (with out codes) to view all the connections active or inactive.


Status       Local     Remote                    Network
-------------------------------------------------------------------------------
Unavailable  M:        \192.168.0.100Music     Microsoft Windows Network
OK                     \192.168.0.103m$        Microsoft Windows Network
The command completed successfully.

3. Type “NET USE \<remote_resource_name>” (with out codes and replace <remote_resource_name> with the actual name) to display the complete details.


Local name        Z:
Remote name       \<remote_resource_name>
Resource type     Disk
Status            OK
# Opens           2
# Connections     1
The command completed successfully.

4. To disconnect this resource use the command without codes as “NET USE \<remote_resource_name> /DELETE
5. Now you are available to connect the remote resource.

Here are the other ways to disconnect through GUI if the connection is visible.

If the connection is mapped to the drive letter.
1. Open the Windows explorer
2. Goto Tools and choose the option Disconnect Network Drive
3. Choose the drive letter and click Disconnect.

If the connect is not mapped to the drive letter.
1. Right click on MyComputer
2. Choose Disconnect Network Drive and it display the all the resources connected
3. Choose the resource name or the drive letter and click OK

1 Comment :, , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!