From this page. formula 1:

```
=IF(COUNTIF($A$2:A2,A2)=1,A2,"")
```

Enter it in B2 and do a fill down.

From this page, formula 2:

```
=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)))
```

Enter it in C2 and do a fill down. Name the range B2:B8 "BlanksRange" and the range C2:C8 "NoBlanksRange". The two ranges must have the same number of rows (they don't have to be *on* the same rows, though).

Enter some values in the cells from A2 through A8. Try changing them. You'll see the lists in columns B and C change dynamically.

A sample sheet:

```
values formula 1 formula2
1 1 1
2 2 2
5 5 5
2 "" 7
1 "" ""
5 "" ""
7 7 ""
```

The two double quotes represent cells displaying blanks