Friday, April 4, 2014

Obtaining All the Information of Computers

When we buy a computer this one is ready to be used so we don't have to worry about having the network card, mouse or touchpad ready, to mention a few examples.

However, what happens if you need or have to format your computer? Well, you can use your Windows disk to make a new installation and get the disk formatted. But you will notice that after this process some of your devices won't work correctly. When this happens it's probably because you don't have the required drivers of your devices.

What a problem! how can I fix that? Very simple, I'll introduce you a tool named HWiNFO, install it and execute it with an account with admin privileges, this application allows you to choice the type of report it's going to generate.

After that, you will be in front of a summary of your system, you can review it but for this post just click on "Close".

Once you close the system summary you'll see a window like this, giving you all the information of each component of you computer, here is where you have to navigate to find the characteristics of the devices that are not working (brand and model for example).

With this information you can search on the internet the appropiate driver for you device, when I have to do this I prefer to go to the driver's manufacturer site for being the most reliable source. Remember that having the correct drivers will help you to harness all their functionality.

Friday, January 3, 2014

Getting the Max Value with Condition in Excel

Happy new year my friends!! It's time for the first post of 2014!. As usual, I was working at a floor where working with Excel it's very important, so I was working by myself until a friend that was close to me just asked me something that I couldn't answer at that moment.

She needed to get the max value from a huge list in Excel, but this value had to be obtained according to a condition, in this case this was a product. Let's do this with an example, supose that the list is about three products (A, B and C) and the months that they were sold expressed in numbers. Thereby, the task is to know what was the last month each product was sold.

To make it very clear let's start with the condition function, we use "IF" to show that it's required a condition, which is going to be searched on "Product" column from "Data" section and the condition is product "A", if true it's going to return the value associated to "Months" column. I know that I'm comparing ranges but this is because it'll be useful later.

Ok, the result of this is "1", meaning that I got just the first value of the comparison. And this happened just because "IF" function when receives a range of cells only takes the first one to evaluate, having A=A then it returns the range of cells specified but in a cell is only allowed one value, the first.

Well, let's try to embrace that function with another one, "MAX" function.

Doing so we get as result "9", which is the max value in the "Data" table, because it's the range returned by "IF" function, then all the calculation is wrong and this is because we are using ordinary functions to make operations over an array.

Then, the trick to fix this is to edit again the calculation and press Ctrl + Shift + Enter keys at the same time, with this you will notice that the result is "4" rathen than "9" being now the correct answer, also see that the all formula is embraced in "{}" which is the notation to operate functions over arrays.

Now, just copy the formula from product "A" to the other products to get the max value for each one.

Remember, using ordinary functions like "IF" with a range of cells just returns the first value, therefore when "MAX" function receives the result of "IF" it jus take the max value of the list returned by this function. To avoid this, it's necessary to apply array functions with Ctrl + Shift + Enter.

Saturday, December 28, 2013

Obtaining Computer Name From an IP Address

In this world all the computers that are connected to a network have to be identified in some way, hence, the IP (Internet Protocol) address exists and gives each computer a unique identification number by which we can find them.

However, when you work with a computer that is in a local network it's always necessary to get connected to it, what is more, sometimes you need to access to it without being physically there. Thanks to god that we have a mechanism to see our machine remotely, and this requires obviously the IP address or the computer name.

Yes, a computer name, because remember that the IP address is composed by many numbers that are not easy to remember, even more, sometimes the name of the machine gives you a clue about what kind of computer is, for example, where I work a name beginning with "PE" means that the computer is located at Peru meanwhile starting with "CO" means that it's in Colombia.

Perhaps it happened that you only have the IP address but no idea if the computer is the one you need, if the name can help you to be sure you can use the "nbtstat" command in Windows using the parameter "-a" with the IP address of the computer.

Execute the command and wait for results.

You will notice that there are four names, this is because there are two different names the first one is the domain name and the second one is the computer name (the final goal). The another two names are the same but they have other codes because of the names clasification.

Monday, December 23, 2013

Making Excel VLOOKUP Function to Work

I have seen many times that there are some scenaries where the Excel VLOOKUP function is not able to calculate the value we are looking for. For example, let's search the amount for the ID 16 according to a little table with this value.

This is something really easy to do, we need only to use VLOOKUP function selecting the ID, the matrix and the column to be search.

However, when you see the result of this function you notice that there's an error despite the values are fine.

The reason of this error is that the searched ID value is stored as text, so you can convert it in number in this case because it's only one value, but what if it were a thousand of them?.

To avoid worrying about format issues just embraces the key value in the VALUE function inside the VLOOKUP function.

Once you use the function you are going to get the value that corresponds to the ID.

Consider that this is an example where the VLOOKUP function won't work as we want, I helped many people with this to solve another kind of problems with this function.

Sunday, December 15, 2013

Changing Size of VirtualBox Disc

Since I decided to use Linux, because I like it a lot, I knew that I'll need Windows anyway due to the fact that everyone (at most) uses it and considering also that my posts would have to be focused in this operating system (my readers use it).

To overcome this situation I decided to install a virtual machine on my Linux (Debian specifically) to avoid rebooting anytime I need to do something in Windows, to accomplish this purpose I installed VirtualBox and installed in it Windows 7.

However, In some point there was need to get more disk space but I couldn't get it because when you define a virtual machine you specify a determined disk size. But, it can be done the following to increase the disk size using the tools provided by VirtualBox.

First, increase the disk size by using a VirtualBox command created for this task. Remember that the virtual machine has to be turned off to be able to modify the virtual disk, once you are sure about this, go to the configuration of the machine.

Then, go to "Storage" section and pick your virtual disk, this is just to get the physical path of it in your computer, copy it from "Location" label.

With this path copied go to a command line and execute "vboxmanage modifyhd <your_disk_path> --resize <new_size_in_megabyter>", it's very similar in Windows so you won't have any problem, in this example I'm increasing the size to 26 GB.

Now you have your virtual disk with the desired size, but as a physical disk it's necessary to asing the new space to the operating system, to do this boot the virtual machine (in my case Windows) and go to "Start", right click over "Computer" and click on "Manage".

In the new window go to "Disk Management" and you will see not asigned space (consequence of the size increment), just right click over the partition next to this free space and select "Extend Volume...".

Finally, select the amount of bytes you will need in the new partition and that's all, more space for your virtual machine.

Sunday, September 29, 2013

Creating Scripts To Insert Data From Existing Tables

When we work with systems is inevitable to work with data that is manipulated by these, and there are moments when you need to move data created by one application to another database, but it's not necessary to copy all the database to another one, maybe you require just to move all data for particular tables.

For this task, SQL Server can help us by using the "Generate Scripts..." option that it's into Management Studio tool, you only have to right-click over the source database and go to "Tasks" menu and here locate the option said before.

You will see the introduction, just click "Next".

As I said at the beginning in this opportunity we are going to select a group of tables and not all the database.

Specify the media where this result is going to be published. But, in this case it is necessary to click on "Advanced" option to personalized the operation.

As we want just the data, select "Data only" in the "Types of data to script".

Review the summary of the operation to be executed.

Wait for the process to finish successfully.

Once the process finishes with success the output file can be use directly over the new database that have the tables with no data.

Sunday, September 22, 2013

Using Calculated Fields in Dynamic Tables with Excel

Honestly, I don't consider myself an expert in dynamic tables, although many people think that actually I am this is just because I know many tricks about Excel, however I don't use dynamic tables a lot and therefore there are things about this tables than I don't know.

One day, when I was at work, a partner called me to watch his computer because he had a big doubt about something so he decided to ask me about it. He was managing a huge dynamic table that have a bigger simple table as data source, this dynamic table resumed all sales from region, but the problem was that he needed to show that resume not with the exact amount but rounded to thousands.

At that moment I couldn't help him because I didn't know how to do it, but now I took a time for testing ways of doing that and I want to show you what could have done at that time.

First at all create a simple table with few regions and sales value in 3 currencies.

With this information, select it and  go to "Insert" tab and select "Dynamic Table" to create one based in the simple table created above.

You will have your dynamic table created, select  "Regiones" field to have the first column of the new table.

 Now select the "Options" tab and inside here select "Fields, Items & Sets" button to select the option "Calculated Field" from the menu.

Once you see the new window the next step is to set the new field, first put a name for it and second, place the function for this, in this case you have to use a previous existing field to divide it over 1000 and this result has to be rounded by using the native function round provided by Excel.

Doing so will give you a new column in your table that shows the amount of sales but in thousands as it is required.

If you want to see the original value just check the original field from the panel, in this case is the "Dólar" field.

Using this method will provide you many useful ways to present information in an easy way, it depends on the complexity of the functions to be used to calculate the new field.