Microsoft Office - Tips & Tricks

[app_engine]

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 :-)

[app_engine]

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]

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]

Office 2007 has a separate button to remove duplicates.

[Sarna]

app, have u tried IF function ?

http://spreadsheets.about.com/od/excelfunctions/ss/if_function_sbs_5.htm

[PARAMASHIVAN]

App anna

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

[app_engine]

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]

App anna

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

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...

[app_engine]

app, have u tried IF function ?

http://spreadsheets.about.com/od/excelfunctions/ss/if_function_sbs_5.htm

Yes, in excel, mostly for "relational" operation (equal / greater / less).

No nested stuff :-)

[PARAMASHIVAN]

yes, good Idea App na, I never thought about that :oops:

[app_engine]

Using Access for some quick & dirty stuff (normally prefer sql*loader to Oracle and do things there but there are some limitations at the current setup)...

Found out that Access (2003) does not like the operator % in sql but prefers * :-)

i.e. if you want to write a query that goes like

SELECT *
FROM TABLE1
WHERE NAME LIKE 'JO%'

you need to write

SELECT *
FROM TABLE1
WHERE NAME LIKE 'JO*'

Interesting:-)

[app_engine]

Forced to learn another Excel trick when a teammate came asking for help to identify duplicates.
(BTW, Excel 2003 - still at the same client, it's possible the newer ones have a fixed function)

Simple solution - say column A has duplicates, we can get them "flagged" on another column (say Z) using the function countif. The expression that should be placed on cell Z1 is :

=IF(countif(A:A,A1)>1,"DUP","")

And drag-copy this to as many Z column rows as needed and the duplicates should show as DUP.
(If the whole list is sorted on column A, then the duplicates should be in adjacent rows) :-)