Removing "Blank Cells" from a column

[PaulQuincy]

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$11,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.

 

[Max]

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$11,Sheet1!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

 

[PaulQuincy]

I tried what you said but i ended up with a sheet 2 not showing anything
at all. Maybe I did something wrong, I'll try again a couple times.

To make it easier you can assume that all the data is in column A only
(the data is only in a single column)

 

[Max]

I tried what you said but i ended up with a sheet 2 not showing anything
at all. Maybe I did something wrong, I'll try again a couple times.
To make it easier you can assume that all the data is in column A only
(the data is only in a single column)

My apologies, Paul ..
the earlier criteria formula (col E in Sheet2) was incorrect

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

should have read as:

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

[ No change to the other formulas ]

(The earlier criteria formula flagged the *duplicates* in col D in Sheet1,
instead of the *uniques*)

---

 

[Max]

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

... and perhaps simpler to illustrate (going back to your orig. post)

Put instead in E1:
=IF(D1="","",IF(COUNTIF($D$11,D1)>1,"",ROW()))

Put in F1:
=IF(ISERROR(SMALL(E:E,ROW())),"",
INDEX(D,MATCH(SMALL(E:E,ROW()),E:E,0)))

Select E1:F1, copy down to say, F1000
to cover the max expected extent of data in col D

Col F will auto-return the uniques list from col D, all neatly bunched at
the top

Here's a quick sample to illustrate the above construct:
http://cjoint.com/?cniYDLtDb8
PaulQuincy_wks_1.xls

---