Microsoft Office - Tips & Tricks

**app_engine** · 22nd February 2011, 04:42 AM

Right from the day MS-Office was released, there are any number of "hidden tricks" which sometimes won't show up easily even when you try searching the help.

It would be nice to share the tricks one comes across every now and then.

Also, here one can ask questions as to "how to" in the most efficient way

**Google Adsense** · 22nd February 2011 04:42 AM

**app_engine** · 22nd February 2011, 04:48 AM

I got two excel reports and needed to compare on a column (don't we get such all the time?).

Unfortunately, one report had unique values while the other doesn't.

e.g sheet 1 has like :
AX1
AX2
BX1
BBP...

& sheet 2 has rows like:
AX1
AX1
AX1
AX3
AX3
BBP
BBP etc

To compare and get the common elements isn't as straight forward as in a database "distinct" thingy. At least, I didn't know.

I was wondering as to should I throw these into a oracle table or access and do a distinct. Too lazy to that, thought of looking at excel help. As usual, it didn't help. Googled and got this simple solution. (I knew it must have something to do with filter as an auto-filter shows unique values as drop-down, but that can't be cut & pasted).

Solution in next post

**app_engine** · 22nd February 2011, 04:52 AM

To compare two columns with similar values but many non-unique rows :

step 1 : Make them unique, by selecting the column / "data" in the menu > filter > advanced filter >filter the list in place > unique records only (now you get the unique ones, if needed do this for both columns)...this was my new learning today

step 2 : I knew this before, use fx, VLOOKUP to compare and get matching values between columns

**littlemaster1982** · 22nd February 2011, 11:49 AM

Office 2007 has a separate button to remove duplicates.

**Sarna** · 22nd February 2011, 12:30 PM

app, have u tried IF function ?

http://spreadsheets.about.com/od/exc...tion_sbs_5.htm

**PARAMASHIVAN** · 22nd February 2011, 03:49 PM

App anna

Itha IT professional thread la yE discuss panni irukalamE, seperate thread yEn ?

**app_engine** · 22nd February 2011, 08:30 PM

Originally Posted by littlemaster1982

Office 2007 has a separate button to remove duplicates.

That's good to know (I keep switching between office versions - prior clients had 2007 / 2010, current one has 2003)

**app_engine** · 22nd February 2011, 08:32 PM

Originally Posted by PARAMASHIVAN

App anna

Itha IT professional thread la yE discuss panni irukalamE, seperate thread yEn ?

Param, this is not IT thingy IMHO. MS-Office is used by everybody (mostly non-IT people) and that's why I thought it could be useful. The moment you brand as IT, others won't walk into that thread at all...