June 18th, 2009 / 3 Comments » / by rajeshmago
Having the same record twice or more time adds to confusion! If you have hundreds of records on your database, then checking them manually for avoiding duplicate entries is too much time consuming. Right? So, I am writing a tip to help you in automatically preventing you from adding duplicate records in Excel 2000. The same tip should work in other versions of Microsoft Excel too. Let me know if there is some problem in using the same tip in other Excel versions.
I wrote this tip as one of my friends who run a tutor agency wanted to enter details of the tutors and clients in an Excel file and he wanted no too same phone numbers to be entered again. That is, he was assuming that no two different persons would have the same phone number. He had all the tutor details written on register.
The simple way to avoid entering the same phone number (record) in Excel 2000 is explained next:
- Launch Excel (Now I assume that you know how to launch Excel 2000. Use shortcut or type excel in Run Or use Programs menu etc.)
- After Excel program has launched and you see a spreadsheet with lot of blank cells. Choose the cells where you want to stop entering duplicate records. In my case, I selected B2:B1000 (Column B, row 2 to 1000). So, I am going to write the steps considering cells B2:B1000 being selected. Kindly change the formula for avoiding duplicate entries as per cells selected at your end.
- After B2:B1000 has been selected, these cells background becomes blue. Now, I clicked on Data menu and then on Validation.
- In Settings tab of Data Validation window, I selected Allow as “Custom” and Formula as “=COUNTIF($B$2:$B$1000,B2)=1″ (without quotes) under Validation Criteria.
- I clicked on third tab of Data Validation menu, i.e., Error Alert where I entered the error message that I wanted to get displayed when someone enters the duplicate record, i.e., already existing Phone number in this case.
- After entering the messages in “Error Alert”, I clicked on OK of the Data Validation window.
Now, I tried entering the same phone number in the second field from B3 to B1000 cells after entering the phone number in B2 cell, I got the error message set by me in Error Alert.
By using the above explained logic of the COUNTIF formula, i.e, =COUNTIF($B$2:$B$1000,B2)=1 you can set other cells also from getting the duplicate data entered in either of them.
Example – To avoid entering the same name in cells A2 to A1000, I would first select the cells A2 to A1000 and then enter =COUNTIF($A$2:$A$1000,A2)=1 in Data Validation.
Hope that this helps some of my friends in setting up Excel spreadsheets with prevention set in the cells for non duplicate entries through the use of COUNTIF formula.
Let me know your comments!
Rajesh
Copy the code below to your web site.
Posted in: Application SW, MS Excel
Tags: automated unique data, cell data integrity, COUNTIF usage to prevent duplicate records, Data validation, Excel 2000 record management, stop same records entry
June 13th, 2009 / No Comments » / by rajeshmago
Hi,
MS Windows Vista doesn’t offer much control on its GUI Shutdown feature, i.e., Click on Start and Shutdown and that’s all. No settings for specifying the time and action etc. after which the Shutdown should happen.
I was searching for some tool, free one, that allows more control on Windows Shutdown. Example of two such controls needed by me were:
- Getting An Alert That Windows Vista Is Going To Shutdown.
I know I am getting older 
and sometimes after I have clicked on Shutdown, I remember that there was something important, like sending an urgent email that was to be done. But I can’t stop the shutdown, though “shutdown -a” (a – for abort) can be used. But one has to be super quick or has to click on a batch file icon (with shutdown -a command in it) to do so. Moreover, Windows closes some processes when doing Shutdown, so if you delay only by few seconds, then you will have to restart your PC as Windows internal programs/services for OS management stops by that time.
Some kind of alert after clicking on Shutdown such as “Are you forgetting to do something important right now Or PC will power OFF, Are you sure you have don’t have any urgent tasks to be done right now?”) will help some of the users (Here I am supposing that you finally clicked on Shutdown and not on Restart etc. instead of Shutdown window).
Second control that was badly needed by me was:
- Autoshutdown Of PC After A Big Download Gets Completed
I leave my PC ON sometimes in night so that it can download some big files that takes few hours to be copied on my PC Or I keep Virus scanner Or Disk Defragmentation running. These tasks take few hours and it’s not possible for one to continuously monitor them again and again manually for their completion. I know that I have wasted a lot of my electricity in keeping my PC powered ON (though I keep the monitor off) as I didn’t know when these lengthy tasks will be completed and when the PC should be shutdown.
I also know that most of the download managers support the Auto Shutdown feature but this feature doesn’t work for all the sites, example – Rapidshare.
Now what if an application can do the same, i.e., Give me some kind of alert that PC is shutting down Or Automatically shutdown my PC after those huge files have been downloaded at my computer Or Virus scanning had been completed Or The disk defragmentation is over.
I found a good free application called DShutdown that does above and much more. This application is free and developed by programmer called Dimio and the letter D in DShutdown stands for him, i.e., Dimio.
I am writing about this no cost application after testing the above two functionalities needed by me presently, i.e. – Better control over manual shutdown and auto shutdown after big download completion Or a time consuming task completion.
I also tested the application for majority of its features except the Remote Shutdown and found them working.
One DShutdown feature that I was not able to activate on Windows Vista Business platform was of Enabling the User Inactivity monitoring shown in red in the following screenshot:
The list of the some of the events that can be used for triggering DShutdown (works for Windows 98, NT, XP, 2000 and Vista PC’s) are:
(taken from readme file of the software and explanations added for making it more informative):
- After a set period of time (example 6 hours).
- At a specific hour (example – at 0500 AM).
- At a specific hour of a particular day of the week/month/year (05 AM on 4th week/02/2010)
- When a running program ends (Disk Defragmentation, Virus scanner etc. Here you should know the main exe file running the corresponding program so that it can be selected in DShutdown for enabling Shutdown when the program closes).
- When CPU utilization goes beyond a specified level for a given time (example – below 10 for 1 minute).
- When Internet activity is above or below a specified value for a given time (Example – Net traffic less than 1KB/sec for 1 minute).
- When the modem connection fails, or that of another similar peripheral network card, etc. (Connections defined in DShutdown can be monitored and on connectivity failure, suitable action can be taken, i.e, PC Shutdown).
- Pixel color change (Monitoring an event based on window closure, appearance of a message or an icon that changes color).
- When another program (or the same user) asks for a shutdown (This is one of the important thing that I was looking for – Windows update doing auto shutdown Or user doing shutdown by clicking on Windows Shutdown button. This feature of DShutdown can be activated by “Enabling shutdown requests monitor”).
Some screenshots showing some of the DShutdown configurations done by me for checking some of its features are shown:
For more details of DShutdown features, kindly refer to its readme file (available after the program download).
DShutdown can be downloaded from:
http://digilander.libero.it/dimio/dshutdown.zip
The webpage of the programmer Dimio, who developed this application for Free is:
http://dimio.altervista.org/eng/
DShutdown is a stand-alone program that even doesn’t require installation as it gets loaded in the memory after the program has been clicked. After downloading it, you need to decompress the files. After decompression, it makes a folder named – DShutdown and in DShutdown folder, you can read the readme file – DShutdown (ENG) to know in detail about what DShutdown can do to offer you more control on Windows Shutdown triggering events.
If you download this application and found it useful, then kindly donate some money to Dimio who is kind enough to share it for Free. Any amount of money – small or big is appreciated by him and would help him in developing more quality applications similar to this one.
To donate, go to bottom of the web page located at -
http://dimio.altervista.org/eng/
and then click at the paypal button located below the photo Dimio and Dimio’s cat. You can make the payment by credit card in case you don’t have a paypal account.
I am sure that you will find Dimio’s DShutdown, a nice program for your Windows PC as it will help you to have more control on when to start Windows Shutdown. This will ensure better productivity, time and money saving too! Right?
Let me know your comments about this post!
Copy the code below to your web site.
Posted in: Quality-Free-Stuff
Tags: Better Windows Shutdown Control, DShutdown features, Excellent Windows Shutdown alternative
May 29th, 2009 / 1 Comment » / by rajeshmago
Hi!
In my previous post, I covered the way to clear Firefox 3.0.7 browser cache. As Microsoft Internet Explorer (MS IE in short), is also used by a good number of surfers, I decided to write the steps for clearing the MS IE cache, version – 7, there on my PC.
The basics of cache and why it needs cleaning can be found at my previous post:
http://www.pctipstricks.com/how-to-clean-or-clear-cache-of-firefox-web-browser-on-your-pc/
In short, browser cache is the temporary space on your hard disk for storing web files so that your browser doesn’t have to fetch them again and again from the online site.
The two big reasons for clearing your Microsoft Internet Explorer browser cache normally are:
- Creating space on your hard disk (how much space will be freed after clearing the cache will depend on how much has been set by you, the recommended and default cache space set is 50 MB)
- Present site in the address bar not getting displayed properly (broken images and old content etc. due to cache stored at the time when the site had problems or there were connectivity issues).
The steps for clearing the MS IE 7 browser cache are (Don’t expect the word cache as available in Firefox, to be found in IE, instead look for Temporary Internet Files):
1. Open MS IE 7 browser. With MS IE 7 open in the current window, click on the Tools menu and then click on Internet Options (screenshot shows the Tools menu and click being made on Internet Options). 
2. A window titled “Internet Options” appears. By default, the General tab (first rectangular button) is selected. Under “Browsing history” click on “Delete…”
3. In the new window, titled “Delete Browsing History”, click “Delete Files…” opposite to the Temporary Internet Files category. 
4. A small window titled “Delete Files” appears requiring confirmation from you regarding deleting of Temporary Internet Files, Click on Yes. 
5. Depending on the size of your MS IE 7 browser cache on your PC, it may take a second or few seconds to delete the cache. After the cache has been deleted (you may get an automatic screen showing that the cache files are deleted – only if cache files are large), close the respective MS IE 7 windows, i.e., “Delete Browsing History” and “Internet Options” by clicking on Close and OK respectively.
By completing the above steps, you would be able to clear or clean the cache (Temporary Internet Files – In Microsoft IE language). This means that you now won’t have any web pages stored in the Microsoft IE cache folder on your hard disk.
Now load the page with the valid URL in the address bar of Microsoft Internet Explorer (MS IE) 7 browser and a page with the current updated content (as of the time of loading of the page) would be served by the respective site. Probably, if the problem of broken pages display was due to issues with your PC’s MS IE 7 browser local cache, then now you will have proper images on the page displayed by MS IE 7.
Was this post of any help to you?
I look forward to your comments. Don’t be shy of letting me know your thoughts 
Take care,
Rajesh
Copy the code below to your web site.
Posted in: Application SW, Internet Browser
Tags: cleaning MS IE 7 cache, clearing MS IE 7 temporary internet files, how to remove IE cache files, solution to old IE content display
Link to this page
