Sunday, September 30, 2012

How To Filter An Excel Table On Multiple Columns Using VBA

Filtering information out of a larger data set is standard Excel functionality but sometimes it's necessary to fine tune the filtering to get the information you need.

For example, you might have a list of customers prioritized as A,B or C. Additionally, you could have them listed by region. Something like this.

Customer, Category, Country

ABC Limited, A, USA

ZYZ Trading, B, UK

IJK Co.,A, Australia

You can use the filtering tool to select, for example all the A category customers or those residing in the UK or Australia. But you might want to select all customers that are rated A OR live in the USA.

An Excel Solution To Filtering Multiple Columns

One solution is to create an additional column holding a formula to identify customers matching the criteria. The formula might be similar to this one:

=IF(OR(b2="A",c2="USA"),1,0)

The new column can then be filtered to extract the data required.

A VBA Alternative To Multiple Column Filters

If the thought of multi-bracketed formulas doesn't inspire you, a few lines of VBA code might do the job just as well. We'll create some code which will select customers that are rated "A" OR live in the USA.

First, select the first column and create a string to hold the search criteria.

Set r = ActiveCell.CurrentRegion.Columns(2)

searchStr = ",USA,A"

It's good practice when using a string for searching to enclose each item in a delimiter. For example ",UK," and not "UK". Otherwise, a search for "England" might return matches for "New England"

Now we can loop through each row to see if there is a match on either of our parameters.

For x = 2 To r.Rows.Count

item1 = "," & r.Rows(x) & ","

item2 = "," & r.Rows(x).Offset(0, 1) & ","

If there isn't a match on the row, then we hide the entry.

If InStr(searchStr, item1) = 0 And InStr(searchStr, item2) = 0 Then

r.Rows(x).EntireRow.Hidden = True
End If

To "unhide" the rows, we'll use a decision box and reverse the hide command.

unhide = MsgBox("Show hidden rows?", vbYesNo)

If unhide = 6 Then
For x = 2 To r.Rows.Count
r.Rows(x).EntireRow.Hidden = False
Next
End If

With some development the code would work equally as well with more columns, or you could use VBA to code a formula to match the criteria and then filter the new column.

Summary

This small code snippet uses standard programming techniques to filter a table by applying criteria across multiple columns. It's another example of how a little knowledge of VBA and Excel can improve your productivity many times over.


Sunday, September 23, 2012

How To Sort An Excel List Conditionally Using VBA Code

It's easy enough to sort a list using Excel's standard sorting tools or applying a function directly in VBA code. But it's a little more challenging to sort a list where you need to apply your own criteria.

An Example Of Conditional Sorting

A typical scenario might be to sort alphabetically the following list of countries, but always have the big regions like the USA, UK and Japan at the top of the list.



Country

New Zealand

Australia

USA

Mexico

Belgium

UK

Japan

We'll create a new list using some simple VBA code which you'll be able to adapt to meet your own needs.

Organizing The Code

One solution to this problem is to reorganize the list so the top countries are at the top and then sort the two areas of the list separately.

First, we'll define the names and number of countries we want to appear at the top of the list.


topItems = ",USA,UK,Japan,"

ctItems = UBound(Split(topItems, ",")) - 1

Next, we can select the list and set a counter for the number of "top" countries and "others".


Set rng = ActiveCell.CurrentRegion

top = 1

others = 1

Now we're ready to separate out the list into the top countries and others which we'll do by moving each country into a new list alongside the old one. Don't forget we need to ignore the header row.


For x = 2 To rng.Rows.Count

If the current cell value is one of the top countries then we'll move the value to the top of a new list, and if not we'll move it to the bottom of the new list.


If InStr(topItems, "," & rng.Rows(x) & ",") Then

top = top + 1

Cells(top, 2) = rng.Rows(x)

Else

others = others + 1

Cells(others + ctItems, 2) = rng.Rows(x)

End If

Next

Our list is now reorganized in the following way, and we just need to sort the bottom part of the list in column 2.


USA

UK

Japan

New Zealand

Australia

Mexico

Belgium

The following code sorts the list below the top countries in column 2. Because we know how many top countries there are, the range begins two rows below that value - to take into account the header row.


Set rng = Range("b" & ctItems + 2 & ":" & ActiveCell.End(xlDown).Address)

rng.Sort Key1:=Range("b1"), order1:=xlAscending

The code produces a final result looking like this:


USA

UK

Japan

Australia

Belgium

Mexico

New Zealand

One area for development might be to arrange the top countries in a certain order. It would be easy enough to hard code a solution, but it is good practice to have a scalable solution; for example it might be a list of customers and you need to highlight your top 100 purchasers.

Summary
This short VBA code provides a solution to a problem not readily solvable by using the standard Excel tools. It's the type of scenario VBA developers often face and a good candidate for saving in a handy location for future reference.

Sunday, September 16, 2012

Joomla Development - Vibrant Option for Web Expansion

Are you stressed about your website development? Yes then, you should switch to Joomla development to get easier, faster and reliable web and related applications expansion. You must be wondering that why just Joomla for entire website development? The answer is straight forward as well as quite simple and i.e. Joomla is an open-source content management system that consists of special abilities to offer offshore web and application development at affordable cost.

Core information about Joomla

PHP is base of Joomla programming language as it is written in the same language. The main and basic feature of Joomla customization is that it supports OOP (Object Oriented Programming). The superiority of the language can be derived from a record-breaking fact that it has been downloaded more than 30 million times till the date and these figures makes it second most famous CMS on the net. For better and comfortable access, the latest version of Joomla i.e. 3.0 was released just a month ago.

Joomla has number of extensions that carry out their separate functions for the betterment of website development. Some of the main extensions of Joomla are:

    Modules of Joomla
    Joomla Components
    Templates of Joomla
    Plugins
    Languages

You should also know that all these components are further divided into sub-divisions, for e.g. - let's have a look over the sub-categories of templates, which are as follows:

    Effects and Images
    Fonts
    Design
    Layout
    Color Schemes

What all Facilities Joomla ca offer you?

Joomla provide several numbers of services for the website and relevant apps development. Some of the important amenities that Joomla proposes are as under:

    Business web-apps development
    Community site development
    Social- Networking websites development
    E-Commerce web-applications development
    Job and Hiring Site Development
    And a range of other additional sites development

Joomla can be helpful for you only when you find a faultless Joomla developer that has core and wide knowledge regarding the same. It is absolutely a daunting task to hire Joomla developer from the bulk crowd of the Joomla programmers. A Joomla developer must be expert in Joomla theme designs and Joomla design integration because both these factors play a vital role in proper and ideal expansion of a website. In addition, you must also ensure that developer has resonance experience in the same field as well as superior grasp over the PHP language. Last yet primary thing, the Joomla developer or the Joomla development company must tender the services at affordable cost as well as on time.


Sunday, September 9, 2012

How Long Does It Take To Learn Java Programming?

In Information Technology, people who are programmers should update themselves with the latest platforms, versions of several programming languages such as Java. IT is a field that continues to evolve, improve every now and then. Do you know that since it's first released, Java has a total of 7 versions already? Imagine the impact of the continuing release of versions to programmers. Say for instance, if you have learned Java programming in the late 90s chances as what you have studied will become obsolete or not applicable at present times. Why? The version that is widely use now is Java 7 which has currently had its update 9 released within this month.

Now you have an idea as to how important it is to learn Java, then you need to determine certain factors which will affect you. First is money. Do you have the money to pay off a one-on-one tutorial with a good programmer, view video tutorials online, or browse through e-books for further insights. Second is time. For having time as an issue, a hindrance it would always be like that.

If you are on a bit of a hurry to learn Java the quickest time possible, sorry to disappoint you but there is no such thing as a guarantee. Indeed, time will be your enemy especially if you are grabbing for a promotion or earn more in the world of IT development. To safely say, it may take a couple of months and even years for some to learn this programming language. But there are those who are gifted with a good memory and academic skills. Therefore, it is much easier for them to learn Java. It's more of less of an effort on their part. By simply reading a book, watching video tutorials, or listening to podcast all at once can do the trick for them. Good for those who can learn things fast.

What about those who are a bit slow and can not process information as fast as they want it to be? Fret no more! There will always be a way to do resolve such issue. This is where pacing comes into the picture. Take it slowly. Be sure that every bit of information is processed and absorbed. Once it's in, then apply it through doing the real thing. It is through application which a person's learning about a particular thing will be tested.

As with regard to the time frame of learning Java, it may take months or even a year. This would always depend upon the person. Not every person is alike. One may have only 2 months to learn everything and master it but another person may take a year to do so. You can never tell.

Sunday, September 2, 2012

Best Way To Learn Java Programming

Java programming has been in the forefront in the world of Information Technology development. This can be better viewed within a company's IT development. Most often when it comes to corporate development Java is being used. One good example where this programming language is being used is on a company-specific tool i.e. airline booking. Have you ever thought how airline companies make it easier to book a flight in a matter of minutes by simply logging into the system? Thanks to Java this is made possible.

Not every programmer is diverse and has learned all the ropes about Java. Some may know the basics but a little bit of the advanced ones. This is where a programmer who wants to excel in this field must do everything to learn Java programming. How is this possible when you got to juggle from your 8-hour job five days a week and got some responsibilities as well? Good thing that the Internet is filled with resources which you can use to your advantage. All you have to do is to go find a person who knows Java, can teach you one-on-one during your free time, and has enough resources to share with you about this complex programming language.

Yes, the best way to learn Java programming is for you to have a mentor or tutor who have the patience to give you a review about the basics and help you learn more about the advance stuff. Be very ready to shell out money a little bit more of what you expect especially if you want to learn from the best guy. Apart from the knowledge which you can gain from it, you can also update yourself with the latest versions. To date there are a total of 7 versions of Java since the first release of JDK 1.1 way back in February 19, 1997. The current version is Java SE7 which was released in July 28, 2011. Update 9 for this Java 7 has been released recently on October 16, 2012.

As you can see, if you have learned Java way back in 1997 and you have not updated yourself with the latest versions plus all of the updates you will be left behind. Therefore, it is really a must to learn Java programming and update your skill set. Not the basic but more of enhancing your learning and skill at the same time.