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 ...
--
Harold Fuchs
London, England
Please reply *only* to [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
