Harold Fuchs wrote:
On 18/09/2007 21:52, JosephK wrote:
Given a sheet of about 2000 rows with the first column containing
numbers sorted into ascending order but with the same number sometimes
appearing in more than one row, what is the easiest way to remove rows
with the duplicated numbers leaving only one instance of each number.
Columns 2 onwards may differ row to row even if the cell in column 1 is
the same. I've tried all sorts of ways to do this but seem to end up
with something far too complex.
Am I missing something obvious? I would have thought this was a fairly
common requirement.
Regards, John
A kludge:
Assuming your sorted data is in Columns A and B starting in row 1 and
that the sort key is column A, then put into cell C2 the formula
=IF(A2=A1;A2;0) and drag/copy this formula down to the last row. Note
that the "0" in the formlua *must* be a value chosen to be either less
than or greater than *any* value in column A (call this the
"magic-value"). The formula will cause the magic-value to appear only
in rows where the column A value is a duplicate.
The result will be that cells in column C contain either a value from
the equivalent cell in column A, or the magic-value.
Now sort the entire 3-column table on column *C*. The rows where
column C contains magic-values will either come all at the top or all
at the bottom and you can delete or hide them easily.
Not ideal I grant you and I really hope someone else will come up with
a better solution but ...
Try the Standard Filter under Data. It has a checkbox for No
Duplication. I've been using this for a couple of years now and it has
never failed me. For an example, look here Filter Unique Values in OOo
Calc <http://markjstevens.net/index.php>.
Rick
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
