Microsoft Office - Tips & Tricks

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