Auto Filter
 _____________________________________________________________________

Business Computing Tips

By K&K Fainges kfainges@bigpond.net.au

Ever had to work with a spreadsheet where others have entered the information? It can be a real problem at times. Take the example below. Here St Catherines is entered four different ways. That is without spelling mistakes. In a large data group, something like that could be entered seven different ways or more.

School Name
Ambrose
Churchies
Saint Catherines
Saint Catherine's
st catherines
St Phill's
St. Catherines

The problem compounds when other schools appear in between the different spellings. It makes it very difficult to sort out all the examples of just one school. If you did it just by alphabetical order, you would miss the last entry. 

Enter Excel's Auto Filter function. 

Click on the top of the column, go to DATA => FILTER => AUTO FILTER. A small down arrow will appear at the top of the column. Click on that arrow. A list will appear. The third option is custom. Click on that.

A box appears with four white boxes. On the top left box it will say equals. If you click the down arrow here and scroll down to the bottom, you will see the word contains. Click on that. If you type cath in the box next to that, the list becomes:

School Name
Saint Catherines
Saint Catherine's
st catherines
St. Catherines

You can go one step further and use EDIT=> FIND=> REPLACE to change all the alternate spellings to the one you decide to use. To replace all the different spellings at once, try typing S*Cath* in the find box. The * symbol stands for any character. 

***********************************************************************

Please feel free to pass it on to your friends, just let them know I wrote it.

Karen Fainges holds a Bachelor of Business, and a Grad. Cert of Vocational and Educational Training. All this is nice but it's the 14 years of having to make sales or starve that makes her think she has really learnt what does and doesn't work. A tutor for all ages, she specialises in helping people get started on the long road to technology.

"It has to be practical, it has to be cheap, and it has to work."