Sunday, December 12, 2010

Excel tricks

First guest tip, from my colleague Jonathan Luker:

If you nest a MATCH() function inside a ISNA() function, it can make a column of TRUE or FALSE on the new data.

e.g. if I look for matches in ItemID between data in separate tabs:
=ISNA(MATCH(A2, 'Test-jul data'!$A$2:$A$15633, FALSE))
This will scan the list for matching ItemsIDs and put TRUE or FALSE in the cell beside each one.
Copy & paste for full list (takes a while to process of course!).

If you sort on that TRUE or FALSE column, you'll have all the new ones listed together.

There are a bunch of IS functions in Excel:
# Excel ISBLANK Function
# Excel ISERR Function
# Excel ISERROR Function
# Excel ISLOGICAL Function
# Excel ISNA Function
# Excel ISNONTEXT Function
# Excel ISNUMBER Function
# Excel ISREF Function
# Excel ISTEXT Function
Quite handy, especially when used with IF(), MATCH(), VLOOKUP(), etc.

No comments:

Post a Comment