One non-array formulas approach ..

Assuming source data in Sheet1, cols A to D
(col D = key col)

In a new Sheet2,

Put in A1:
=IF(ISERROR(SMALL($E:$E,ROW())),"",
INDEX(Sheet1!A:A,MATCH(SMALL($E:$E,ROW()),$E:$E,0) ))
Copy A1 to D1

Put in E1:
=IF(Sheet1!D1="","",IF(COUNTIF(Sheet1!$D$1:D1,Shee t1!D1)1,ROW(),""))

Select A1:E1, fill down to cover the max expected data extent in Sheet1
(Format the cols to taste)

Sheet2 will return the desired results, with all lines neatly bunched at the
top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PaulQuincy" wrote
in message ...

This is my first post on this forum so please bear with me :)

I've created a spreadsheet for one of my clients. In the sheet various
data is entered. This data is then processed and a new column of data
is produced, typically less than 1000 cells. In some cases this new
column contains multiple identical entries. I have found a formula
which creates a copy of the column and removes duplicate entries;

=IF(COUNTIF($D$1:D1,D1)=1,D1,"") - Entered into E1, E2, etc.

The problem is that this forumla leaves "blank" cells where duplicate
entries were previously. (The cells just show as blank, they actually
contain the above formula in them) I'd like to be able to remove these
blank spaces and make the data more presentable but I'm having trouble
figuring out how to do this. I'd like to try to keep the solution to a
formula so that its automated and occurs as soon as the data is
entered, and I'm unfamiliar with VBA.

If anyone can help me I would really appreciate it. I'm using Excel
2003 if it matters. Thanks all.


--
PaulQuincy
------------------------------------------------------------------------
PaulQuincy's Profile:
http://www.excelforum.com/member.php...o&userid=31474
View this thread: http://www.excelforum.com/showthread...hreadid=511547