Tuesday, May 26, 2009

Excel: Listing Distinct Elements in a List

I found this info over at http://www.cpearson.com which is an absolutely awesome Excel reference. I wanted to copy these bits here so that I wouldn't lose them.

You can use a simple formula to extract the distinct elements in a list. Suppose your list begins in cell C11. In some cell, enter

=IF(COUNTIF($C$11:C11,C11)=1,C11,"")


Eliminating Blank Cells from a list

You can use a formula to return only the non-blank cells from a range. The following function will return all the cell values in a range named BlanksRange that are not empty.

Create a range name, with the same number of rows as BlanksRange called NoBlanksRange. The range NoBlanksRange must have the same number of rows as BlanksRange but it need not be in the same row numbers. Enter the following Array Formula in the first cell of NoBlanksRange, and then use Fill Down to fill out the range:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

The first N rows of NoBlanksRange will contain the N non-blank cells of BlanksRange. Note that this is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }.

Note that if you do not use named ranges and enter the actual cell references, you must use absolute cell references (e.g., $B$1) rather than relative cell references (e.g., B1).

No comments:

Post a Comment