Best Microsoft Excel Bloggers

Monday, February 8, 2010

Duplicates

Great game although The WHO vocals and the ads were a bit of a disappointment.

DUPLICATE VALUES

I've had a number of emails and conversations lately about identifying duplicate values.
Using Conditional Formatting in Excel 2007 makes it relatively easy but not everyone has Excel 2007.
Let's start with Excel 2003:

Let's say that you have a column of numbers in B4:B16 and you want to identify duplicates.

Method 1:
Sort - Easy but sometimes you can't or don't want to manipulate your data.

Method 2: Click in cell C4 and type
and then copy it down.




You will have a column of TRUE and FALSE.  You can manually delete them or highlight them. The problem with this method is that Excel labels both numbers are TRUE - there is no differentiation between the original and the duplicate.

Method 3:  A Better Alternative
To get around this, try =IF(ISNUMBER(MATCH(B4,$B$3:B$3,0)),"Duplicate","Unique").
This will label the original as Unique but any other instances will be labeled as a Duplicate.












Method 4: This method is actually available in Excel 2003 and Excel 2007.
You can use the Advanced Filter function under the Data menu or tab and select Unique Only.
To be honest, I have mixed results with this so double-check your answer.

Select the column of numbers and then go to the Data tab or menu and select Advanced Filter.
Click copy to another location
Enter a cell to Copy To:
Put a checkmark in Unique Records Only
Click OK.




Well, it's getting late. Tomorrow I'll talk about how to identify Duplicates in Excel 2007.

Friday, February 5, 2010

It's Friday!

Sorry.. I got behind and just realized that it is 6pm.
I am looking out the window at about 5 inches of snow. An absolutely beautiful sight.
Stay warm.

Watch those great Superbowl Ads and cheer for your preferred team.
Have a great weekend.

Patricia








Thursday, February 4, 2010

Calculating Age or Years of service


When you are calculating age or years of service, you can't just subtract the date from the current date. You will not get the correct answer if you do that. Instead, you need to use =yearfrac() which will give you a fraction based on the number of days between the Start Date and the End Date.

The syntax is =yearfrac(today(),A1,1))
My brother's birthday is next week so let's see how old he actually is:
38.97 sounds better than 39 doesn't it?  Now, if you wanted it as a whole number you could simply use the INT() function with it.
=INT((YEARFRAC(TODAY(),A1,1)))
Now- be careful - if you use the INT, his age would display as 38. After all, he is not 39 yet. Don't rush him. Just wait until next year. Egad. my baby brother is getting old.
 ========================================================
For those of you who remember Lotus, you can also use DATEDIF(). This function provides you with a little more flexibility.
DATEDIF() is not documented in Excel and has to be typed in manually.
Rumor has it that it was a very popular Lotus function and back when people were transitioning there was a huge outcry over its loss and Microsoft added it in - grudgingly..ergo.. no documentation.

DATEDIF() allows you to display the date in terms of years, months or days.
The syntax is DATEDIF(start date, end date, time interval)                                                                         
Time intervals include:
"Y" The number of complete years
"M" The number of complete months
"D" The number of days.
"MD" The difference between the days in start_date and end_date.
          The months & years of the dates are ignored.
"YM" The difference between the months in start_date and end_date.
          The days & years of the dates are ignored.
"YD" The difference between the days of start_date and end_date.
          The years of the dates are ignored.





If you are really into it, you can join these functions all together using & and quotes
and get something like this:
=DATEDIF(A1,NOW(),"Y")&" years" &" "&DATEDIF(A1,NOW(),"ym") &" months "
and the resulting answer would display as 38 years 11 months

Remember, the & joins items together and you need quotes around text. I left a space before and after the word years and months so that everything would not run together. If you don't press your space bar to get the spaces, your answer will look like this: 38years11months instead of 38 years 11 months.

Tuesday, February 2, 2010

WEEKNUM()

One Date Function that some of you may find useful is =WEEKNUM()
This function returns the week number in the year.
If you want to find out what the current week number is you use the =TODAY() or =NOW() with WEEKNUM.

So, in the example below, =WEEKNUM(TODAY())  returned the value of 6 because today is February 2 2010.

Warning: Microsoft points out that the WEEKNUM function is based on the American standard where January 1 is considered the first week of the year even though this year the first week of 2010 only had two days in it. The European standard differs as the first week of the new year has to have at least 4 days in it so this function should not be used if you are presenting or using the data where European standards are used.

Monday, February 1, 2010

I could have sworn I had blogged about Date Functions awhile ago but apparently not.
Let me start with some basics.
A lot of people struggle with Date Functions and I think a lot of the problem arises because of formatting problems as as the fact that many of the answers are volatile.

If you don't know about =TODAY() or =NOW(), you should check them out. I use them constantly.
=TODAY() returns today's date. It is a volatile function. By volatile, I mean that it changes.
Coming from Lotus - oh so long ago- I hated these functions. I think I spent 2 hours once trying to figure out what I was doing wrong - only to realize that yes you really do need the empty parentheses to get the function to work!

If I type =TODAY() in an Excel spreadsheet today, which is February 1, it will display February 1. But, if I save the spreadsheet and open it February 2, it will display February 2 without me having to do anything. Open it on March 15th and it will display March 15th.

=NOW()
 works the same way, only it displays the current date and the current time.

People tend to be a little wary of these because sometimes the display looks hinky..(is that word?)
=TODAY() only displays the date so if you format it as date and time, it always displays 12:00 which of course looks strange.

Sometimes, if you are using these functions in a mathematical calculation, Excel tries to help you out and just confuses you. For example, if I type =TODAY() + A1 and A1 contains the value 10, I expect the answer to be February 11, 2010 since 10 days from today, February 1, would be February 11th. However, sometimes Excel surprises you and gives you an answer like the one below:
Generally the person who typed the formula, assumes they did something wrong. Well, you didn't. Excel sees that you are adding and so instead of displaying it in a date format - it displays it in a number format. All you have to do is reformat it as a number and it will display properly as 2/11/10. The good news is that Excel 2007 seems to have taken care of this problem but sometimes when you expect a number Excel displays it as a date or vice versa. So, before you decide it is user error on your part, re-format the cell. 

Ms. Excel- Resident Excel Geek